SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Server query

    I have a simple select statement for SQL server, with a couple of joins. It just returns a list of products (by category).

    What I need to do is have the very first product result be always the same (for just one of the categories). I don't know how to accomplish that. Any advice? TYIA!
    renkai.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Could you give a data example?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Renkai View Post
    What I need to do is have the very first product result be always the same (for just one of the categories).
    Code:
    ORDER
        BY CASE WHEN productname = 'this one'
                THEN 'humpty' ELSE 'dumpty' END DESC
         , category
         , productname
    for the desired product, the sort key is 'humpty' and for all others it's 'dumpty', and since the sort is DESC, all humpties (there being only this one) come before all dumpties, and the dumpties are then sorted by category and product as before
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    ORDER
        BY CASE WHEN productname = 'this one'
                THEN 'humpty' ELSE 'dumpty' END DESC
         , category
         , productname
    for the desired product, the sort key is 'humpty' and for all others it's 'dumpty', and since the sort is DESC, all humpties (there being only this one) come before all dumpties, and the dumpties are then sorted by category and product as before
    Thanks for that. Problem is, I need to keep the other sort order in tact. My default sort order is: ORDER BY Rank, p.ID Desc. Rank is a list we keep of top-selling products, but not all products have a unique Rank. What I currently have for a data set is:


    ID ProdName ProdCategory ProdPrice Rank etc
    -------------------------------------------------------
    540 GreatProduct SameCat 15.00 1 etc
    201 CoolWidget1 SameCat 21.50 2 etc
    222 FavoriteOne SameCat 9.00 3 etc <---- this one should be first in the set
    409 AnotherProd SameCat 35.50 4 etc
    177 BadSeller3... SameCat 7.00 99 etc
    421 WorstSelling SameCat 15.00 99 etc

    What I want is:

    ID ProdName ProdCategory ProdPrice Rank etc
    -------------------------------------------------------

    222 FavoriteOne SameCat 9.00 3 etc <---- here it's first, but Rank is still kept for the rest of the products
    540 GreatProduct SameCat 15.00 1 etc
    201 CoolWidget1 SameCat 21.50 2 etc
    409 AnotherProd SameCat 35.50 4 etc
    177 BadSeller3... SameCat 7.00 99 etc
    421 WorstSelling SameCat 15.00 99 etc


    I was wondering about a UNION - but would that be overkill?
    renkai.com

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Renkai View Post
    Thanks for that. Problem is, I need to keep the other sort order in tact. My default sort order is: ORDER BY Rank, p.ID Desc.
    Did you change the order by that Rudy posted to use your sort order?
    Code:
    ORDER
        BY CASE WHEN productname = 'this one'
                THEN 'humpty' ELSE 'dumpty' END DESC
         , Rank
         , p.ID Desc

  6. #6
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guido - I think I didn't quite comprehend Rudy's solution at first - I am trying it now.
    renkai.com

  7. #7
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This works but it's not maintaining the sort order for the rest of the items. It seems I can only place the "DESC" at the end of the CASE statement.

    So although the following works:

    Code:
    ORDER BY CASE WHEN productname = 'my top product'
                THEN 'humpty' ELSE 'Rank' END DESC
    What happens is the product Ranking ends up being descending:

    My top Product is result 1
    Product of Rank 10 is result 2
    Product of Rank 9 is result 3
    Product Rank 8 is result 4
    etc

    What I want to say is something along these lines:

    Code:
    ORDER BY CASE WHEN productname = 'my top product'
                THEN 'humpty' ELSE 'Rank' ASC END DESC
    Problem is, the above gives the error "Incorrect syntax near the keyword 'ASC'."

    The result I want would be this:

    My top Product is result 1
    Product Rank 1 is result 2
    Product Rank 2 is result 3
    Product Rank 3 is result 4
    etc

    I can't figure a way to get this to work... for example I try this:

    Code:
    CASE WHEN productname = 'my top product' THEN ORDER BY p.ID DESC
    ELSE ORDER BY Rank ASC END
    That gives a syntax error too...
    renkai.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    let's rewind back to post #3
    Code:
    ORDER
        BY CASE WHEN productname = 'this one'
                THEN 'humpty' ELSE 'dumpty' END DESC
         , category
         , productname
    where did 'Rank' come from?

    you said you had a list of products by category

    this is why i wrote category and productname as 2nd and 3rd sort fields

    the first sort field is because you wanted the very first product result be always the same

    so don't touch the CASE expression at all, other than to change 'this one' to the actual product name that you want coming up first

    but please do look at the 2nd and 3rd sort fields
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, I got it now. Thanks Rudy for your help!!
    renkai.com

  10. #10
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i giving a example like Bank Account have many records and its query allwayes asking about these

    SELECT ACCT_NO, TYPE, FROM ACCT_MSTER GROUP BY PRODUCT EXIST IN (SELECT *FROM ACCT_DTLS)

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    dear jsoft, you made at least 4 syntax errors

    http://www.quickmeme.com/meme/3oyvd3/



    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •