Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
pchuen
Starting Member
11 Posts |
Posted - 2013-05-15 : 02:57:19
|
Hi,I am new to sql scripting, I have the following data. Example:Top row is my field names:state agency QtyKDH WSL 1KUL WSL 1JHR RTL 1MLK RTL 1I need to sort by: No.1 : Agency (i) RTL (ii) WSLNo.2 : State (i) JHR (ii) MLK (iii) KUL (iv) KDHPlease help me. Thank you very much in advance. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-15 : 03:45:07
|
[code]SELECT *FROM tableORDER BY CASE Agency WHEN 'RTL' THEN 1 WHEN 'WSL' THEN 2 END SELECT *FROM tableORDER BY CASE State WHEN 'JHR' THEN 1 WHEN 'MLK' THEN 2 WHEN 'KUL' THEN 3 WHEN 'KDH' THEN 2 END [/code]A more flexible solution would be to create sortorder column in states/agency tables and use it in above query by joining to them on relevant fields. This will ensure you just needing to tweak value in table rather than changing the query each time.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pchuen
Starting Member
11 Posts |
Posted - 2013-05-15 : 23:35:40
|
thanks Visakh for your quick response. I tried the below as i need to sequence by column based on specific records, would the below be fine? SELECT SWFYEAR AS FisYear, SWAC01 AS Agency, SWADDS AS State, SWSRP1 AS Brand, SWSRP2 AS SubBrand, SWSRP4 AS Range, SWURRF as ACTBGT,SUM(SWC9QTY1) AS JanQty, SUM(SWC9QTY2) AS FebQtyFROM PRODDTA.F55SWF where swsrp4 not in ('QTR','HQT') GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0, SWURRFORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC, (CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END) |
|
|
pchuen
Starting Member
11 Posts |
Posted - 2013-05-15 : 23:48:26
|
quote: Originally posted by pchuen thanks Visakh for your quick response. I tried the below as i need to sequence by column based on specific records, would the below be fine? SELECT SWFYEAR AS FisYear, SWAC01 AS Agency, SWADDS AS State, SWSRP1 AS Brand, SWSRP2 AS SubBrand, SWSRP4 AS Range, SWURRF as ACTBGT,SUM(SWC9QTY1) AS JanQty, SUM(SWC9QTY2) AS FebQtyFROM PRODDTA.F55SWF where swsrp4 not in ('QTR','HQT') GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0, SWURRFORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC, (CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END)
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-16 : 00:11:17
|
quote: Originally posted by pchuen thanks Visakh for your quick response. I tried the below as i need to sequence by column based on specific records, would the below be fine? SELECT SWFYEAR AS FisYear, SWAC01 AS Agency, SWADDS AS State, SWSRP1 AS Brand, SWSRP2 AS SubBrand, SWSRP4 AS Range, SWURRF as ACTBGT,SUM(SWC9QTY1) AS JanQty, SUM(SWC9QTY2) AS FebQtyFROM PRODDTA.F55SWF where swsrp4 not in ('QTR','HQT') GROUP BY SWFYEAR, SWAC01, SWADDS, SWSRP1, SWSRP2, SWSRP3, SWSRP4, SWSRP0, SWURRFORDER BY (CASE WHEN SWADDS = 'JHR' THEN 1 WHEN SWADDS = 'MLK' THEN 2 ELSE 3 END), State, FisYear DESC, (CASE WHEN SWSRP1 = 'PAL' THEN 1 ELSE 2 END)
Looks fine to me------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pchuen
Starting Member
11 Posts |
Posted - 2013-05-16 : 23:19:45
|
thanks a lot for your kind help |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-16 : 23:56:46
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|