Reputation: 2377
I'm looking for some help in writing an Oracle SQL statement to accomplish the following ...
Let's say I have the following data:
YEAR | PLACE
1984 | somewhere
1983 | work
1985 | somewhere
1982 | home
1984 | work
1983 | home
1984 | somewhere
How can I get a result that keeps all the PLACE column values together and orders it by the YEAR column ... so the result I'm looking for is:
YEAR | PLACE
1982 | home
1983 | home
1983 | work
1984 | work
1984 | somewhere
1984 | somewhere
1985 | somewhere
Thanks.
EDIT:
Just to illustrate answers to some of the questions asked ... let's say I add the following data to my original data:
1981 | somewhere
Now the result should be:
YEAR | PLACE
1981 | somewhere
1984 | somewhere
1984 | somewhere
1985 | somewhere
1982 | home
1983 | home
1983 | work
1984 | work
Thanks.
Upvotes: 0
Views: 153
Reputation: 115530
You can achieve this with a window aggregate function:
SELECT
year,
place
FROM
tablename
ORDER BY
MIN(year) OVER (PARTITION BY place),
place,
year ;
Tested at: SQLfiddle.com
Upvotes: 4
Reputation: 8376
REVISED base on the OPs comments:
select a.year, a.place
from my_table a join
(select place, min(year) as min_year from my_table group by place) b on a.place = b.place
order by b.min_year, a.place, a.year
Upvotes: 1