CODEWITHSUNDEEP
sqlsql-server
ojek
ojek

Reputation: 10056

Get last distinct row?

I have the following table (table is called posts):

id  datecreated                 threadid
1   2013-03-17 17:36:12.9281661 1
2   2013-03-17 17:56:18.3472836 1
3   2013-03-18 07:09:08.2733381 2
4   2013-03-18 07:35:58.1251661 3
5   2013-03-18 22:04:41.6053307 3
6   2013-03-19 03:30:56.0803165 3
7   2013-03-19 16:26:59.1518276 4
8   2013-03-19 16:27:47.4339124 4
9   2013-03-19 16:28:13.3455579 4
10  2013-03-19 16:55:16.3930089 5

I want the query to return only rows where threadid is listed three times, but I want only single row of each threadid, and it need to be the last one (ordered by date).

How can I do this?

Upvotes: 2

Views: 58

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460028

You could use ranking functions:

WITH CTE AS
(
    SELECT id, datecreated, threadid,
        TotalCount = COUNT(*) OVER (PARTITION BY threadid),
        RN = ROW_NUMBER()     OVER (PARTITION BY threadid
                                    ORDER BY datecreated DESC)
    FROM dbo.Posts
)
SELECT id, datecreated, threadid
FROM CTE 
WHERE TotalCount = 3 AND RN = 1

Sql-Fiddle

Upvotes: 4

juergen d
juergen d

Reputation: 204746

select p.*
from posts p
inner join
(
  select threadid, max(datecreated) maxdate 
  from posts
  group by threadid
  having count(*) = 3
) x on x.threadid = p.threadid and x.maxdate = p.datecreated

Upvotes: 4

Related Questions