Harry Muscle
Harry Muscle

Reputation: 2377

Oracle SQL Grouping/Ordering

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

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

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

GriffeyDog
GriffeyDog

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

Related Questions