DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: SQL query 'IN' with 'Order By'

  1. #1
    Join Date
    Feb 2008
    Posts
    1

    SQL query 'IN' with 'Order By'

    Hi,

    I have an SQL query that uses 'IN' as I want to get a number of records based on the values inserted into the query. The order of the values within the 'IN' is the order in which I want the records to be retrieved from the database and displayed.

    E.g.

    Select * from employees where empnum in (1234, 2345, 6643, 3456)

    I want the records to be displayed as:

    1234
    2345
    6643
    3456

    rather than, say, in numerical order ie:

    1234
    2345
    3456
    6643

    Do I need to use some sort of 'order by' statement, or is there a way to retrieve them in the original order?

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Welcome to DevX

    If you do an ORDER BY, it will either display them as:

    1234
    2345
    3456
    6643

    or

    6643
    3456
    2345
    1234

    depending on whether your ORDER BY is ascending or descending.

    If you do not do an ORDER BY, it will display them in the order in which they are stored in your database.

  3. #3
    Join Date
    Feb 2008
    Posts
    5
    Assuming you are using SQL Server 2000 and Above

    if Field empnum is a primary key it is not possible because an index is already created.

    Work Arround is add a identity field in table.
    While Executing Query U can use Order By <Identity Col Name>
    Lets not spoon feed and create pool of lazy coders

    - ComIT Solutions

  4. #4
    Join Date
    Mar 2004
    Location
    UK
    Posts
    31
    Try something like this:

    Select
    *
    from
    employees
    where
    empnum in (1234, 2345, 6643, 3456)
    order by
    case empnum
    when 1234 then 0
    when 2345 then 1
    when 6643 then 2
    when 3456 then 3
    else 4
    end,
    empnum

Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 05:14 PM
  2. Access SQL query in ASP page
    By Terry in forum ASP.NET
    Replies: 2
    Last Post: 03-12-2003, 08:17 AM
  3. Answered - Access SQL query in ASP page
    By Terry in forum ASP.NET
    Replies: 0
    Last Post: 03-03-2003, 11:46 AM
  4. how to write this SQL query and SQL assertion
    By Diana Perkins in forum Database
    Replies: 2
    Last Post: 11-22-2000, 10:14 AM
  5. SQL server administrative knowledge
    By Becky in forum Database
    Replies: 1
    Last Post: 07-11-2000, 03:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center