Sql: Select Most Recent Date for Each Category

SQL: Select most recent date for each category

SELECT
    category_a,
    category_b,
    MAX(date)
FROM
    Some_Unnamed_Table
GROUP BY
    category_a,
    category_b
ORDER BY
    category_a,
    category_b

I certainly don't mind helping people when I can, or I wouldn't be on this site, but did you really search for an answer for this before posting?

Get most recent date for each id

select ID, Max(Date) as Date 
from yourtable
group by ID

how do I query sql for a latest record date for each user

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

SQL Group By most recent date and sales value

SELECT ID, Name, Order, Date FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date DESC) AS sn
    FROM your_table_name
) A WHERE sn = 1;

select rows in sql with latest date for each ID repeated multiple times

This question has been asked before. Please see this question.

Using the accepted answer and adapting it to your problem you get:

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

Select row with most recent date per user

Query:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)

Result:

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

Note that if a user has multiple records with the same "maximum" time, the query above will return more than one record. If you only want 1 record per user, use the query below:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user            
                 ORDER BY t2.id DESC
                 LIMIT 1)

Select second latest date for each group in the table

You can use the following code to get the second latest date:

select * from 
(select SKU,plannendstartinflow,
ROW_NUMBER()over(partition by SKU order by plannendstartinflow desc) as rn 
from test
)t where rn=2;

EDIT:

select * from 
(select SKU,plannendstartinflow,  --similarly add other columns
ROW_NUMBER()over(partition by SKU order by plannendstartinflow desc) as rn 
from (
      --Put Your Subquery here
     )t1
)t2 where rn=2;

EDIT2: To select max and second max date.

select SKU,min(plannendstartinflow) as secondmaxdate,max(plannendstartinflow) as 
maxdate from 
(select * from (select SKU,plannendstartinflow,
ROW_NUMBER()over(partition by SKU order by plannendstartinflow desc) as rn 
from test
)t where rn<3
)t1 group by SKU;

Join tables based on most recent date for each record

An OUTER APPLY is like the LEFT JOIN that you tried, but it allows you to join to an inline view AND it allows you to refer to columns from previously joined tables in the WHERE clause of that inline view.

Using OUTER APPLY you can use the WHERE clause to find all the backups that occurred on or before each date, use the ORDER BY clause to sort them by backup date latest-to-earliest, and then use the FETCH FIRST clause to just get the first one in the sorted list (i.e., the latest backup).

SELECT d.*, b.DATE most_recent_backup
FROM   my_dates d
OUTER APPLY ( SELECT b.date
              FROM backups b 
              WHERE b.date <= d.date 
              ORDER BY b.date DESC
              FETCH FIRST 1 ROW ONLY ) b

You can also do this with NOT EXISTS if you aren't on a version of Oracle that supports OUTER APPLY. Something like this:

SELECT d.*, b.DATE most_recent_backup
FROM   my_dates d
LEFT JOIN backups b ON b.date <= d.date
WHERE ( 
    -- Either the backup date is NULL (this happens if the LEFT JOIN
    -- found no backups earlier than the given date)
    b.date IS NULL
    OR  
    -- Or there is a backup later than the row backup we are looking at.
    -- The LEFT JOIN condition joins each date to ALL the backups that
    -- happened on or before that date.  This condition excludes
    -- every backup for a given date except for the most recent one.
    -- If the backup is not the most recent backup on or before a 
    -- given date, there will exist a later backup that is also on
    -- or before that same date.
    NOT EXISTS ( SELECT 'later backup that is earlier than date'
                     FROM   backups b2
                     WHERE  b2.date <= d.date
                     AND    b2.date > b.date )
      )

---

Related Topics