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

Question

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?

What is the "first set of 'first_name' and 'last_name' for each"? — less, Oct 12 '17 at 18:25
as far as I'm concerned, ANY set where the row is identical for both values. I just don't want mix and match like "james" + "with_golden_gun" as a set of first_name and last_name — JasonGenX, Oct 12 '17 at 18:25
I think @less was asking about the definition for the ordering - is it alphabetical? — Error_2646, Oct 12 '17 at 18:27

score 1 · Accepted Answer · answered Oct 12 '17 at 18:26

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

score 1 · Answer 2 · answered Oct 12 '17 at 20:12

1

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

answered Oct 12 '17 at 20:12

OBi

106
1
4

score 0 · Answer 3 · answered Oct 12 '17 at 18:58

-- 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

less · Answer 4 · 2017-10-12T18:32:29.597

-1

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.

edited Oct 12 '17 at 18:32

answered Oct 12 '17 at 18:27

less

699
6
25

wasn't my downvote, but guessing that your answer here is best as a comment. Linking to another answer and saying go look there is my answer is generally bad form. Answers should stand on their own, links to support. – Twelfth Oct 12 '17 at 19:16

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

4 Answers4