How to order rows as specified

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

  • 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

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

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

  • 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]

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

  • 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,'

    );

  • 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?

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

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

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