Order By - Leave specific record for first

Asked

Viewed 2,325 times

0

Store_Name  | Sales | Date
America     | 1500  | 05-Jan-1999
Boston      | 700   | 08-Jan-1999
Canada      | 300   | 08-Jan-1999
Dinamarca   | 250   | 07-Jan-1999

I have this table, I’d like to sort in a way where Boston was the first record, the order of the others being irrelevant. How do I?

database postgresql classification query

  • What is the need for this?

  • 7

    meet customer’s demand

3 answers

9


No need to use case

select *
from t
order by store_name != 'Boston'

The false sorts before the true. And you can do a normal sort with the other lines:

order by store_name != 'Boston', store_name

  • This is much better than accept it.

3

select *
from tabela
order by case when store_name = 'Boston' then 1 else 2 end;
  • 2

    I’m curious to understand what this 1 else 2 makes. In my understanding it means "order by the column Store_Name (i.e., column 1) when this is 'Boston' or by the column Sales (column 2) otherwise. I am wrong?

  • 3

    @Piovezan Actually, this SQL means: 'Create an anonymous field, whose value is 1 store_name case is Boston, and 2 for any other case - and sort my result by this field.'

  • @Onosendai, good explanation.

  • 1

    @Piovezan, Not! Look at this fiddle http://sqlfiddle.com/#! 6/746fe/2

  • @Onosendai Thanks for the explanation :)

  • @Jean Thanks for the example :)

Show 1 more comment

1

I prefer an alternative where I don’t need to create a dynamic order by

select Store_Name, Sales, Date, case when store_name = 'Boston' then 0 else 9 end as Orderby
from tabela
order by orderby

Besides this alternative you can add an orderby column in the table, so it is easy to place more than one record with a "fixed" sort".

I believe that the solution using a case in the order by clause can generate not very "cool" query plans (this needs to be tested before clear!)

Here a fiddle for reference

  • I did a test now in an Oracle environment and the Plan was the same (having order by), only changed when without order by.

  • I didn’t understand when you said 'can generate not very "cool" query plans', could explain better?

  • I meant that it can (and this needs to be tested) make the engine choose a slower run, affecting performance

Browser other questions tagged database postgresql classification query

You are not signed in. Login or sign up in order to post.