0

I'm attempting to do something similar to this question. I have this table:

<code>tab_id</code> is the second column. <code>order_in_tab</code> is the fourth column.

tab_id is the second column. order_in_tab is the fourth column.

I want to order by tab_id equal to 2 first, then rest of tab_id ascending, then order_in_tab ascending. 

select * 
from cam_to_tab_mapping 
where unit_id='90013550' 
order by (tab_id='2') asc, tab_id asc, order_in_tab asc

However, it says Incorrect syntax at '='.. I'm a complete SQL newbie, so I'm not sure what is wrong (or if I'm misunderstanding the linked solution from above).

Community
  • 1
  • 1
But I'm Not A Wrapper Class
  • 13,614
  • 6
  • 43
  • 65

4 Answers4

3

Try changing the query like this:

select * 
from cam_to_tab_mapping 
where unit_id='90013550' 
order by CASE WHEN tab_id='2' THEN 1 ELSE 0 END DESC, tab_id asc, order_in_tab asc
dotnetom
  • 24,551
  • 9
  • 51
  • 54
  • Close... Shouldn't the ASC be DESC since he wants those first? Or just switch the 1 and 0 in the case statement. – xQbert Jul 24 '14 at 19:27
  • 1
    Can you explain the `CASE WHEN tab_id='2' THEN 1 ELSE 0 END DESC` part in detail? – But I'm Not A Wrapper Class Jul 24 '14 at 19:34
  • This statement represents calculated value. If tab_id='2' then the calculated value is 1, otherwise it's 0. And then the ordering is done based on this calculated value – dotnetom Jul 24 '14 at 19:38
  • I think "order by CASE WHEN tab_id='2' THEN -1 ELSE tab_id END ASC, order_in_tab ASC" is the solution he's asking for. – Du D. Jul 24 '14 at 19:39
  • @dotnetom Wouldn't 0 be placed before 1 (referring to the `then 1 else 0` part)? Or does SQL not work that way? – But I'm Not A Wrapper Class Jul 24 '14 at 19:46
  • If ordering is ascending then 0 comes first and then 1, but after @xQbert noticed my mistake I changed the ordering to descending and in this case 1 comes first and then 0 – dotnetom Jul 24 '14 at 19:48
  • Essentially what it is doing is providing a calculated column to sort by. It's sorting in descending order anything with a tab_id of 2 first, then everything else, then the other sort sequences apply. It's spot on with the update IMO. – xQbert Jul 24 '14 at 19:51
  • Will this guarantee that case `tab_id='2'` will always be placed top of the list then the rest is order as ascending (not matter the mix of rows)? – But I'm Not A Wrapper Class Jul 24 '14 at 19:59
  • Yes, this will guarantee that items where tab_id=2 will come first, then all other items will come ordered by tab_id and order_in_tab in ascending order – dotnetom Jul 24 '14 at 20:01
1

I think you have a copy & paste error in your query.

select * 
from cam_to_tab_mapping 
where unit_id='90013550' 
order by (tab_id='2') asc, tab_id asc, order_in_tab asc

You have a logical expression as the first order by criteria.

Maybe you meant

select * 
from cam_to_tab_mapping 
where unit_id='90013550' and tab_id='2'
order by tab_id asc, order_in_tab asc
Spidey
  • 2,508
  • 2
  • 28
  • 38
  • I made the same mistake OP post says, "I want to order by tab_id equal to 2 first, then rest of tab_id ascending, then order_in_tab ascending." – xQbert Jul 24 '14 at 19:28
1

The question you linked is right. You just missunderstood the TYPE of the fields. There it has a string field which can be equalized to a string.

So in your case you have to do as this:

select * 
 from cam_to_tab_mapping 
where unit_id='90013550' 
order by (tab_id=2) DESC, 
        tab_id asc, order_in_tab asc

(tab_id=2) DESC will bring the id with 2 first on the results.

See it here on fiddle: http://sqlfiddle.com/#!2/15ffc/2

EDIT:

The OP said that it is using SQL Server. This answer is for MySQL. On SQL SERVER the correct way is using a CASE statement like:

select * 
 from cam_to_tab_mapping 
where unit_id='90013550' 
order by (case when tab_id=2 then 0 else 1 end), 
        tab_id, order_in_tab
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
0

In order by you can not specify tab_id=2. You'll have to add another dummy field which will have say 0 for tab_id=2 and 1 otherwise and order by that field first, then by tab_id. Try this way...

select mac, tab_id, unit_id, order_in_tab, 
(case when tab_id='2' then 0 else 1 end) as temp
from cam_to_tab_mapping 
where unit_id='90013550' 
order by temp, tab_id, order_in_tab

You (can but) don't need to specify asc in the order by clause, it is asc by default if you do not specify anything.

Swagata
  • 622
  • 5
  • 19