SQL Server - Compare ordered columns from multiple tables

Question

I have three tables full of item observations, each with say the following columns: Table2015: ItemName, ItemCount Table2014: ItemName, ItemCount Table2013: ItemName, ItemCount

and I'd like to get the 3 highest Counts for each ItemName from Table2015 with a special column that in the report that flags the row if the highest Count for that ItemName in Table2015 is greater than the highest Count for it in Table2014 and Table2013.

I have the following to get the high counts from Table2015, I'm not sure how to proceed to get what I need. Should I use another CTE with the other tables and join it in the final select somehow?

with counts as (
select e.ItemName, e.ItemCount, row_number() over (partition by e.ItemName order by cast(e.ItemCount as int) desc) as rk
from Table2015 e where e.ItemCount <> 'X')

select s.*, 
from counts s
where s.rk<4
order by s.ItemName,s.rk;

That already looks really complex, wouldn't select top 3 ItemName, ItemCount from counts order by ItemCount desc work? — James Z, Feb 24 '15 at 18:05

Matthew Jaspers · Accepted Answer · 2015-02-24T21:00:57.700

0

As long as item names are consistent between years, you just need to join to the other tables

with counts as (
select e.ItemName, e.ItemCount, row_number() over (partition by e.ItemName order by cast(e.ItemCount as int) desc) as rk
from Table2015 e where e.ItemCount <> 'X')

select s.ItemName
   , s.ItemCount
   , CASE WHEN CAST(t15.ItemCount AS INT) > ISNULL(CAST(t14.ItemCount AS INT), 0) THEN 1 ELSE 0 END AS GreaterThan2014
   , CASE WHEN CAST(t15.ItemCount AS INT) > ISNULL(CAST(t13.ItemCount AS INT), 0) THEN 1 ELSE 0 END AS GreaterThan2013
from counts s
    inner join counts t15 ON s.ItemName = t15.ItemName and t15.rk = 1
    left join (
        select ItemName, MAX(CASE WHEN IsNumeric(ItemCount) = 1 THEN CAST(ItemCount AS INT) ELSE -1 END)
        from Table2014 
        where ItemCount <> 'X' 
        group by ItemName
   ) t14 on s.ItemName = t14.ItemName 
    left join (
        select ItemName, MAX(CASE WHEN IsNumeric(ItemCount) = 1 THEN CAST(ItemCount AS INT) ELSE -1 END)
        from Table2013
        where ItemCount <> 'X' 
        group by ItemName
   ) t13 on s.ItemName = t13.ItemName 

where s.rk<4
order by s.ItemName,s.rk;

Also, you really shouldn't have an 'X' as a possibility in a count field. If you're counting something it should be typed as an INT.

edited Feb 24 '15 at 21:00

answered Feb 24 '15 at 18:07

Matthew Jaspers

1,546
1
10
13

Isn't this just a row by row comparison against everything in Table2014 and Table 2013 instead of comparing the max counts? I'm thinking I need to find the max count for each item in each table somehow. – free_to_fly_wi Feb 24 '15 at 19:17
I misread the question. It's updated to reflect what you're asking. – Matthew Jaspers Feb 24 '15 at 19:27
Gave me a Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'X' to data type int. I'm not sure which cast is actually failing. – free_to_fly_wi Feb 24 '15 at 20:48
Yeah, you should really not have X in a count column. Why not change those to NULL? – Matthew Jaspers Feb 24 '15 at 20:49
Unfortunately in the source of data, X has a logical meaning that is different from NULL - it means that there were some items, but we aren't quite sure how many. – free_to_fly_wi Feb 24 '15 at 20:55
It's almost certainly failing on the sub-queries. SQL is trying to CAST before applying the where. [This answer](http://stackoverflow.com/questions/12305823/mssql-cast-varcharcolumn-to-int-in-select-gets-executed-before-where-clause) addresses that. I've updated my query to reflect. – Matthew Jaspers Feb 24 '15 at 21:00
You're better off making the X a -99 or some other flag value when importing to SQL. Then you don't have to deal with casts. – Matthew Jaspers Feb 24 '15 at 21:03
Darn - one more issue. The GraterThan20XX columns shows a 1 for all top three rows of high counts from 2015. I thought the rk=1 join would have avoided this - but no? – free_to_fly_wi Feb 24 '15 at 21:13
Yes, if you want it so that the flag only shows for the Rank = 1 rows, then you can drop the self-join to counts and just compare the ItemCount for rk = 1 only. – Matthew Jaspers Feb 24 '15 at 21:21
Fixed it by limiting the subsequent table joins with rk=1 -- thanks! – free_to_fly_wi Feb 24 '15 at 21:22

SQL Server - Compare ordered columns from multiple tables

1 Answers1