How to order rows as specified

Question

How to order rows as specified

SQLUserC

SSCrazy

Points: 2345
More actions
April 19, 2010 at 11:18 am

#233995

Hey all,
So, I have a list of people whose names I want to query the database for. I get the IDs from an Excel spreadsheet, paste it into Notepad++, and replace the CRLFs with commas. I then run the following query:
select * from people
where personid in (1, 5, 9, 2, 4)
I want the results presented in the order that they are specified in in the in expression. By default, SSMS seems to order the results by the primary key column. I want to paste the names into the spreadsheet that I got the IDs from, so it'd be nice if the results were returned in that order.
I realize I could sort the spreadsheet by the IDs, paste the information in, and then restore the original order of the spreadsheet, but I would like to know if there is a way in SQL Server of having the records returned in the same order as they were specified in and in expression.
Does anyone know a way of doing this? I would appreciate any help that anyone can give.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

cjohn5552 (4/19/2010)
Hey all,
So, I have a list of people whose names I want to query the database for. I get the IDs from an Excel spreadsheet, paste it into Notepad++, and replace the CRLFs with commas.

Do you know that SSMS can do this for you? Copy the IDs from the spreadsheet, and paste them into a SSMS query window. Open up the find/replace dialog, check the "Use" checkbox, and then select "Regular Expressions". Then, in the "Find what" specify "\ n", and in "Replace With" specify ",".

Alternatively, in "Replace With", specify " UNION ALL \ nSELECT " to put the data in the format ready for use as shown below. (Note that I put a space between the "\" and the "n" so that it will show up here... you would not specify a space.)

I then run the following query:
select * from people
where personid in (1, 5, 9, 2, 4)
I want the results presented in the order that they are specified in in the in expression.

I think that this code would do it for you:

DECLARE @temp TABLE (ID int IDENTITY, personid int)

INSERT INTO @temp

SELECT 1 UNION ALL

SELECT 5 UNION ALL

SELECT 9 UNION ALL

SELECT 2 UNION ALL

SELECT 4

SELECT P.*

FROM people p

JOIN @temp t

ON t.personid = p.personid

ORDER BY t.ID

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

You left out critical info. How is the data sorted in the spreadsheet?

SQLUserC SSCrazy Points: 2345 More actions · Answer 3

I didn't know that SSMS could do that. I had tried pasting the CRLF into the Find field in the past, with no luck. That seems very useful, thanks. And thanks for the code. That seems like a good way to do it.

LutzM SSC Guru Points: 107049 More actions · Answer 4

To expand Waynes suggestion:

Instead of changing "\ n" into commas you could add another column to your spreadsheet with incrementing values and add another column with a formula that creates the insert statement. Something like ="SELECT " & $A1 &","& $B1 & " UNION ALL".

Copy and paste the resulting SELECT statement into Waynes INSERT statement and you're all set.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLUserC SSCrazy Points: 2345 More actions · Answer 5

The data is sorted in the spreadsheet based on when I entered the ID for the person (i.e. it is essentially not sorted).

Paul White SSC Guru Points: 150467 More actions · Answer 6

DECLARE @People

TABLE (

person_id INTEGER PRIMARY KEY,

data AS 'data for person #' + CONVERT(VARCHAR(12),person_id)

);

INSERT @People

VALUES (1), (2), (3), (4), (5),

(6), (7), (8), (9), (10);

SELECT person_id,

data

FROM @People

WHERE person_id IN (1, 5, 9, 2, 4)

ORDER BY

CHARINDEX

(

',' + CONVERT(VARCHAR(12),person_id) + ',',

',1,5,9,2,4,'

);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLUserC SSCrazy Points: 2345 More actions · Answer 7

Thanks for all the replies, folks. They've not only helped me resolve the problem, but also have expanded my general understanding of SQL.

One question that I have pertains to the usage of regular expressions like \ n. After reading up on regular expressions in Books Online, they seem really useful. In fact, I was wondering if there is a notepad utility that allows the usage of them. Is anyone aware of any? I like Notepad++: does it support them?

SQLUserC SSCrazy Points: 2345 More actions · Answer 8

Hi--the answer to this question is obvious, so I apologize for asking it without doing any research. However, to those who are reading this thread and were wondering this: Yes, Notepad++ does support regular expressions. Simply select the "Regular expression" radio button in the lower-left hand corner of the search box.