SQL query without Order By

Question

SELECT ItemID, Name
FROM tblItem
WHERE ItemID IN (4, 38, 39, 37, 16, 8, 15,14)

By using given above SQL query I am getting below result set which is in order by ItemID.

I want the result set without Order By or with any effect of sorting.

4   Item1
8   Item2
14  Item3
15  Item4
16  Item5
37  Item6
38  Item7 
39  Item8

@Tanner: There is no default order. The data is grabbed as fast as possible and that may be in order of the index but that is not predictable — juergen d, Aug 06 '15 at 10:20
mentioned query will return data in the order at which rows were inserted hence indexed by id — Mach Mitch, Aug 06 '15 at 10:22
@MachMitch: The data will be returned in the order that it's found. The order that the data was inserted is irrelevant. — Guffa, Aug 06 '15 at 10:26
is there any way to avoid default Order By, I want to show result set as mentioned in IN clause — Dev Developer, Aug 06 '15 at 10:27
@Guffa here's a similar question asked : http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order — Mach Mitch, Aug 06 '15 at 10:39
@user3271821 maybe this is the answer for you : http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause — Mach Mitch, Aug 06 '15 at 10:41

score 2 · Accepted Answer · answered Aug 06 '15 at 10:44

To get the items in a specific order you have to sort them that way. Specifying the values in the in statement in a specific order won't make the database fetch them in that order. The values in the in statement will be processed in some way, like sorted or put in a hash set so that they can be matched to an index or used in a table scan. Keeping the items in the original order would only make the query slower.

You can use a case to translate the identities into an ordering:

select
  ItemID, Name
from
  tblItem
where
  ItemID in (4, 38, 39, 37, 16, 8, 15,14)
order by
  case ItemID
    when 4 then 1
    when 38 then 2
    when 39 then 3
    when 37 then 4
    when 16 then 5
    when 8 then 6
    when 15 then 7
    when 14 then 8
  end

score 1 · Answer 2 · answered Aug 06 '15 at 10:19

The query will just return the items as they are found. The db will (probably, indexes and db implementation might influence this) start at the begining and work its way through the table comparing each of them to the list. If they match one of the itemIds in the list it will add it to the resultSet. Odds are that they get added to the db in order (the ItemIds are generated), so you will get the lowest ones first. But you have no guarantee that this will be the case.

is there any way to avoid default Order By, I want to show result set as mentioned in IN clause — Dev Developer, Aug 06 '15 at 10:34

score 1 · Answer 3 · answered Aug 06 '15 at 10:54

If you want the result set as specified by the in clause (or anything else), then you need to do an order by.

One way of doing this is using a VALUES clause and join:

SELECT i.ItemID, i.Name
FROM tblItem iJOIN
     (VALUES(4, 1), (38, 2), (39, 3), (37, 4), (16, 5), (8, 6), (15, 7), (14, 8)
     ) ids(id, priority)
     ON i.ItemId = ids.id
ORDER BY ids.priority;

YLG · Answer 4 · 2015-08-06T10:46:38.383

0

Try this:

With item_ids as
  (select item_id from tblitem 
    where item_id IN (4,8,9....))
 select * from item_ids
  where item_id <(select max(item_id) from item_ids) or   
        item_id=(select max(item_id) from item_ids);

edited Aug 06 '15 at 10:46

answered Aug 06 '15 at 10:25

YLG

855
2
14
36

is there any way to avoid default Order By, I want to show result set as mentioned in IN clause – Dev Developer Aug 06 '15 at 10:34
This method is known as common Table Expression (CTE). – YLG Aug 06 '15 at 10:42
http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER You can refer it from here. – YLG Aug 06 '15 at 10:42

score 0 · Answer 5 · answered Aug 09 '15 at 05:59

If you don't specify the order, the order is arbitrary. Consider a separate table or Common Table Expression which includes a SortOrder. An additional benefit is the JOIN syntax lets you keep SET-BASED action whereas the CASE statement will force your query to go row-by-agonizing-row (RBAR). Better than this would be to have your sort order and item ids in their own table so you don't have to create the CTE on the fly. Here's a CTE version:

;with SortOrder (ItemID,Sort)
as (
    select 4, 1
    union select 38,2
    union select 39,3
    union select 37,4
    union select 16,5
    union select 8,6
    union select 15,7
    union select 14,8
   )
select tblItem.ItemID
     , tblItem.Name
  from tblItem
  join SortOrder
    on SortOrder.ItemID = tblItem.ItemID
order by SortOrder.Sort

SQL query without Order By

5 Answers5