CODEWITHSUNDEEP
sqlsql-order-by
Dennis Szymanski
Dennis Szymanski

Reputation: 1

firebird sql order by

i got a dataset which looks like this:

customernumber year value

1    2011   500
2    2011   100
1    2010   400
3    2010   600
3    2011   300
2    2010   700

i want it to be ordered by highest value of year 2011, but the rows of each customer need to stay together.

it should look like this:

customernumber year value

1    2011   500
1    2010   400
3    2011   300
3    2010   600
2    2011   100
2    2010   700

is this even possible?

thanks in advance!

Upvotes: 0

Views: 7171

Answers (2)

apomene
apomene

Reputation: 14389

select  customernumber, year, value from mytable  
group by customernumber, year, value 
order by year desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Use join to bring that value in, and then you can use it for the oder by:

select d.customernumber, d.year, d.value
from dataset d join
     (select d.*
      from dataset d
      where d.year = 2011
     ) d2011
     on d.customernumber = d2011.customernumber
order by d2011.value, d.customernumber, d.year desc;

In databases that support window functions, this can more easily be done as:

select d.*
from dataset d
order by max(case when year = 2011 then value end) over (partition by customernumber),
         customernumber, year desc;

Upvotes: 1

Related Questions