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!
Could you give a data example?
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:
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:
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?
Did you change the order by that Rudy posted to use your sort order?
ORDER
BY CASE WHEN productname = 'this one'
THEN 'humpty' ELSE 'dumpty' END DESC
, Rank
, p.ID Desc
Thanks Guido - I think I didn’t quite comprehend Rudy’s solution at first - I am trying it now.
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:
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:
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:
CASE WHEN productname = 'my top product' THEN ORDER BY p.ID DESC
ELSE ORDER BY Rank ASC END
That gives a syntax error too…
let’s rewind back to post #3
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
ok, I got it now. Thanks Rudy for your help!!
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)
dear jsoft, you made at least 4 syntax errors
