sort order

  • Hi

    How can I get query results in the same order i have given using

    'in' express

    for example

    select col_1,col_2 from table_1

    where col_1 in(5,1,3,8,2)

    select col_1,col_2 from table_1

    where col_1 in(1,3,5,4)

    The result should display according the order i have given 'in' order

    like

    col_1

    --------

    5

    1

    3

    8

    2

    Is there any way

    Thanks

    Padmaja

  • Hi,

    how it is showing output right now ?

  • -- Method 1

    DROP TABLE #InList

    CREATE TABLE #InList (RowID INT IDENTITY(1,1) NOT NULL, JoinValue INT)

    INSERT INTO #InList (JoinValue)

    SELECT 5 UNION ALL

    SELECT 1 UNION ALL

    SELECT 3 UNION ALL

    SELECT 8 UNION ALL

    SELECT 2

    -- NOTE - the sequence will fail if UNION is used instead of UNION ALL

    -- because UNION eliminates dupes; the processing reorders the input set.

    SELECT col_1,col_2

    FROM table_1 t

    INNER JOIN #InList i

    ON i.JoinValue = t.col_1

    ORDER BY i.RowID

    -- Method 2

    SELECT col_1,col_2

    FROM table_1

    WHERE col_1 IN (5,1,3,8,2)

    ORDER BY CASE col_1

    WHEN 5 THEN 1

    WHEN 1 THEN 2

    WHEN 3 THEN 3

    WHEN 8 THEN 4

    WHEN 2 THEN 5 END

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hey padmaja,

    how about you going through this following article and helping us help you?? ๐Ÿ™‚

    FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

    When u do so, i am sure a lot of us will help u instantly...

    So please post

    1. Table Structures - CREATE TABLE scripts

    2. Constraints or Indexes in the table, - CREATE SCRIPTS

    3. SAMPLE DATA - INSERT INTO TABLE scripts

    4. Desired output - some visual representation of this.

  • Padmaja.

    Create a tabular function

    which takes input VACHAR(1000)

    Returns Table (id identity(1,1),FindNo int)

    Write a code to insert the comma seperated string into the table

    SELECT * FROM fn_gettblOrder('5,1,3,8,2')

    id FIndNo

    1 5

    2 1

    3 3

    4 8

    5 2

    Now u can use Ur query with JOin of this Function....

    select Col_1,Col_2 from @table

    JOIN (SELECT id,findNo FROM fn_gettblOrder('5,1,3,8,2')) Ftbl on Ftbl.findNo=Col_1

    where Col_1 in(5,1,3,8,2) order by id ASC

    U may get the O/p what u expected.....& Use this same function where u need to get table from CSV values....

    :-):-)

    Thanks!

    Sasidhar Pulivarthi

  • Hi Sasidhar,

    It really helped me. Thanks

    Padmaja[font="Arial"][/font]

  • Sasidhar Pulivarthi (4/9/2010)


    Create a tabular function...

    Or better still use one of Chris' methods or:

    DECLARE @Example

    TABLE (

    col1 INTEGER NOT NULL PRIMARY KEY,

    col2 VARCHAR(10) NOT NULL

    );

    INSERT @Example

    (col1, col2)

    SELECT 1, 'One' UNION ALL

    SELECT 2, 'Two' UNION ALL

    SELECT 3, 'Three' UNION ALL

    SELECT 4, 'Four' UNION ALL

    SELECT 5, 'Five' UNION ALL

    SELECT 6, 'Six' UNION ALL

    SELECT 7, 'Seven' UNION ALL

    SELECT 8, 'Eight';

    SELECT col1, col2

    FROM @Example

    WHERE col1 IN (5,1,3,8,2)

    ORDER BY

    CHARINDEX

    (

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

    ',' + '5,1,3,8,2' +','

    ) ASC;

  • pmadhavapeddi22 (4/9/2010)


    Hi Sasidhar,

    It really helped me. Thanks

    Padmaja[font="Arial"][/font]

    Cool... can you post the function you ended up with, please? I'd like to see what you did. Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE function [dbo].[Test_GetOrderTable] (@string varchar(max))

    returns @tblOrder Table(id int identity(1,1),strings varchar(100))

    AS

    begin

    declare @LocalVar varchar(100)

    declare @pos int

    set @pos=1

    set @LocalVar=''

    set @string = ltrim(rtrim(@string))

    while (@pos <=len(@string))

    begin

    if (substring(@string, @pos,1)!=',')

    begin

    set @LocalVar =@LocalVar + substring(@string,@pos,1)

    set @pos=@pos+1

    end

    else

    begin

    if @LocalVar <> ''

    insert into @tblOrder values(ltrim(rtrim(@LocalVar)))

    set @pos= @pos+ 1

    set @LocalVar=''

    end

    end

    if @LocalVar <> ''

    insert into @tblOrder values(ltrim(rtrim(@LocalVar)))

    return

    end

    GO

    :-):-):-)

    Select * from [dbo].[Test_GetOrderTable] ('1,2,8,6,4,23,74,94,4,0')

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

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