sort ordering.... - SQL Server Forums

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)

sort ordering....

Author

Topic

tran008
Starting Member

38 Posts

Posted - 2007-08-01 : 09:39:30

I need to sort the order base on the following: col1, col2,col3.
Col1 is group together, col2 is base on the value of col3. eg:

col1 col2 col3
10685 784 0
33884 706 106
29018 290 940
33884 106 0
33884 191 706
29018 194 290
10686 217 800
10685 801 784
29018 940 0
10686 783 0
10685 218 801
10686 800 783

col1 col2 col3
33884 191 706
33884 706 106
33884 106 0
29018 194 290
29018 290 940
29018 940 0
10685 218 801
10685 801 784
10685 784 0
10686 217 800
10686 800 783
10686 783 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 09:41:15

ORDER BY Col1, Col3 DESC, Col2

E 12°55'05.25"
N 56°04'39.16"

tran008
Starting Member

38 Posts

Posted - 2007-08-01 : 09:55:17

Peso,

thank for the quick response, but result not in order: Your suggestion will product:
col1 col2 col3
29018 290 940
29018 194 290
29018 940 0

which should be:

29018 194 290
29018 290 940
29018 940 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 09:59:31

Yikes!
ORDER BY Col1, Col2, Col3

If this is not OK, then you have to explain your business rules, because you are not consistent in your provided expected output.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 10:02:26

Or this

ORDER BY	Col1,
		CASE WHEN Col3 = 0 THEN 1 ELSE 0 END,
		Col2

E 12°55'05.25"
N 56°04'39.16"

tran008
Starting Member

38 Posts

Posted - 2007-08-01 : 10:09:34

Thanks Peso,
ORDER BY Col1,
CASE WHEN Col3 = 0 THEN 1 ELSE 0 END,
Col2

did the jobs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources