CODEWITHSUNDEEP
sqlsql-server
But I'm Not A Wrapper Class
But I'm Not A Wrapper Class

Reputation: 14286

Incorrect syntax at '='

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).

Upvotes: 0

Views: 105

Answers (4)

Jorge Campos
Jorge Campos

Reputation: 23361

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

Upvotes: 1

Swagata
Swagata

Reputation: 622

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.

Upvotes: 0

Spidey
Spidey

Reputation: 2589

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

Upvotes: 1

dotnetom
dotnetom

Reputation: 24901

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

Upvotes: 3

Related Questions