CODEWITHSUNDEEP
sqloracle-database
Grim
Grim

Reputation: 2026

JOIN / GROUP select earliest

This is the Data in Bar:

ID     FooID    StartDate
1        1         1-1-2011
2        1         2-1-2011
3        1         3-1-2011
4        2         9-1-2011
5        2         4-1-2011

This is the Table Foo:

ID   Name
1        Car
2        Bus

I need to LEFT JOIN The earliest occourency FROM Foo

This is what i have now:

SELECT NAME
FROM Foo
LEFT JOIN (
  SELECT *
  FROM Bar
  WHERE Bar.FooID = Foo.ID
  ORDER BY Bar.StartDate
  ) MyBar
  ON (ROWNUM = 1)

But a ORA-00923 occours.

Upvotes: 2

Views: 65

Answers (3)

valex
valex

Reputation: 24144

SQLFiddle demo

select * from Foo
LEFT JOIN
(
  SELECT  BAR.*,
          ROW_NUMBER() 
          OVER (PARTITION BY FooID 
                ORDER BY StartDate) as RowNumber
   FROM BAR 
 ) Bar2 on (Foo.id=Bar2.FooId) and (Bar2.RowNumber=1)

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21657

Try this:

SELECT NAME
FROM foo b
LEFT JOIN (
  SELECT FooID,MIN(StartDate)
  FROM bar
  GROUP BY FooID
  ) MyBar
  ON MyBar.FooId = b.ID

sqlfiddle demo

This will JOIN with MIN(startDate) for each FooId.

If you include the date in the query it would result in:

NAME   FOODATE
Car    January, 01 2011 00:00:00+0000
Bus    April, 01 2011 00:00:00+0000

Upvotes: 1

Brett Schneider
Brett Schneider

Reputation: 4103

i am not quite sure what you are trying to do here, but i think you cannot join on rownum nor can you address foo.id from the inner select. i can give you this approach, i think it should do the trick:

select name
  from foo
  left join (
              select bar.*
                   , dense_rank() over (order by bar.startdate) as cand
                from bar
            ) mybar
    on (mybar.fooid = foo.id)
 where mybar.cand = 1

it first ranks the entries from bar by startdate and then joins through the ids. outside it only selects the candidate ranked 1 (earliest startdate)

Upvotes: 1

Related Questions