[RESOLVED] Conditional Order By

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

General SQL Server Forums

New to SQL Server Programming

[RESOLVED] Conditional Order By

Author

Topic

Bzyk
Starting Member

5 Posts

Posted - 2012-07-27 : 01:28:40

Hi.
I'm looking for any idea, how to sort a table:
What I have:


ID	OMSNr	Variant	Briefe	Docs	Hefte	Postlist
610	129952	10DE	232	696	232	2739	
595	129960	20DE	325	975	325	2741	
609	130016	20INT	2	6	2	183	
597	129969	22DE	56	392	168	2743	
598	129973	23DE	16	144	64	2744	
600	129978	30DE	314	942	314	2746	
611	130022	30INT	1	3	1	184	
601	129982	31DE	10	50	20	2747	
602	129986	32DE	1	7	3	2748	
603	129990	41DE	173	519	173	2749	
604	129994	42DE	44	220	88	2750

What I want to have (as recordset):


ID	OMSNr	Variant	Briefe	Docs	Hefte	Postlist	
609	130016	20INT	2	6	2	183	
598	129973	23DE	16	144	64	2744	
611	130022	30INT	1	3	1	184	
601	129982	31DE	10	50	20	2747	
602	129986	32DE	1	7	3	2748	
604	129994	42DE	44	220	88	2750	
610	129952	10DE	232	696	232	2739	
595	129960	20DE	325	975	325	2741	
597	129969	22DE	56	392	168	2743	
600	129978	30DE	314	942	314	2746	
603	129990	41DE	173	519	173	2749

In human words: sort on Variant for "Hefte" < 100 and then sort on Variant for "Hefte" >= 100 in one, cute query.

All ideas and hints are very welcome.

Thx in advance
Greets
Tadels

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 01:41:36

[code]
SELECT *
FROM Table
ORDER BY CASE WHEN Hefte < 100 THEN 1 ELSE 2 END,Variant
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bzyk
Starting Member

5 Posts

Posted - 2012-07-27 : 02:00:08

Thank you.
It works beautifull on the server...
Now the same under pure MS-Access. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 02:00:41

welcome
Sorry I dont know how access works
please post in Access forum for access related query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bzyk
Starting Member

5 Posts

Posted - 2012-07-27 : 02:24:34

OK, I try, but Access forum is not so active as this. :)
Thx again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 02:28:36

Try IIF instead of CASE WHEN like this


SELECT *
FROM Table
ORDER BY IIF(Hefte < 100,1,2),Variant

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bzyk
Starting Member

5 Posts

Posted - 2012-07-27 : 02:37:14

Yep. I found it too. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources