SQLite/Postgres SQL Query Distinct column

Question

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?

DO you want to perform a DELETE operation or you just want to filter your resultset? — Mikayil Abdullayev, Feb 08 '13 at 07:57
no, I just want to select them. I am using java to retrieve data then manipulate it. — oneofakind, Feb 08 '13 at 08:01

score 6 · Accepted Answer · answered Feb 08 '13 at 07:58

6

Try this,

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

answered Feb 08 '13 at 07:58

Orangecrush

1,970
2
15
26

score 3 · Answer 2 · edited May 23 '17 at 12:06

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:

Select first row in each GROUP BY group?

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.

SQLite/Postgres SQL Query Distinct column

2 Answers2