How to Select the Set of Rows Where Each Item Has the Greatest Timestamp

Select row with largest timestamp in each category

For MySQL, this query will return the result set you specified:

SELECT b.title
     , MAX(b.timestamp)
     , b.category_id
  FROM blogs b
 GROUP BY b.category_id

If there happen to be more than one "title" in a category that has the same latest "timestamp", only one row for that category will be returned, so you will get just one "title" returned for each category.

Note: other DBMS system will throw an exception (error) with a query like the one above, because of the handling of non-aggregates in the SELECT list that don't appear in the GROUP BY.

Your query was very close. You've already got that inner query returning the "latest" timestamp for each category. The next step is to return the category_id along with that latest timestamp.

SELECT category_id, MAX(timestamp) AS timestamp
  FROM blogs
 GROUP BY category_id

The next step is to join that back to blogs, to get the associated "title"(s)

SELECT b.title
     , b.timestamp
     , b.category_id
  FROM (SELECT category_id, MAX(timestamp) AS timestamp
          FROM blogs 
         GROUP BY category_id
       ) l
  JOIN blogs b
    ON b.category_id = l.category_id AND b.timestamp = l.timestamp 

NOTE: if there is more than one "latest" row for a category (the timestamp values match), this query will return both of them.

If that's a concern, the query can be modified (or written in a different way) to prevent any possibility of two rows for a category.

Simply adding a GROUP BY clause to that query will work (in MySQL only, not other DBMSs)

SELECT b.title
     , b.timestamp
     , b.category_id
  FROM (SELECT category_id, MAX(timestamp) AS timestamp
          FROM blogs 
         GROUP BY category_id
       ) l
  JOIN blogs b
    ON b.category_id = l.category_id AND b.timestamp = l.timestamp
 GROUP BY b.timestamp, b.category_id

(For other DBMSs, you could modify the SELECT list, replace b.title with MAX(b.title) AS title. That will work when you are returning a single column from the row.

If you want the rows returned in a particular order, add an ORDER BY clause.

Selecting all rows with highest (relative) timestamp

I think you need to use something like this:

SELECT T3.*
FROM your_table T4
JOIN
(
    SELECT T2.[Key], T2.EffectiveDate, MAX(T2.ID) AS ID
    FROM your_table T2
    JOIN 
    (
        SELECT [Key], MAX(EffectiveDate) AS EffectiveDate
        FROM your_table
        WHERE AppName = 'App1'
        AND EffectiveDate <= GetDate()
        GROUP BY [Key]
    ) T1
    ON T1.[Key] = T2.[Key] AND T1.EffectiveDate = T2.EffectiveDate
    WHERE T2.AppName = 'App1'
    GROUP BY T2.[Key], T2.EffectiveDate
) T3
ON T3.ID = T4.ID

How to select only the most recent timestamp?

You can go for simple query with CROSS APPLY as given below:

SELECT
  e.customer_id AS customer_id,
  e.event_id AS event_id,
  max(t.updated_On)
FROM
  event_table AS e
  INNER JOIN car AS c ON e.customer_id = c.customer_id and e.event_id = c.event_id
  INNER JOIN motorcycle AS m ON e.customer_id = m.customer_id and e.event_id = m.event_id
  INNER JOIN walk AS w ON e.customer_id = w.customer_id and e.event_id = w.event_id
  CROSS APPLY (values (c.updated_On),(m.updated_On),(w.updated_On)) as t(updated_On)
GROUP BY e.customer_id,
  e.event_id

Sample data and working solution

declare @event table(cust_id int, event_id int)
declare @car table(cust_id int, event_id int, updated_on datetime)
declare @walk table(cust_id int, event_id int, updated_on datetime)

insert into @event values (1, 100)
insert into @car values (1,100, '2020-01-01')
insert into @walk values(1,100, '2020-02-01')

SELECT
  e.cust_id AS customer_id,
  e.event_id AS event_id,
  max(t.updatedON)  as recent_timestamp
FROM
  @event AS e
  INNER JOIN @car AS c ON e.cust_id = c.cust_id and e.event_id = c.event_id
  INNER JOIN @walk AS w ON e.cust_id = w.cust_id and e.event_id = w.event_id
  CROSS APPLY (values(c.updated_On),(w.updated_on)) as t(updatedOn)
  group by e.cust_id, e.event_id

SELECT * FROM my_table -- standard stuff
   WHERE user_2 = 22 -- predicate
   ORDER BY timestamp DESC -- this means highest number (most recent) first
   LIMIT 1; -- just want the first row

SELECT MAX( timestamp ) FROM my_table

SELECT a.*
FROM t1 AS a
INNER JOIN
(
    SELECT mid, MAX(time) AS maxTime
    FROM t1
    WHERE field_1 <> 0
    GROUP BY mid
) b ON a.mid = b.mid AND a.time = b.maxTime

SELECT tt.*
FROM myTable tt
INNER JOIN
    (SELECT ID, MAX(Date) AS MaxDateTime
    FROM myTable
    GROUP BY ID) groupedtt 
ON tt.ID = groupedtt.ID 
AND tt.Date = groupedtt.MaxDateTime