CODEWITHSUNDEEP
sqlpostgresqlselectgreatest-n-per-grouppostgresql-9.6
JasonGenX
JasonGenX

Reputation: 5434

SQL: how do I select two fields of the same SINGLE row in a group?

I'm using Postgres 9.6.*

I have this:

street | first_name | last_name
1st    | james      | bond
1st    | mr         | q 
1st    | ms         | m
2nd    | man        | with_golden_gun

I would like to get a list of distinct addresses and the first set of 'first_name' and 'last_name' for each.

my desired output:

street | first_name | last_name
1st    | james      | bond
2nd    | man        | with_golden_gun

I am grouping by street, and trying MIN(first_name) and MIN(last_name) -- however -- using MIN there are cases for each group of unique street I can get seemingly random a mix-and-match first_name and last_name that may not be of the same row. Obviously, MIN (minimum) isn't the right aggregator function here.

my question: how do I enforce that the first_name and last_name are from the same row?

Upvotes: 1

Views: 156

Answers (4)

OBi
OBi

Reputation: 116

You need the "DISTINCT ON" clause, but this requires sorting, e.g. first_name:

SELECT
 DISTINCT ON (street)
 street, first_name, last_name
FROM table
ORDER BY street, first_name

Upvotes: 1

Anonymous 11
Anonymous 11

Reputation: 1

-- I am grouping this based on min first_name and getting the last name based on that first name

Select street, first_name, 
(select last_name from person o where o.first_name = x.first_name) 
from (Select street, min(first_name) as first_name  
from person v group by street) as x;

-- Output 

street | first_name | last_name
-------------------------------------
1st    | james      | bond
2nd    | man        | with_golden_gun

-- If it is okay to combine first name and last name field 

Select street, min(concat(first_name , ' ' , last_name)) as name
from person group by street

-- Output 

street | name 
----------------------------
1st    | james bond
2nd    | man with_golden_gun

Upvotes: 0

less
less

Reputation: 711

As described in How to show row numbers in PostgreSQL query? you can get a row number. Then you can ORDER or WHERE your select statement as desired.

Upvotes: -1

Mureinik
Mureinik

Reputation: 311053

You can use the row_number window function to query a single line per group:

SELECT street, first_name, last_name
FROM   (SELECT street, first_name, last_name,
               ROW_NUMBER() OVER (PARTITION BY street ORDER BY first_name) AS rn
        FROM   mytable) t
WHERE  rn = 1

Upvotes: 1

Related Questions