SQL How to Select the most recent date item

Question

Hello I have a table with columns:

*using oracle

ID                  NUMBER
USER_ID            NUMBER
DATE_ADDED          DATE
DATE_VIEWED        DATE
DOCUMENT_ID        VARCHAR2
URL                VARCHAR2
DOCUMENT_TITLE      VARCHAR2
DOCUMENT_DATE        DATE

I want to know how i can get the most recently added document for a given user.

Select * FROM test_table WHERE user_id = value AND (do something with date_added column)

Thanks

Solution 1

Select * 
FROM test_table 
WHERE user_id = value 
AND date_added = (select max(date_added) 
   from test_table 
   where user_id = value)

Solution 2

Not sure of exact syntax (you use varchar2 type which means not SQL Server hence TOP) but you can use the LIMIT keyword for MySQL:

Select * FROM test_table WHERE user_id = value
     ORDER BY DATE_ADDED DESC LIMIT 1

Or rownum in Oracle

 SELECT * FROM
     (Select rownum as rnum, * FROM test_table WHERE user_id = value ORDER BY DATE_ADDED DESC)
 WHERE rnum = 1

If DB2, I'm not sure whether it's TOP, LIMIT or rownum...

Solution 3

With SQL Server try:

SELECT TOP 1 *
FROM dbo.youTable
WHERE user_id = 'userid'
ORDER BY date_added desc

Solution 4

You haven't specified what the query should return if more than one document is added at the same time, so this query assumes you want all of them returned:

SELECT t.ID,
       t.USER_ID,
       t.DATE_ADDED,
       t.DATE_VIEWED,
       t.DOCUMENT_ID,
       t.URL,
       t.DOCUMENT_TITLE,
       t.DOCUMENT_DATE
FROM (
  SELECT test_table.*,
         RANK()
         OVER (ORDER BY DOCUMENT_DATE DESC) AS the_rank
  FROM   test_table
  WHERE  user_id = value
  )
WHERE the_rank = 1;

This query will only make one pass through the data.

Solution 5

Assuming your RDBMS know window functions and CTE and USER_ID is the patient's id:

WITH TT AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY DOCUMENT_DATE DESC) AS N
    FROM test_table
)
SELECT *
FROM TT
WHERE N = 1;

I assumed you wanted to sort by DOCUMENT_DATE, you can easily change that if wanted. If your RDBMS doesn't know window functions, you'll have to do a join :

SELECT *
FROM test_table T1
INNER JOIN (SELECT USER_ID, MAX(DOCUMENT_DATE) AS maxDate
            FROM test_table
            GROUP BY USER_ID) T2
    ON T1.USER_ID = T2.USER_ID
        AND T1.DOCUMENT_DATE = T2.maxDate;

It would be good to tell us what your RDBMS is though. And this query selects the most recent date for every patient, you can add a condition for a given patient.

View more solutions