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

User · Accepted Answer

Select    FROM test table  WHERE user id   value  AND date added    select max date added      from test table     where user id   value

User · Answer

Select Top 1  FROM test table WHERE user id   value order by Date Added Desc

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

With SQL Server try   SELECT TOP 1   FROM dbo youTable WHERE user id    userid  ORDER BY date added desc

[sql] SQL How to Select the most recent date item

Examples related to sql

Examples related to oracle

Examples related to select

Examples related to date