CODEWITHSUNDEEP
sqlsql-servergroup-bydistinct
joky
joky

Reputation: 3

Select distinct value of a view with repeated values in sql server

I have a view with this table result:

id   date         text     name     othertext
--- |-------------|-------|--------|---------
14  | 2013/02/01  | text1 | john   | 399 
14  | 2013/02/02  | text2 | john   | 244
14  | 2013/02/03  | text3 | john   | 555
14  | 2013/02/04  | text4 | john   | 300
13  | 2013/02/05  | text5 | juliet | 200
12  | 2013/02/06  | text6 | borat  | 500
12  | 2013/02/07  | text7 | borat  | 600
10  | 2013/02/08  | text8 | Adam   | 700
10  | 2013/02/09  | text9 | Adam   | 700

It is like a comment system. Each user can comment in different posts(the "id" is the id of a post). I would want to get the list of which post have been commented the last, order by date, but I don´t want to get the repeated name of the user who has done the comment.

This is the result that I want:

id   date          text    name     othertext
--- |-------------|-------|--------|---------
10  | 2013/02/09  | text9 | Adam   | 700 
12  | 2013/02/07  | text2 | borat  | 600
13  | 2013/02/05  | text5 | juliet | 200
14  | 2013/02/04  | text4 | john   | 300

Finally: I want to know the id of the post that have been commented the last but without repeated.

Thank you very much!!!!

Upvotes: 0

Views: 2713

Answers (2)

juergen d
juergen d

Reputation: 204784

select t.*
from your_table t
inner join 
(
   select id, max(date) as mdate
   from your_table
   group by id
) x on x.id = t.id and t.date = x.mdate
order by t.date desc

Upvotes: 1

John Woo
John Woo

Reputation: 263733

There are many ways to achieve what you want. Since it's SQL Server, you can use ranking function such as ROW_NUMBER()

SELECT  id, date, text, name, othertext
FROM    
        (
            SELECT  id,date,text,name,othertext,
                    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date DESC) rn
            FROM    tableName
        ) a
WHERE   rn = 1
ORDER   BY id

Upvotes: 2

Related Questions