Sorting across multiple column

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sorting across multiple column

Author

Topic

weichunglow
Starting Member

2 Posts

Posted - 2005-10-20 : 06:40:48

I have a sample table like the following way:

Col1 col2 Col3
------------------------
3 4 6
7 4 7
8 7 2
5 7 9
6 6 6

After the sorting, I should produce the result like the following:

Col1 col2 Col3
------------------------
5 7 9
8 7 2
7 4 7
6 6 6
3 4 6

Means that I have to sort the rows for the largest value among all three column whichever come first. I saw people doing this using the command like the following in mySQL, I doubt that how could we do this in Ms SQL Server.
http://www.issociate.de/board/post/224328/Advanced_Sorting_SQL_command.html

Any idea or command would help, thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-20 : 06:49:10

somthing like this ..

Select Col1,Col2,Col3 From TableName
Group By Col1,Col2,Col3
Order By
(
Case When MAx(Col1) > Max(Col2) And MAx(Col1) Max(Col3) Then Col1 End )
.....

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-20 : 07:04:30

Where do you want to show these data?
Usually this type of sorting is not done in SQL Server Queries
Try this


Declare @t table(col1 int, col2 int, col3 int)
insert into @t values(3,4,6)
insert into @t values(7,4,7)
insert into @t values(8,7,2)
insert into @t values(5,7,9)
insert into @t values(6,6,6)

Select Col1,Col2,Col3 From @t
Group By Col1,Col2,Col3
Order By 
(
Case When MAx(Col1) > Max(Col2) And MAx(Col1) >Max(Col3) Then Col1 
When MAx(Col2)> Max(Col3) Then Col2 
else col3 end)
Desc

Madhivanan

Failing to plan is Planning to fail

weichunglow
Starting Member

2 Posts

Posted - 2005-10-20 : 07:13:15

The suggested query is quite couraging where it produces the following result:
Col1 Col2 Col3
----------- ----------- -----------
5 7 9
8 7 2
7 4 7
3 4 6
6 6 6

I understand that this sorting is not common, I would display this within a datagrid of a audit report, where it relates to 7 different column.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources