CODEWITHSUNDEEP
sqlsql-server
Ozzman1893
Ozzman1893

Reputation: 23

Sql group by date query

I am trying to group this result:

ID  TEXT             Table1  FK1    Nodedate
940 bov1             TASKS   48  2016-10-26 15:53:20.247
938 foxxxxdsx        TASKS   48  2016-10-26 15:49:15.083
937 test2            TASKS   48  2016-10-26 15:24:32.667
936 test             TASKS   48  2016-10-26 15:24:20.277
889 <>asdasdsa       TASKS   48  2016-09-19 11:23:02.083
600 sadadasasdadsa   TASKS   48  2016-08-29 15:03:11.680

Into just 

ID  TEXT             Table1  FK1    Nodedate
940 bov1             TASKS   48  2016-10-26 15:53:20.247

is there a way to group to one result with just the date ?

current query:

SELECT  N.ID As ID , n.Text as Text ,Table1 , FK1 ,N.Created as notedate 
FROM NOTES N With(nolock)
LEFT OUTER JOIN NOTELinks NL On(N.id = NL.noteid)
WHERE Table1 = 'TASKS' AND N.IsSystem =0 AND FK1=48  
GROUP BY Table1 , FK1 
order by N.Created desc

Upvotes: 1

Views: 68

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Do you just want the most recent record?

SELECT TOP 1
    N.ID As ID,
    N.Text AS Text,
    Table1,
    FK1,
    N.Created AS notedate 
FROM NOTES N WITH (NOLOCK)
LEFT JOIN NOTELinks NL
    ON N.id = NL.noteid
WHERE
    Table1 = 'TASKS' AND
    N.IsSystem = 0   AND
    FK1 = 48
ORDER BY N.Created DESC

If you need to find the latest record for each Table1 and FK1 group then consider using an analytic function:

SELECT t.ID, t.Text, t.Table1, t.FK1, t.notedate
FROM
(
    SELECT TOP 1 N.ID As ID, N.Text as Text, Table1, FK1, N.Created AS notedate,
        RANK() OVER (PARTITION BY Table1, FK1 ORDER BY N.Created DESC) rank
    FROM NOTES N With(nolock)
    LEFT JOIN NOTELinks NL
        ON N.id = NL.noteid
    WHERE Table1 = 'TASKS' AND N.IsSystem = 0 AND FK1 = 48
) t
WHERE t.rank = 1;

I used RANK here rather than ROW_NUMBER in the event that two or more records might be tied for being the latest record. In this case, the above query would return all ties.

Upvotes: 2

sagi
sagi

Reputation: 40491

I advise on using ROW_NUMBER() , because if the requirement is for more than 1 FK1 , or you need an OFFSET then it can't be done with TOP :

SELECT p.* FROM (
    SELECT .....
           ROW_NUMBER() OVER(PARTITION BY n.FK1 ORDER BY n.created DESC) as rnk
    FROM .... // REST OF THE QUERY ) p
WHERE p.rnk = 1

This is dynamic to return all the most recent records for every FK1 exists

Upvotes: 0

Related Questions