Geek Answers Handbook

SQL query to select recent records with a single object

I am using SQL Server and have a table created as shown below:

| id | subject | content | moreContent | modified   |
| 1  | subj1   | aaaa    | aaaaaaaaaaa | 03/03/2015 |
| 2  | subj1   | bbbb    | aaaaaaaaaaa | 03/05/2015 |
| 3  | subj2   | cccc    | aaaaaaaaaaa | 03/03/2015 |
| 4  | subj1   | dddd    | aaaaaaaaaaa | 03/01/2015 |
| 5  | subj2   | eeee    | aaaaaaaaaaa | 07/02/2015 |

I want to select the most recent entry for each item, so the returned entries will be as follows:

| id | subject | content | moreContent | modified   |
| 2  | subj1   | bbbb    | aaaaaaaaaaa | 03/05/2015 |
| 3  | subj2   | cccc    | aaaaaaaaaaa | 03/03/2015 |


SELECT Subject, MAX(Modified) FROM [CareManagement].[dbo].[Careplans] GROUP BY Subject

I could do a query like the above, but I want to save all the content from the selected rows. To return the content columns, I will need to apply an aggregate function or add them to the group by clause, which will not give me the desired effect. I also looked at the nested queries, but have not yet found a successful solution. If someone can help, it will be great.

+4
source share
3 answers

You can use ROW_NUMBER():

SELECT id, subject,  content, moreContent, modified
FROM (
   SELECT id, subject,  content, moreContent, modified,
          ROW_NUMBER() OVER (PARTITION BY subject 
                             ORDER BY modified DESC) AS rn
   FROM [CareManagement].[dbo].[Careplans] ) t
WHERE rn = 1

rn = 1 modified subject. "" , , , RANK().

+3

ROW_NUMBER .

with myCTE as
(
    select id
        , Subject
        , content
        , morecontent
        , Modified
        , ROW_NUMBER() over (PARTITION BY [Subject] order by Modified desc) as RowNum
    from [CareManagement].[dbo].[Careplans]
)

select id
    , Subject
    , content
    , morecontent
    , Modified
from myCTE
where RowNum = 1
+1

You can use the window function rankto retrieve only the last record:

SELECT id, subject, content, moreContent, modified
FROM   (SELECT id, subject, content, moreContent, modified,
               RANK() OVER (PARTITION BY subject ORDER BY modified DESC) AS rk
        FROM   [CareManagement].[dbo].[Careplans]) t
WHERE  rk = 1
+1
source

More articles:


All Articles