Return multiple columns in a GROUP BY query

Question

Consider the following table:

    tbl_start_times

    id mach_id start_time
    -- ------- ----------
    1  1       00:12
    2  3       05:18
    3  1       03:56
    4  2       01:51
    5  1       12:48
    6  2       00:10
    7  3       09:15

I want to return id, mach_id, and MIN(start_time) for each mach_id.

The code:

    SELECT mach_id, MIN(start_time) FROM tbl_start_times GROUP BY mach_id

Returns this result:

    mach_id start_time
    ------- ----------
    1       00:12
    3       05:18
    2       00:10

How can I add the id to my result so that I get this?

    id mach_id start_time
    -- ------- ----------
    1  1       00:12
    2  3       05:18
    6  2       00:10

When you group by `mach_id`, the `id` may not be unique. Are you okay with seeing any one of the possibly many `id`s? — unutbu, Oct 17 '13 at 20:50
I want the id from the same row as the value returned by MIN(start_time) — Ben Glick, Oct 17 '13 at 20:53

score 2 · Answer 1 · 2013-10-17T21:07:50.267

There are two ways to do this in Postgres:

Using the Postgres specific distinct on () operator:

SELECT distinct on (match_id) id, match_id, start_time
FROM tbl_start_times 
ORDER BY match_id, start_time;

Or use a window function:

with numbered_times as (
    select id, match_id, start_time, 
           row_number() over (partition by match_id order by start_time) as rn
    from tbl_start_times 
) 
select id, match_id, start_time
from numbered_times
where rn = 1;

This also lets you easily pick the "second" or "fourth" row rather than only the "first" or "last" when you use distinct on (or a min/max solution)

If multiple rows are the "lowest" (i.e. have the same lowest time for the same match_id) and you want to see all of them, use dense_rank() instead of row_number()

The solution with distinct on is usually faster than the corresponding solution using a window function. The window function is standard SQL however and runs on (nearly) all modern DBMS. Both versions are usually faster than solutions using a sub-query or a derived table as only a single pass is required to read the data.

SQLFiddle example: http://sqlfiddle.com/#!12/caa95/5

score 1 · Answer 2 · answered Oct 17 '13 at 21:02

You can do it with correlated sub-query as follows:

SELECT id, mach_id, start_time
FROM tbl_start_times tst
WHERE start_time = (SELECT MIN(start time) 
                    FROM tbl_start_times tst2
                    WHERE tst2.mach_id = tst.mach_id)
ORDER BY id

SQL Fiddle

score 0 · Answer 3 · answered Oct 17 '13 at 21:02

0

Try this:

SELECT t.id , t.mach_id, t.start_time
FROM tbl_start_times t, 
    (SELECT mach_id, MIN(start_time) as start_time
    FROM tbl_start_times 
    GROUP BY mach_id) t1
WHERE t.mach_id=t1.mach_id AND t.start_time=t1.start_time

answered Oct 17 '13 at 21:02

Juliano

821
6
21

Return multiple columns in a GROUP BY query

3 Answers3