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
Select Top 1* FROM test_table WHERE user_id = value order by Date_Added Desc
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.
With SQL Server try:
SELECT TOP 1 *
FROM dbo.youTable
WHERE user_id = 'userid'
ORDER BY date_added desc
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...
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.
Source: Stackoverflow.com