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 |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-06-14 : 07:40:25
|
| Hi all, I need to arrange (sort) the records based on a particular column value. Just see the below record set. In this scenario, the records for a specific month and year should be sorted based on the last SortCode of the previous month and year. That is if SortCode of the last month is 3 , next month's SortCode should start with 4 and then other values should be sorted in ascending. Let me know if there is a any code sample or posts posted earlier. Thanks & Regards,Hariarul The below recordset Names Month Year SortCodeName1 05 07 1Name2 05 07 1Name3 05 07 2Name4 05 07 3Name5 06 07 2 Name6 06 07 2Name7 06 07 4Name8 06 07 3Name9 07 07 2Name10 07 07 4 should be like , Names Month Year SortCodeName1 05 07 1Name2 05 07 1Name3 05 07 2Name4 05 07 3Name5 06 07 4Name6 06 07 2Name7 06 07 2Name8 06 07 3Name9 07 07 4Name10 07 07 2 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-14 : 07:52:25
|
| WeirdAssuming that Year and Month are character columns.Might have to use a derived table for the sort but give this a try.select t.Names, t.Month, t.Year, t.SortCodefrom tbl tleft join(select YearMonth = Year + Month, SortCode = max(SortCode) from tbl group by Year + Month) t2on t2.YearMonth = (select max(t3.Year + t3.Month) from tbl t3 where t3.Year + t3.Month < t.Year + t.Month)order by t.Year+t.Month, case when t2.SortCode <= t.SortCode then 1 else 2 end, t.SortCode==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Sort records based on a column value.