ORDER BY Should be same as my input in IN()

  • DECLARE @T TABLE(ID INT,NAME VARCHAR(10))

    INSERT INTO @T VALUES(1,'PPP')

    INSERT INTO @T VALUES(2,'AAA')

    INSERT INTO @T VALUES(2,'ZZZ')

    INSERT INTO @T VALUES(3,'XYZ')

    INSERT INTO @T VALUES(4,'PQR')

    INSERT INTO @T VALUES(5,'ZAB')

    SELECT * FROM @T WHERE NAME IN('XYZ','AAA','PQR')

    IDNAME

    ----------

    2AAA

    3XYZ

    4PQR

    --But I need the result as below (i.e, the order should be same as how I had input the value in the IN() )

    IDNAME

    -----------

    3XYZ

    2AAA

    4PQR

    -- CAN YOU PLEASE GIVE ME A SINGLE QUERY WITHOUT DOING ANY FUNCTIONS OR TEMP TABLES

  • Maybe dynamic sql for the order by

    A cte with union all for each value might do it.


    Cursors never.
    DTS - only when needed and never to control.

  • ;with cte as

    (

    select val = 'XYZ', seq = 1

    union all

    select val = 'AAA', seq = 1

    union all

    select val = 'PQR', seq = 1

    )

    SELECT t.*

    FROM @T t

    join cte

    on t.Name = cte.val

    order by cte.seq

    If you put the string into a variable you could use the cte to parse it into a table.


    Cursors never.
    DTS - only when needed and never to control.

  • You cannot force the order as per list in IN.

    You should consider re-factoring your query.

    One of the ways:

    SELECT t.*

    FROM @T t

    JOIN (VALUES (1,'XYZ'),(2,'AAA'),(3,'PQR')) lst(rn,name)

    ON lst.name= t.name

    ORDER by lst.rn

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Is the IN list an input parameter of some sort, or is it hard-coded into the query? You weren't clear on that, but it's the deciding factor on how you do something like this.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Logic ???? :w00t:

    DECLARE @T TABLE(ID INT,NAME VARCHAR(10))

    INSERT INTO @T VALUES(1,'PPP')

    INSERT INTO @T VALUES(2,'AAA')

    INSERT INTO @T VALUES(2,'ZZZ')

    INSERT INTO @T VALUES(3,'XYZ')

    INSERT INTO @T VALUES(4,'PQR')

    INSERT INTO @T VALUES(5,'ZAB')

    DECLARE @T_INPUT TABLE(ID int IDENTITY(1,1) , NAME VARCHAR(10))

    INSERT INTO @T_INPUT VALUES('XYZ')

    INSERT INTO @T_INPUT VALUES('AAA')

    INSERT INTO @T_INPUT VALUES('PQR')

    /* SELECT * FROM @T WHERE NAME IN('XYZ','AAA','PQR') */

    SELECT AA.*

    FROM @T AA,

    @T_INPUT BB

    WHERE AA.NAME=BB.NAME

    ORDER BY BB.ID

  • ....

    SELECT AA.*

    FROM @T AA,

    @T_INPUT BB

    WHERE AA.NAME=BB.NAME

    ORDER BY BB.ID

    Here I will play J.CELKO: Mate, you should really learn how to use ANSI standard JOINS...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene Elutin (12/11/2012)


    Here I will play J.CELKO: ......

    Steady on, don't overdo it :hehe:

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (12/11/2012)


    Eugene Elutin (12/11/2012)


    Here I will play J.CELKO: ......

    Steady on, don't overdo it :hehe:

    I hope I didn't, as I have not complained about lack of PK and didn't state ISO numbers and didn't use "punch card" preamble... :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • CELKO (12/11/2012)


    >> --But I need the result as below (i.e, the order should be same as how I had input the value in the IN() ) <<

    You have no idea how RDBMS and SQL work. The list in the IN() predicate is a set; sets have no order. The result of a query is a set; sets have no ordering. Repeat that phrase until you understand it. SETS HAVE NO ORDERING.

    Now, read that book on basic RDBMS you skipped and look for Dr. Codd's 12 rules. Look at the one known as “The Information Principle”, which tells us that ALL relationships are modeled as scalar values in the columns of rows in tables. Where is the column that models your ordering relationship? It does not exists!

    What you want to do it fine in a punch card or magnetic tape file system, where the sequential records would provide the ordering you want. We would use an array for the IN list and loop from A[1] to A[n] , writing to a scratch tape in that order. But this is not SQL or RDBMS!

    You mindset is wrong. Not a little off, but totally fundamentally wrong. You are the Flat Earth kid in Geography class, the creationist in Biology class, the Celestial Spheres advocate in an astronomy class. YOu have a lot of un-learning to do.

    Joe, considering the posts right before yours, this was a thing of beauty. Kind of like H-bomb explosions are a thing of beauty their own special way.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (12/11/2012)


    CELKO (12/11/2012)


    >> --But I need the result as below (i.e, the order should be same as how I had input the value in the IN() ) <<

    You have no idea how RDBMS and SQL work. The list in the IN() predicate is a set; sets have no order. The result of a query is a set; sets have no ordering. Repeat that phrase until you understand it. SETS HAVE NO ORDERING.

    Now, read that book on basic RDBMS you skipped and look for Dr. Codd's 12 rules. Look at the one known as “The Information Principle”, which tells us that ALL relationships are modeled as scalar values in the columns of rows in tables. Where is the column that models your ordering relationship? It does not exists!

    What you want to do it fine in a punch card or magnetic tape file system, where the sequential records would provide the ordering you want. We would use an array for the IN list and loop from A[1] to A[n] , writing to a scratch tape in that order. But this is not SQL or RDBMS!

    You mindset is wrong. Not a little off, but totally fundamentally wrong. You are the Flat Earth kid in Geography class, the creationist in Biology class, the Celestial Spheres advocate in an astronomy class. YOu have a lot of un-learning to do.

    Joe, considering the posts right before yours, this was a thing of beauty. Kind of like H-bomb explosions are a thing of beauty their own special way.

    I was thinking the exact same thing as soon as Joe brought up the punch card.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Exactly as I thought - it's really hard to overdo the real master of "outburst"... :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • I think we all wanted to say that :).


    Cursors never.
    DTS - only when needed and never to control.

  • Every time i open that database management window now.. that thought is there..

    "Is what I am doing wrong.. not a LITTLE bit wrong.. but TOTALLY FUNDAMENTALLY WRONG?" :w00t:

    Should I be buying a book on RDBMS and learning the earth is round?!?!?

  • Based on Dr Codd's rule, ALL existing relational DBMSs are "fundamentally wrong". NONE of them come close to implementing even the initial 12 "rules".

    So, if we all behaved as Celko wishes, there'd be NO Oracle, NO Sybase, NO SQL Server, NONE of it.

    In pursuit of theoretical perfection, we'd give up all the real gains made from a best-effort now.

    The first plane was pitiful compared to what we use know. So should we have declared the Wright brothers had it "fundamentally wrong" and waited another 50 years to fly??

    Sometimes we use the tools available to us as required by reality, not theory. Yes, fundamentals are important, but they can't be allowed to overshadow getting the job done. We've seen the effects of detached theory vs reality in the disaster that "classroom economics" has wrought in the real world.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 15 posts - 1 through 15 (of 60 total)

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