August 15, 2012 at 11:43 am
I am trying to order rows. Simple enough, (and I'm a little embarassed to have to post this) but there a unique business need. It is well known data isn't always entered correctly here, and a certain colomn defaults to zero. Therefore, a zero, doesn't necessarily mean zero. So, when that column1 = 0, I want the program to sort on a different column, but only when column1 = 0. So I put in a CASE stmt.
Here is an example from Northwind, and what I'd like is if there is a zero in UnitsInStock, then order by UnitPrice.
SELECT [CategoryID]
,[UnitPrice]
,[UnitsInStock]
,ROW_NUMBER() over (partition by categoryid order by CASE when unitsinstock = 0 THEN Unitprice else unitsinstock end desc)
FROM [Northwind].[dbo].[Products]
I have finally figured out why this doesn't work, but I still don't know how to make it do what I want. If you look at the results for CategoryID = 2
CategoryID UnitPrice UnitsInStock
----------- --------------------- ------------ --------------------
2 25.00 120 1
2 28.50 113 2
2 21.05 76 3
2 22.00 53 4
2 15.50 39 5
2 13.00 32 6
2 19.45 27 7
2 43.90 24 8
2 21.35 0 9
2 10.00 13 10
2 40.00 6 11
2 17.00 4 12
You see the ordering put the value of UnitPrice (21.35) into the ranking of values for UnitsInStock. But what I'd like is since there is a zero in the UnitsInStock, then the ordering for CategoryID = 2 should be by UnitPrice DESC, and not UnitsInStock at all.
Is this possible? Thank you for any help.
Amy
August 15, 2012 at 11:52 am
Split the query vertically, based on 0 vs non-0 in UnitsInStock, assign a "SortValue" column in each of those queries, union the two queries together, then do an outer query that does the Row_Number piece.
- 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
August 15, 2012 at 12:02 pm
Put foot in mouth here... Next time I'll read your whole post before asking about homework... Sorry Amy.
Jared
CE - Microsoft
August 15, 2012 at 12:05 pm
I'm not sure if this will give a good performance. Maybe someone else will come with a better idea.
SELECT [CategoryID]
,[UnitPrice]
,[UnitsInStock]
,ROW_NUMBER() over (partition by categoryid
order by CASE when 0 IN (SELECT x.unitsinstock
FROM [Northwind].[dbo].[Products] x
WHERE x.CategoryID = t.CategoryID )
THEN Unitprice else unitsinstock end)
FROM [Northwind].[dbo].[Products] t
August 15, 2012 at 12:11 pm
GSquared, Thank you for this solution. This in deed does do exactly what I was asking for, but maybe I'm making it too complicated? Now that I'm looking at it, I think I'm giving myself a little too much trouble than it's worth.
WITH CTE AS(
SELECT [CategoryID]
,[UnitPrice]
,[UnitsInStock]
,SortColumn = ROW_NUMBER() over (partition by categoryid order by UnitPrice desc )
FROM [Northwind].[dbo].[Products]
WHERE [CategoryID] IN (
select CategoryID
FROM [Northwind].[dbo].[Products]
Group by CategoryID
HAVING MIN(UnitsInStock) = 0)
UNION ALL
SELECT [CategoryID]
,[UnitPrice]
,[UnitsInStock]
,SortColumn = ROW_NUMBER() over (partition by categoryid order by UnitsInStock desc )
FROM [Northwind].[dbo].[Products]
WHERE [CategoryID] IN (
select CategoryID
FROM [Northwind].[dbo].[Products]
Group by CategoryID
HAVING MIN(UnitsInStock) <> 0))
SELECT * FROM CTE
order by CategoryID, SortColumn
August 15, 2012 at 12:18 pm
Luis, You make my query look like a school kid's art project. How do you think up that kind of thing? Thank you!
August 15, 2012 at 12:27 pm
I'm confused... Why didn't your original query work? I just tested your original query in AdventureWorks and got the required results....
Jared
CE - Microsoft
August 15, 2012 at 12:32 pm
Amy.G (8/15/2012)
Luis, You make my query look like a school kid's art project. How do you think up that kind of thing? Thank you!
hahaha Thank you Amy, you made my day.
I just modified your original condition. Instead of working with values, I thought about working with the whole set. It's something I've learned from others here.
August 15, 2012 at 12:34 pm
Ugh, I am missing so much and not catching on! Sorry about the homework question, I should have originally read your whole post. I'm a bit busy today 🙂 I resign from this post for my inability to read and pay attention today.
Jared
CE - Microsoft
August 15, 2012 at 12:43 pm
Jared,
The order should be determined by UnitsInStock for each CategoryID, unless there is a zero for any value in UnitsInStock for that particular CategoryID, the order should be determined by UnitPrice.
The original query ordered always by UnitsInStock and used the value from UnitPrice whenever the value from UnitsInStock was zero.
It's confusing, and got that impression too.
August 15, 2012 at 12:46 pm
I retired too, Jared. The table I'm working with is way too big for the cost in performance this is adding. I get into these mind-sets where I need the puzzle solved, and lose sight of the objective. I'll just sort by the other column. CASE closed 😉
August 15, 2012 at 12:52 pm
Luis Cazares (8/15/2012)
Jared,The order should be determined by UnitsInStock for each CategoryID, unless there is a zero for any value in UnitsInStock for that particular CategoryID, the order should be determined by UnitPrice.
The original query ordered always by UnitsInStock and used the value from UnitPrice whenever the value from UnitsInStock was zero.
It's confusing, and got that impression too.
I got it now. Amy, I am so sorry for jumping the gun here. How does this work, if you like cte's:
WITH cte (categorySort)
AS
(
SELECT DISTINCT categoryId
FROM Products
WHERE UnitsInStock = 0
)
SELECT [CategoryID]
,[UnitPrice]
,[UnitsInStock]
,ROW_NUMBER() over (partition by categoryid order by CASE when cte.categorySort IS NULL THEN UnitsInStock else UnitPrice end desc)
FROM Products
LEFT JOIN cte
ON #temp.CategoryID = cte.categorySort
Jared
CE - Microsoft
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply