Post reply

Using " IN " inside " WHERE " clause

  • May 8, 2010 at 1:40 am

    #224221

    Hello,

    I have a Data Table 'InvoiceDetails' which contains Invoice and its Details.

    Below is my Query,

    SELECT *

    FROM InvoiceDetails

    WHERE InvoiceNo IN ('1248','1063','963','1524')

    Can anyone pls tell me how to the data in the Below order

    ---------------------------------------------------------

    InvoiceNo | InvoiceValue | StockCode |

    ---------------------------------------------------------

    1248 | 2000 | AAAAA |

    ---------------------------------------------------------

    1063 | 5000 | BBBBB |

    --------------------------------------------------------

    963 | 1000 | CCCCC |

    --------------------------------------------------------

    1524 | 8000 | DDDDD |

    ---------------------------------------------------------

    i.e., the result should be in the same order as mentioned inside " IN Clause"

    Regards and Thanks,

    Kavya

  • May 8, 2010 at 4:55 am

    #1164546

    You would nee to provide an ORDER BY clause based on a column that will hold any order criteria.

    Since SQL Server does not perform a row-by-row processing, it will select all matching rows and, without a given ORDER BY clause, return the result more or less random (it might be orderd by the clustered index, but there's no guarantee either...)


    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]

  • May 8, 2010 at 8:00 pm

    #1164649

    lmu92 (5/8/2010)

    You would nee to provide an ORDER BY clause based on a column that will hold any order criteria.

    Since SQL Server does not perform a row-by-row processing, it will select all matching rows and, without a given ORDER BY clause, return the result more or less random (it might be orderd by the clustered index, but there's no guarantee either...)

    I'm thinking he wants it in the same order list in the IN.

    --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)

  • May 8, 2010 at 8:02 pm

    #1164650

    c_kavya_shri (5/8/2010)

    Hello,

    I have a Data Table 'InvoiceDetails' which contains Invoice and its Details.

    Below is my Query,

    SELECT *

    FROM InvoiceDetails

    WHERE InvoiceNo IN ('1248','1063','963','1524')

    Can anyone pls tell me how to the data in the Below order

    ---------------------------------------------------------

    InvoiceNo | InvoiceValue | StockCode |

    ---------------------------------------------------------

    1248 | 2000 | AAAAA |

    ---------------------------------------------------------

    1063 | 5000 | BBBBB |

    --------------------------------------------------------

    963 | 1000 | CCCCC |

    --------------------------------------------------------

    1524 | 8000 | DDDDD |

    ---------------------------------------------------------

    i.e., the result should be in the same order as mentioned inside " IN Clause"

    Regards and Thanks,

    Kavya

    This is important to figuring out how to do what you ask. Where does the data from the IN clause come from? Is it a passed in parameter, hard coded as you have it, embedded SQL in a GUI, or something else? Don't be bashful with the details here... there are answers for each method but I have to be sure as to where the data in the IN clause is coming from?

    --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)

  • May 9, 2010 at 8:46 am

    #1164698

    If it is hard coded, I'm thinking as below.

    SELECT *

    FROM InvoiceDetails

    WHERE InvoiceNo IN ('1248','1063','963','1524')

    ORDER BY CASE InvoiceNo

    WHEN '1248' THEN 1

    WHEN '1063' THEN 2

    WHEN '963' THEN 3

    WHEN '1524' THEN 4

    END

    Is there any other way if it is hard coded ?

    Thanks & Regards,
    MC

  • May 9, 2010 at 9:20 am

    #1164707

    only4mithunc (5/9/2010)

    If it is hard coded, I'm thinking as below.

    SELECT *

    FROM InvoiceDetails

    WHERE InvoiceNo IN ('1248','1063','963','1524')

    ORDER BY CASE InvoiceNo

    WHEN '1248' THEN 1

    WHEN '1063' THEN 2

    WHEN '963' THEN 3

    WHEN '1524' THEN 4

    END

    Is there any other way if it is hard coded ?

    That's the right line of thinking but I try to avoid hardcoding because it makes a mess in the future when things change. We still need to know what the source of these invoice numbers is so we can come up with a good and proper non-hardcoded solution.

    --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)

  • May 9, 2010 at 9:28 am

    #1164709

    I agree with you Jeff.

    Thanks & Regards,
    MC

  • May 9, 2010 at 1:41 pm

    #1164720

    Heh... don't ya just love it when the OP doesn't even show up for 24 hours? 😉

    --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)

  • May 9, 2010 at 9:26 pm

    #1164785

    Hello ,

    The data inside the " IN Clause" is hard coded and I want the output in the same order as it is inside the "IN Clause". Using "Order By" will not solve my problem.

    Thanks for the Quick Response

    Regards,

    Kavya

  • May 9, 2010 at 9:57 pm

    #1164797

    Hello Mithun,

    Thanks for the code. It solved my Problem.

    Regards,

    Kavya

  • May 9, 2010 at 10:15 pm

    #1164804

    c_kavya_shri (5/9/2010)

    Hello ,

    The data inside the " IN Clause" is hard coded and I want the output in the same order as it is inside the "IN Clause". Using "Order By" will not solve my problem.

    Thanks for the Quick Response

    Regards,

    Kavya

    SELECT *

    FROM InvoiceDetails

    WHERE InvoiceNo IN ('1248','1063','963','1524')

    ORDER BY CASE InvoiceNo

    WHEN '1248' THEN 1

    WHEN '1063' THEN 2

    WHEN '963' THEN 3

    WHEN '1524' THEN 4

    END

    c_kavya_shri (5/9/2010)

    Hello Mithun,

    Thanks for the code. It solved my Problem.

    Regards,

    Kavya

    BWAA-HAAA... I'm thinking that ORDER BY DID solve your problem. 😛

    But, unless it's a one-off script, you or someone in the future still have a problem whether you care to acknowledge it or not... hardcoding.

    --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)

  • May 10, 2010 at 6:06 am

    #1164918

    Hi Jeff,

    I would like to know how you will approach this if the values are not hard coded, say it is from some table or it is a string with more than 100 values.

    Thanks & Regards,
    MC

  • May 10, 2010 at 6:49 am

    #1164937

    only4mithunc (5/10/2010)

    Hi Jeff,

    I would like to know how you will approach this if the values are not hard coded, say it is from some table or it is a string with more than 100 values.

    Sure... That's why I was asking what the "source" of the criteria is.

    If it's meant to be relatively "static", then a table would do the trick nicely because if you need to change the criteria, you don't have to find and change the code... just update the table for the invoice number and position that it should appear in for the ORDER BY. That also works especially well if the criteria is used in other code.

    However, its difficult for me to believe that invoice numbers would be "static", not just in this case, but in any case. Although the problem could certainly be done using dynamic SQL, you have to be careful about SQL Injection with anything that may be public facing and, sometimes, with things that face internal people depending on who has access. With that in mind, I'd have a CSV splitter built into a CTE or an Inline Table Valued Function to parse the parameters and join to those parsed parameters. Of course, the splitter would also return a number representing the position that each invoice number should appear in for the ORDER BY.

    Some people will pass the parameters as XML and parse that. That's fine, I suppose... I like to be kinder to the pipe, though.

    If it's a true one-off piece of code, then hardcoding would probably be fine but I've also been through supposed one-off situations like this where they recur more often than not. It may be worth building a small stored proc for such a thing.

    For more information on how you can pass 1, 2, or even 3 dimensional parameters in simple delimited formats, please see the following article...

    http://www.sqlservercentral.com/articles/T-SQL/63003/

    ... and a good splitter iTVF will make life even easier.

    --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)

  • May 10, 2010 at 7:08 am

    #1164955

    Thanks a lot for the reply Jeff.

    Thanks & Regards,
    MC

  • May 10, 2010 at 9:04 pm

    #1165316

    You bet, MC... thanks for the feedback.

    --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)

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy