September 1, 2011 at 1:09 pm
I need to get max values for each row and its column name.
For ex:
My table:
Zipcode West East North South
10001 50 10 100 5
10002 100 200 26 180
10003 0 0 0 0
....
Output table:
Zipcode West East North South Maxvalue colname
10001 50 10 100 5 100 North
10002 100 200 26 180 200 East
10003 0 0 0 0 0 N/A
....
Code :
SELECT ZIPCODE,maxvalue,colname
FROM
(
SELECT ZIPCODE, West, East, North, South
FROM dbo.tbl1
) ua
UNPIVOT
(maxvalue for colname in (West, East, North, SouthOTHRACE_CY,RACE2UP_CY)) ub
Results: ( I just want 96708 5884 west, not sure why its pulling all the zipcodes)?
96708 5884 West
96708 75 East
96708 56 North
96708 847 South
96713 594 West
96713 3 South
96713 7 East
96713 140 North
September 1, 2011 at 1:16 pm
If you have a limited number of columns, you could use a case statement. Alternatively, try using a cte to "normalize" the data before aggregating.
September 1, 2011 at 1:19 pm
I have 30,000 records..I almost got it but if you saw my code and results it shows all the values for the same zipcode. I just need max value, column name for particular zipcode.
For ex:
96708 5884 West
92113 100 East
etc.,
September 1, 2011 at 1:33 pm
sql4us (9/1/2011)
I have 30,000 records..I almost got it but if you saw my code and results it shows all the values for the same zipcode. I just need max value, column name for particular zipcode.For ex:
96708 5884 West
92113 100 East
etc.,
To do this, you need to rank the data after you have normalized it. Try using the Row_Number() function to achieve that.
September 1, 2011 at 1:47 pm
Martin Schoombee (9/1/2011)
sql4us (9/1/2011)
I have 30,000 records..I almost got it but if you saw my code and results it shows all the values for the same zipcode. I just need max value, column name for particular zipcode.For ex:
96708 5884 West
92113 100 East
etc.,
To do this, you need to rank the data after you have normalized it. Try using the Row_Number() function to achieve that.
Thanks a lot for your reply
I tried this code:
SELECT ZIPCODE,maxvalue,colname, row_number() over (partition by ZIPCODE order by ZIPCODE, maxvalue desc) col
FROM
(
SELECT ZIPCODE, West, East, South, North
FROM do_tbl1
) ua
UNPIVOT
(maxvalue for colname in (West, East, South, North)) ub
Results:
zipcode maxvalue colname col
10022 28641 West 1
10022 3238 North 2
10022 701 South 3
10022 507 East 4
10023 51931 East 1
10023 7348 West 2
10023 4917 South 3
10023 2402 north 4
I am unable to get: Please let me know where I am doing wrong?
zipcode maxvalue colname col
10022 28641 West 1
10023 51931 East 1
September 1, 2011 at 1:53 pm
Almost there 🙂
Two things to consider:
1. Your order by clause should only include the maxvalue column and not the zip code.
2. Encapsulate your whole query in a cte, and then select the rows from the cte where the col field = 1
Like so:
;with MyCte
as
(
<your query>
)
select * from MyCte where col = 1
September 1, 2011 at 2:08 pm
Martin Schoombee (9/1/2011)
Almost there 🙂Two things to consider:
1. Your order by clause should only include the maxvalue column and not the zip code.
2. Encapsulate your whole query in a cte, and then select the rows from the cte where the col field = 1
Like so:
;with MyCte
as
(
<your query>
)
select * from MyCte where col = 1
Thank you so much...Now it makes sense. I have one more question
I want to include all column names to display
For ex:
Zipcode West East North South maxvalue Colname
00001 100 10 5 1 100 West
00002 2 88 4 99 99 South
....
Right now it just shows,
Zipcode maxvalue colname
When I include all column names it says invalid column name...not sure if I am missing something..so close...thanks again 🙂
September 1, 2011 at 2:33 pm
sql4us (9/1/2011)
Thank you so much...Now it makes sense. I have one more questionI want to include all column names to display
For ex:
Zipcode West East North South maxvalue Colname
00001 100 10 5 1 100 West
00002 2 88 4 99 99 South
....
Right now it just shows,
Zipcode maxvalue colname
When I include all column names it says invalid column name...not sure if I am missing something..so close...thanks again 🙂
In that case (and if you have only one record for each zip code), you should rather use a case statement and not the elaborate way of denormalizing and then normalizing again.
Something like this:
select ZipCode
, West
, East
, North
, South
, case
when West >= East and West >= North and West >= South then West
when East >= West and East >= North and East >= South then East
when North >= East and North >= West and North >= South then North
when South >= East and South >= North and South >= West then South
end as MaxValue
from <table>
Edit: At least you've learnt a few valuable techniques here today 🙂
September 2, 2011 at 6:03 am
Thanks a lot for replying and explaining step by step 🙂 Really appreciate:-)
September 2, 2011 at 6:10 am
You are very welcome 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply