CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 1852

Group by with MIN value in same query while presnting all other columns

I have a view called a with this data:

 ID    tDate        name     task   val
23   2015-06-14
23   2015-06-25
126  2015-06-18
126  2015-06-22
126  2015-06-24

ID is integer and tDate is timestamp.

Basically I want to get for each ID the min value of tDate and present this row. meaning:

ID   tDate       name     task   val
23   2015-06-14
126  2015-06-18

I wrote this query:

select ID, min(tDate)
from a
group by ID
order by ID

This is working BUT it doesn't allow me to present all other columns of a

for example if I do:

select ID, min(tDate), name
from a
group by ID
order by ID

it says that name must be under group by. So I wrote this query:

select ID, MIN(tDate), name, task, val , ....
from a
group by ID, name, task, val , ....
order by ID

And this one doesn't work. it gives false results.

How do I solve it?

Upvotes: 1

Views: 52

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

Postgres has the very convenient distinct on for this type of problem:

select distinct on (id) a.*
from a
order by id, tdate;

This will return one row for each id. The row is the first one determined by the ordering defined in the order by clause.

Upvotes: 2

DRapp
DRapp

Reputation: 48139

Do a join from the one table to a sub-query table on just the ID / Min Date

select
      YT.ID,
      YT.tDate as OriginalDate,
      PQ.MinDate,
      YT.name,
      YT.task,
      YT.val
   from
      YourTable YT
         JOIN ( select ID, min( tdate ) as MinDate
                   from YourTable
                   group by ID ) as PQ
         on YT.ID = PQ.ID
        AND YT.tDate = PQ.MinDate
   order by
      ID

Upvotes: 1

Tim3880
Tim3880

Reputation: 2583

Try something like this:

select a.id, a.tdate , .... from a
join (select id, min(tdate) min_date   
from a
group by ID
) b
on a.id=b.id and a.tdate = b.min_date
order by a.id

Upvotes: 0

Related Questions