CODEWITHSUNDEEP
sqlgroup-bygreatest-n-per-group
Joyce
Joyce

Reputation: 435

SQL Select min(date) group by with corresponding row

There are some subsets of a parent set with release date. And I want the earliest release date with the subset's no, but in a group base.

I try this:

SELECT Sub_No,Parent_No,MIN(DATE)
FROM mytable
WHERE other_CODE IS NULL
GROUP BY Sub_No,Parent_No
ORDER BY Parent_No DESC

but I get this: but I only want the most earliest released one as the 2015-02-12's Sub_No and Parent_No.

Any help will be highly appreciated!

Parent_No   (No column name)    Sub_No
07  2015-02-12  90
07  2015-11-03  88
07  2017-02-06  59

Upvotes: 0

Views: 464

Answers (2)

EmiFala
EmiFala

Reputation: 91

Only if Parent_No and DATE are unique key of mytable, then you can do the trick like this:

SELECT * FROM mytable
INNER JOIN
(
SELECT Parent_No,MIN(DATE) as MinDate
FROM mytable
WHERE other_CODE IS NULL
GROUP BY Parent_No
) AS Filter 
on mytable.Parent_No = Filter.Parent_No and mytable.DATE = Filter.MinDate

Upvotes: 0

Popeye
Popeye

Reputation: 35930

You can use the NOT EXISTS as follows:

SELECT Sub_No,Parent_No,DATE
FROM mytable t
where other_CODE IS NULL
  and not exists (select 1 from mytable tt
  where tt.Parent_No = t.Parent_No and tt.date < t.date
     and tt.other_CODE is null)

If your database supports analytical function then you can use it as follows:

select Sub_No,Parent_No, DATE from
(SELECT Sub_No,Parent_No, DATE,  
       row_number() over (partition by parent_no order by date) as rn
FROM mytable
WHERE other_CODE IS NULL) t
where rn = 1

Upvotes: 1

Related Questions