Geek Answers Handbook

How to remove duplicates from the selected query?

Before asking this question, I searched with Google, but I could not understand, or maybe I could not find a solution suitable for my situation.

So, I have one table with 10 columns, I want to remove duplicates from the select result. And as a result, all columns that have a unique userID should be presented

  + ----------------------------------- + ------ + ------ --------------- + ------ +
 |  name |  yr |  some Columns | userID |
 + ----------------------------------- + ------ + ------ --------------- + ------ +
 |  abc |  2000 |  |  10 |
 |  jack |  2000 |  |  11 |
 |  dadas |  2000 |  |  12 |
 |  jack |  2004 |  ............. |  11 |
 |  jack |  2000 |  ........... |  11 |
 |  nell |  2006 |  ............. |  13 |
 |  ...... |  2000 |  ............. |  1 |
 |  ............. |  2000 |  ............. |  2 |
 |  again |  2000 |  ............. |  3 |
 |  again |  2000 |  |  3 |
 |  ....... |  1973 |  ............. |  2 |
 |  abc |  2000 |  |  10 |
+6
sql select postgresql distinct
source share
4 answers

If you don't need to save different yrs just use DISTINCT ON (FIELD_NAME)

SELECT DISTINCT ON (userID) userdID, name, yr FROM TABLE_NAME
+6
source share

For PostgreSQL , as well as SQL Server 2005+, DB2, and later versions of Oracle (9+), you can use the windowing function ROW_NUMBER ()

 select * from ( select *, ROW_NUMBER() over (partition by userID order by yr desc) rown ) X where rown = 1
+2
source share

Try the following:

 SELECT * FROM TABLE_NAME GROUP BY userID
+1
source share

It will be easy:

 SELECT DISTINCT (userID) userdID, name, yr FROM TABLE_NAME
0
source share

More articles:


All Articles