CODEWITHSUNDEEP
sqlsql-servert-sql
Mudassir Hasan
Mudassir Hasan

Reputation: 28771

Order by followed by dependent order by

I have a table with part of data like below . I have done order by on edition_id . Now there is further requirement of ordering laungauge_id which depends on value of edition_id.

So suppose edition_id = 5 it means New Delhi.
For New Delhi language_id are 13(English ), 5 (Hindi) ,1(Telugu ),4(Urdu).

What i want is to display for New Delhi , is display all English articles first , followed by hindi , followed by Telugu followed by Urdu.

If edition_id=1 then order of language_id should be 13,1,2.

Similarly ,

If edition_id=5 then order of language_id should be 13,5,1,4

Right now what I have is

Edition_id | Language_id
1             1
1             2
1             13
1             1
1             13 
1             2
5             4
5             1
5             1
5             4
5             13
5             5
5             13

What is required

Edition_id | Language_id
1             13
1             13
1             1
1             1
1             2 
1             2
5             13
5             13
5             5
5             1
5             1
5             4
5             4

How to do this ? Please help.

Is something like this possibe

Select * from <table>
order by edition_id ,
            case when edition=6 then <order specified for language_id ie 13,5,1,4>

Upvotes: 3

Views: 146

Answers (4)

Randall W Thomson
Randall W Thomson

Reputation: 111

You've probably considered this but if your desired ordering is always based of the actual alphabetical name of the language then there would usually be a table with the language description that you could join with and then sort by. I base this on your quote below.

...English articles first , followed by hindi , followed by Telugu followed by Urdu.

SELECT E.EDITION_ID, E.LANGUAGE_ID, LN.LANGUAGE_NAME
FROM <TABLE> E LEFT OUTER JOIN <LANGUAGE_NAMES> LN ON
E.LANGUAGE_ID = LN.LANGUAGE_ID
ORDER BY 1, 3

Upvotes: 1

Steve Mayne
Steve Mayne

Reputation: 22868

I would create a supplementary ranking table. I would then JOIN to provide your sort order. Eg:

EDITION_SORT_ORDER

EDITION_ID  LANGUAGE_ID  RANK
----------  -----------  ----
1           13           1
1           1            2
1           2            3
5           13           1
5           5            2
5           1            3
5           4            4

Using this table in a query might look like this:

SELECT E.EDITION_ID, E.LANGUAGE_ID
FROM <TABLE> E LEFT OUTER JOIN EDITION_SORT_ORDER S ON
  E.EDITION_ID = S.EDITION_ID AND
  E.LANGUAGE_ID = S.LANGUAGE_ID
ORDER BY S.RANK

This way you can add other rules in future, and it isn't a huge mess of CASE logic.

Alternatively, if you want to avoid a JOIN, you could create a stored function which did a similar lookup and returned a rank (based on passed parameters of EDITION_ID and LANGUAGE_ID).

If you must use CASE, then I'd confine it to a function so you can re-use the logic elsewhere.

Upvotes: 5

bummi
bummi

Reputation: 27384

without a fixed order colum you could things like that, but the logic is not comprehensible.
Assuming first criteria is length of Language_id,
Second is Edition_id= Language_id,
rest is order of Language_id it could or work this way:

Declare @t table(Edition_id  int, Language_id int)
insert into @t values
(1,             1),
(1,             2),
(1,             13),
(1,             1),
(1,             13), 
(1,             2),
(5,             4),
(5,             1),
(5,             1),
(5,             4),
(5,             13),
(5,             5),
(5,             13);

Select * from @t
order by Edition_id,Case when len (Cast(Language_ID as Varchar(10)))=1 then '1' else '0' end
                   +case when Edition_id=Language_id  then '0' else '1' end
                   ,Language_ID

Upvotes: 1

KekuSemau
KekuSemau

Reputation: 6852

If there is no mathematical logic behind it, I would insert another column that can be used for proper sorting.
If you cannot do this, you can simply type out the rules for the relation like this: 

Order By Edition_Id,
case Edition_id
    when 1 then
        case Language_id
            when 13 then 1
            when 1 then 2
            when 2 then 3
        end
    when 5 then
        case Language_id
            when 13 then 1
            when 5 then 2
            when 1 then 3
            when 4 then 4
        end
end

Upvotes: 2

Related Questions