sql select without sort

Question

I guess this is a long shot but, is there a way to list the sql query without sorting...

eg. I have

select * from items 
where manufacID = 2 
or manufacID = 1
or manufacID = 4

and I don't want them to be listed in the asc or decs order, but as i typed in... so 2,1,4.

So, can i do that?

Answer 1

Yes, use this:

SELECT * FROM items
WHERE manufacID IN (2, 1, 4)
ORDER BY (manufacID = 2) ASC,
         (manufacID = 1) ASC,
         (manufacID = 4) ASC

The results are sorted in the order that the conditions match. Change ASC to DESC to reverse the order. This only works on databases which allow conditions in the sort clauses.

(Side note: why would you want to do this?)

Answer 2

You could add an extra column in the select as your sort column then order by that:

SELECT 
  *,
  CASE manufacID
    WHEN 2 THEN 1
    WHEN 1 THEN 2
    WHEN 4 THEN 3
  END AS sortOrder
FROM
  items
ORDER BY
  sortOrder

Answer 3

As you haven't specified the sorting, the records will be sorted in the natural order, which depends on the RDBMS you are using. In SQL Server for example the order is undefined.

You can create a value to order by from the values:

select * from items 
where manufacID in (2, 1, 4)
order by case manufacID
  when 2 then 1
  when 1 then 2
  when 4 then 3
end

Answer 4

SELECT * FROM
(
select 1 as sort, * from items 
where manufacID = 2 

union all

select 2 as sort, * from items 
where manufacID = 1

union all

select 3 as sort, * from items 
where manufacID = 4
)
order by sort

Answer 5

select * from items where manufacID = 2 
union all
select * from items where manufacID = 1
union all
select * from items where manufacID = 4