Custom 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 2008 Forums

Transact-SQL (2008)

Custom Ordering

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-10-25 : 09:27:50

Hello all,
having a Select that returns me some values like this ones:

1 Pmax
12 PMI1
27 PMI2
28 PMI3
29 PMI4
2 Pmin
.....
.....

how can I force a order so it appears in this way?

12 PMI1
27 PMI2
28 PMI3
29 PMI4
1 Pmax
2 Pmin

Thanks in advance.

Luigi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 09:34:24

I didn't quite follow the rule you want to apply, but may be this?:

ORDER BY
   LEN(CAST(col1) AS VARCHAR(32)) DESC, col1 ASC

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-10-25 : 09:41:40

It gives me the error:
LEN is not a recognized built-in function name.

Simply I need to have the values PMI(number) in order - and appear first - then the Pmax and Pmin at the bottom.

L

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 10:09:30

quote:
Originally posted by Ciupaz

It gives me the error:
LEN is not a recognized built-in function name.

Simply I need to have the values PMI(number) in order - and appear first - then the Pmax and Pmin at the bottom.

L

Are you using SQL Server?

LEN is available on T-SQL

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

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-10-25 : 10:33:46

It was a parenthesis problem, but it does not work properly.

L

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 11:22:09

quote:
Originally posted by Ciupaz

It was a parenthesis problem, but it does not work properly.

L

specify your rules for the ordering

do you want PMI items to come at first?

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

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-10-25 : 14:30:29

Yes, PMI(number) can come first. Basically I have to have all number together, and Pmax e Pmin near each other.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 14:40:22

You should be able to use the ordering like this:

ORDER BY
	CASE WHEN col2 IN ('Pmax','Pmin') THEN 1 ELSE 0 END,
	col1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 14:58:50

quote:
Originally posted by Ciupaz

Yes, PMI(number) can come first. Basically I have to have all number together, and Pmax e Pmin near each other.

ORDER BY CASE WHEN Col2 LIKE 'PMI%' THEN 1 ELSE 2 END,
Col1

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

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-10-26 : 04:28:38

Thank you Sunita, this works fine.

Luigi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-26 : 06:50:47

You are very welcome .)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources