CODEWITHSUNDEEP
sqlsql-serversortingsql-order-by
user776676
user776676

Reputation: 4385

SQL: How to sort / order query result in somewhat arbitrary manner?

I need to sort the results of a query in an UNCONVENTIONAL order.

Say, I do a search for "Chair". I want all the records STARTING with Chair to be listed first alphabetically, then other records that CONTAIN "chair" to be listed afterwards in alphabetical order. So "Blue Chair" would be listed after "Chair Blue" for example.

Please note the STARTING and CONTAINING conditions. How should I implement this sort? Thanks.

Upvotes: 2

Views: 1361

Answers (3)

mikn
mikn

Reputation: 484

(Turns out this is MySQL-specific)

The shortest I can come up with in SQL is the following:

SELECT * FROM table
WHERE column LIKE '%chair%'
ORDER BY IF(column LIKE 'chair%', 0, 1), column;

The IF gives the rows which start with 'chair' a 0 and the ones that don't a 1. The sorting order in SQL sorts the rows according to the first "column" specified first, then within that sorting order it will sort it according to the second, and so on.

To clarify, this is what it will look like for the ORDER BY:

ChairBlack  = 0, 'ChairBlack'
BlackChair  = 1, 'BlackChair'
Hello Chair = 1, 'Hello Chair'
Chair       = 0, 'Chair'

Making it sort it like this:

Chair       = 0, 'Chair'
ChairBlack  = 0, 'ChairBlack'
BlackChair  = 1, 'BlackChair'
Hello Chair = 1, 'Hello Chair'

Upvotes: 3

ifx
ifx

Reputation: 581

You can use an ORDER BY with a function (your own, or PATINDEX as below), for example:

create table test (value varchar(255))
delete from test
insert into test values ('Foo')
insert into test values ('Bar')
insert into test values ('BlueChair')
insert into test values ('Chair')
insert into test values ('NotABlueChair')
insert into test values ('chairs')
insert into test values ('Zoo')

select * from test where value like '%chair%' order by PATINDEX('%chair%', value)

Returns:

Chair
chairs
BlueChair
NotABlueChair

Upvotes: 4

Jake Feasel
Jake Feasel

Reputation: 16955

How does this look?

SELECT *, 1 as sort_pref FROM table WHERE column LIKE 'CHAIR%'
UNION
SELECT *, 2 as sort_pref FROM table WHERE column LIKE '%CHAIR%' AND column NOT LIKE 'CHAIR%'
ORDER BY sort_pref, column

Upvotes: 2

Related Questions