DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: SELECT STATEMENT order by criteria

  1. #1
    Mustafa Guest

    SELECT STATEMENT order by criteria


    i am selecting data from sql database in a particular order set by the user.
    however i need to arrange the data so that it is in the order that the user
    has selected. eg.

    In a VB listview the user clicks on list items in an order. A SELECT statement
    needs to be used to select all the records from a table where the primary
    key is IN the selected list items key record i.e.:

    SELECT * FROM tblStaff WHERE StaffID IN ( 67,12,92,2)

    I need the above statement to come out in the order of the IN STATEMENT:
    67,12,92,2.

    Thanks

  2. #2
    David Satz Guest

    Re: SELECT STATEMENT order by criteria

    my quick solution w/b to create a stored proc that inserted into a temp
    table, i.e.
    CREATE PROCEDURE dbo....AS
    @lStaffIDs varchar(8000)
    , ...
    SET NOCOUNT ON

    DECLARE @StaffID smallint
    , @commaPos int
    , @startPos int

    CREATE TABLE #display_orders (
    display_order smallint identity PRIMARY KEY
    , StaffID int NOT NULL )
    -- insert all display orders into temp table

    SET @commaPos = CHARINDEX(",",@lStaffIDs )
    IF LEN(@lStaffIDs ) > 0
    BEGIN
    SET @startPos = 1
    WHILE @commaPos > 0
    BEGIN
    SET @StaffID = SUBSTRING(@lStaffIDs , @startPos, @commaPos-@startPos)
    INSERT #display_orders (StaffID ) SELECT @StaffID
    SET @startPos = @commaPos+1
    SET @commaPos = CHARINDEX(",",@lStaffIDs , @startPos )
    END
    SET @StaffID = SUBSTRING(@lStaffIDs , @startPos,
    LEN(@lStaffIDs )-@startPos+1)
    INSERT #display_orders (StaffID) SELECT @StaffID
    END

    SELECT *
    FROM tblStaff s
    JOIN #display_orders x ON x.StaffID = s.StaffID
    ORDER BY display_order

    --
    HTH,
    David Satz
    Principal Web Engineer
    Hyperion Solutions
    { SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------
    "Mustafa" <bonefacekilla@hotmail.com> wrote in message
    news:3d5cd7ba$1@10.1.10.29...
    >
    > i am selecting data from sql database in a particular order set by the

    user.
    > however i need to arrange the data so that it is in the order that the

    user
    > has selected. eg.
    >
    > In a VB listview the user clicks on list items in an order. A SELECT

    statement
    > needs to be used to select all the records from a table where the primary
    > key is IN the selected list items key record i.e.:
    >
    > SELECT * FROM tblStaff WHERE StaffID IN ( 67,12,92,2)
    >
    > I need the above statement to come out in the order of the IN STATEMENT:
    > 67,12,92,2.
    >
    > Thanks




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