Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select distinct on multiple columns simultaneously, and keep one column in PostgreSQL

Tags:

sql

postgresql

For a table such as this:

tblA  

A,B,C  
1,2,t3a  
1,3,d4g  
1,2,b5e  
1,3,s6u 

I want to produce a table that selects distinct on both A and B simultaneously, and still keep one value of C, like so:

tblB  

A,B,C  
1,2,t3a  
1,3,d4g 

Seems like this would be simple, but not finding it for the life of me.

DROP TABLE IF EXISTS tblA CASCADE;  
SELECT DISTINCT ON (A,B), C  
INTO tblB  
FROM tblA;  
like image 505
nittyjee Avatar asked May 20 '26 12:05

nittyjee


2 Answers

When you use DISTINCT ON you should have ORDER BY:

SELECT DISTINCT ON (A,B), C
INTO tblB
FROM tblA
ORDER BY A, B;
like image 173
Gordon Linoff Avatar answered May 23 '26 03:05

Gordon Linoff


This should do the trick

CREATE TABLE tblB AS (
    SELECT A, B, max(C) AS max_of_C FROM tblA GROUP BY A, B
)
like image 43
Clément Prévost Avatar answered May 23 '26 04:05

Clément Prévost