Order by data as per supplied Id in sql

Question

Query:

SELECT *
FROM [MemberBackup].[dbo].[OriginalBackup]
where ration_card_id in
(
1247881,174772,
808454,2326154
)

Right now the data is ordered by the auto id or whatever clause I'm passing in order by.

But I want the data to come in sequential format as per id's I have passed

Expected Output:

All Data for 1247881
All Data for 174772
All Data for 808454
All Data for 2326154

Note: Number of Id's to be passed will 300 000

Read the 3 Lac+ IDs into a table, or temporary table, and then go from there. — Tim Biegeleisen, Jan 23 '17 at 05:56
but the insertion on the temp table will not be in the given format right? — Arijit Mukherjee, Jan 23 '17 at 05:57
Possible duplicate of [SQL Server Select query with IN() and order by the same](http://stackoverflow.com/questions/41712032/sql-server-select-query-with-in-and-order-by-the-same) — Ullas, Jan 23 '17 at 06:00
@Ullas yes i think it's quite similar but the data i need to pass is more — Arijit Mukherjee, Jan 23 '17 at 06:07
It's doesn't depend on volume of data, you can get the output in the same way as low volume data. So my suggestion is to follow the link above. — Shushil Bohara, Jan 23 '17 at 06:15

Tim Biegeleisen · Answer 1 · 2017-01-23T05:59:34.267

1

One option would be to create a CTE containing the ration_card_id values and the orders which you are imposing, and the join to this table:

WITH cte AS (
    SELECT 1247881 AS ration_card_id, 1 AS position
    UNION ALL
    SELECT 174772, 2
    UNION ALL
    SELECT 808454, 3
    UNION ALL
    SELECT 2326154, 4
)

SELECT t1.*
FROM [MemberBackup].[dbo].[OriginalBackup] t1
INNER JOIN cte t2
    ON t1.ration_card_id = t2.ration_card_id
ORDER BY t2.position DESC

Edit:

If you have many IDs, then neither the answer above nor the answer given using a CASE expression will suffice. In this case, your best bet would be to load the list of IDs into a table, containing an auto increment ID column. Then, each number would be labelled with a position as its record is being loaded into your database. After this, you can join as I have done above.

edited Jan 23 '17 at 05:59

answered Jan 23 '17 at 05:53

Tim Biegeleisen

502,043
27
286
360

what if the number of id's i have to pass is more? – Arijit Mukherjee Jan 23 '17 at 05:55
@ArijitMukherjee Then can consider creating a formal table and using it. – Tim Biegeleisen Jan 23 '17 at 05:55
thanks for replying can you please explain how? AS if i will try to insert data into new table then also the order is not in the same manner – Arijit Mukherjee Jan 23 '17 at 05:56

score 0 · Answer 2 · answered Jan 23 '17 at 05:54

0

If the desired order does not reflect a sequential ordering of some preexisting data, you will have to specify the ordering yourself. One way to do this is with a case statement:

SELECT *
FROM [MemberBackup].[dbo].[OriginalBackup]
where ration_card_id in
(
1247881,174772,
808454,2326154
)
ORDER BY CASE ration_card_id
    WHEN 1247881 THEN 0
    WHEN 174772 THEN 1
    WHEN 808454 THEN 2
    WHEN 2326154 THEN 3
END

Stating the obvious but note that this ordering most likely is not represented by any indexes, and will therefore not be indexed.

answered Jan 23 '17 at 05:54

lc.

113,939
20
158
187

what if the number of id's i have to pass is more? – Arijit Mukherjee Jan 23 '17 at 05:55
2

@ArijitMukherjee Then you should try to identify a reason for the ordering rather than arbitrarily ordering by some external sequence of ids. Or if the sequence is external, add them to a table with an index first. – lc. Jan 23 '17 at 05:56

score 0 · Answer 3 · answered Jan 23 '17 at 07:40

0

Insert your ration_card_id's in #temp table with one identity column. Re-write your sql query as:

SELECT a.*
FROM [MemberBackup].[dbo].[OriginalBackup] a
JOIN #temps b
on a.ration_card_id = b.ration_card_id
order by b.id

answered Jan 23 '17 at 07:40

Yatin Patil

1
2

Order by data as per supplied Id in sql

3 Answers3