CODEWITHSUNDEEP
sqlsqlitepostgresql
oneofakind
oneofakind

Reputation: 562

SQLite/Postgres SQL Query Distinct column

I have been dealing with this for quite sometime now and I could not be able get around this. It is like this, I have table with case_no (alphanumeric), first_name, last_name and description.

What I want do is to select only one row for each distinct first_name/last_name combination. So, for example

     case_no         |         first_name         |         last_name
---------------------+----------------------------+------------------------
     AAAAAAA         |          john              |          doe
     BBBBBBB         |          Dog               |          dawg
     CCCCCCC         |          Cat               |          kitten
     DDDDDDD         |          john              |          doe

Given that example I want to retrieve the following rows:

     case_no         |         first_name         |         last_name
---------------------+----------------------------+------------------------
     BBBBBBB         |          Dog               |          dawg
     CCCCCCC         |          Cat               |          kitten
     DDDDDDD         |          john              |          doe

How do I do this?

Upvotes: 1

Views: 234

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656241

In PostgreSQL:

SELECT DISTINCT ON (2, 3)
       case_no, first_name, last_name
FROM   tbl
ORDER  BY 2, 3, case_no DESC NULLS LAST;

About DISTINCT ON:

Difference to the version with max():

  • Results are ordered by first_name, last_name. You may or may not want that.
  • Performance is similar. If you need ordered results this is probably faster, else max() may be faster.
  • You can easily add more columns from the same row.

Upvotes: 3

Orangecrush
Orangecrush

Reputation: 1980

Try this,

SELECT MAX(CASE_NO), FIRST_NAME, LAST_NAME
FROM TABLE
GROUP BY FIRST_NAME, LAST_NAME;

Upvotes: 6

Related Questions