CODEWITHSUNDEEP
sqlt-sqldb2
etm124
etm124

Reputation: 2140

Getting MIN date

I have a table(A) that looks something like:

ID     Date
1      2012/01/12
2      2012/01/01
3      2012/01/03
4      2012/03/12

If I wanted to grab the MIN date for this query, would I just group by?

select
  a.ID,
  MIN(a.DATE),
  b.name,
  c.price
FROM
  tablea a inner join tableb b on a.ID = b.ID
  inner join tablec c b.ID = c.ID

Upvotes: 0

Views: 266

Answers (3)

John Woo
John Woo

Reputation: 263703

WITH recordList
as
(
    select  a.ID,
            a.DATE,
            b.name,
            c.price,
            DENSE_RANK() OVER (PARTITION BY a.ID
                                ORDER BY a.Date ASC) rn
    FROM    tablea a 
            inner join tableb b on a.ID = b.ID
            inner join tablec c b.ID = c.ID
)
SELECT  ID, DATE, name, Price
FROM    recordList
WHERE   rn = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You want a window function. The correct expression is:

select a.id,
       min(a.date) over () as mindate,
       b.name, c.price
. . .

This says to get the min of the date over the data. There is no partition, so it gets it over all the data.

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

If you are looking for those that had the minimum date, then you can do this:

select
  a.ID,
  a.DATE,
  b.name,
  c.price
FROM tablea a 
INNER JOIN
(
   SELECT Id, MIN(Date) AS MinDate 
   FROM tablea
   GROUP BY Id
) As minA ON a.date = mina.mindate AND a.id = mina.id
inner join tableb b on a.ID = b.ID
inner join tablec c b.ID = c.ID

Upvotes: 1

Related Questions