ORDER BY - 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 2005 Forums

Transact-SQL (2005)

ORDER BY

Author

Topic

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 04:09:16

[code] Hi

I have table data like this after using UNPIVOT

COL1 COL2 COL3
1521M31P02 D_PERIOD_1 2.000
1521M31P02 D_PERIOD_10 5.000
1521M31P02 D_PERIOD_11 3.000
1521M31P02 D_PERIOD_12 6.000
1521M31P02 D_PERIOD_2 2.000
1521M31P02 D_PERIOD_3 6.000
1521M31P02 D_PERIOD_4 5.000
1521M31P02 D_PERIOD_5 6.000
1521M31P02 D_PERIOD_6 3.000
1521M31P02 D_PERIOD_7 5.000
1521M31P02 D_PERIOD_8 6.000
1521M31P02 D_PERIOD_9 7.000

1644M88P02 D_PERIOD_1 2.000
1644M88P02 D_PERIOD_10 5.000
1644M88P02 D_PERIOD_11 3.000
1644M88P02 D_PERIOD_12 6.000
1644M88P02 D_PERIOD_2 2.000
1644M88P02 D_PERIOD_3 6.000
1644M88P02 D_PERIOD_4 5.000
1644M88P02 D_PERIOD_5 6.000
1644M88P02 D_PERIOD_6 3.000
1644M88P02 D_PERIOD_7 5.000
1644M88P02 D_PERIOD_8 6.000
1644M88P02 D_PERIOD_9 7.000

But I want the output like this…

COL1 COL2 COL3
1521M31P02 D_PERIOD_1 2.000
1521M31P02 D_PERIOD_2 2.000
1521M31P02 D_PERIOD_3 6.000
1521M31P02 D_PERIOD_4 5.000
1521M31P02 D_PERIOD_5 6.000 --GROUP ONE
1521M31P02 D_PERIOD_6 3.000
1521M31P02 D_PERIOD_7 5.000
1521M31P02 D_PERIOD_8 6.000
1521M31P02 D_PERIOD_9 7.000
1521M31P02 D_PERIOD_10 5.000
1521M31P02 D_PERIOD_11 3.000
1521M31P02 D_PERIOD_12 6.000

1644M88P02 D_PERIOD_1 2.000
1644M88P02 D_PERIOD_2 2.000
1644M88P02 D_PERIOD_3 6.000
1644M88P02 D_PERIOD_4 5.000
1644M88P02 D_PERIOD_5 6.000 --GROUP TWO
1644M88P02 D_PERIOD_6 3.000
1644M88P02 D_PERIOD_7 5.000
1644M88P02 D_PERIOD_8 6.000
1644M88P02 D_PERIOD_9 7.000
1644M88P02 D_PERIOD_10 5.000
1644M88P02 D_PERIOD_11 3.000
1644M88P02 D_PERIOD_12 6.000
[/code]

-------------------------
R..

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-07-24 : 04:37:45

may be
select * from table order by right(col2,1)

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 04:38:34

ORDER BY LEN(COL2)

Hope can help...but advise to wait pros with confirmation...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-24 : 04:39:46

select * from table order by col1,right(col2,charindex('_',reverser(col2)-1),col3

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 04:40:05

Hi Nages

No, If you put means it will return all D_PERIOD_1 first and D_PERIOD_2 second...
Correct me if am in wrong..

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 04:45:18

Hi bklr

I got this error...

Conversion failed when converting the nvarchar value '1_DOIREP_D' to data type int.

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 04:47:36

Hi waterduck

One record was changed

2.000 D_PERIOD_2 1521M31P02
6.000 D_PERIOD_3 1521M31P02
5.000 D_PERIOD_4 1521M31P02
6.000 D_PERIOD_5 1521M31P02
3.000 D_PERIOD_6 1521M31P02
5.000 D_PERIOD_7 1521M31P02
6.000 D_PERIOD_8 1521M31P02
7.000 D_PERIOD_9 1521M31P02
2.000 D_PERIOD_1 1521M31P02
5.000 D_PERIOD_10 1521M31P02
3.000 D_PERIOD_11 1521M31P02
6.000 D_PERIOD_12 1521M31P02
2.000 D_PERIOD_1 1644M88P02
2.000 D_PERIOD_2 1644M88P02
6.000 D_PERIOD_3 1644M88P02
5.000 D_PERIOD_4 1644M88P02
6.000 D_PERIOD_5 1644M88P02
3.000 D_PERIOD_6 1644M88P02
5.000 D_PERIOD_7 1644M88P02
6.000 D_PERIOD_8 1644M88P02
7.000 D_PERIOD_9 1644M88P02
5.000 D_PERIOD_10 1644M88P02
3.000 D_PERIOD_11 1644M88P02
6.000 D_PERIOD_12 1644M88P02

-------------------------
R..

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 04:49:05

SELECT *
FROM table
ORDER BY col1, len(col2)
this cannot?

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 04:53:22

[code]CREATE TABLE #Tempfun(
col1 varchar(10),
col2 varchar(10))

INSERT INTO #Tempfun (col1, col2)
SELECT '1','1' UNION ALL
SELECT '1','2' UNION ALL
SELECT '1','3' UNION ALL
SELECT '1','11' UNION ALL
SELECT '1','12' UNION ALL
SELECT '1','13' UNION ALL
SELECT '1','21' UNION ALL
SELECT '1','22' UNION ALL
SELECT '1','23' UNION ALL
SELECT '2','1' UNION ALL
SELECT '2','1' UNION ALL
SELECT '2','1'

SELECT *
FROM #Tempfun
ORDER BY col1, LEN(col2)
DROP TABLE #Tempfun[/code]
i try can oo x.X

Hope can help...but advise to wait pros with confirmation...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-24 : 04:55:33

try this
small correction inplace of brackets
select * from table order by col1,right(col2,charindex('_',reverse(col2))-1),col3

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 05:11:22

[code]SELECT *
FROM #Tempfun
ORDER BY col1, LEN(col2), col2
[/code]
sorry mistake been done

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 05:35:00

If D_PERIOD_ is fixed for col2,

Order by len(col2),col2

should work

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 05:35:57

Hi bklr

I was noticed inplace of brackets..that i have done change. But still the data is some mismatch order..friend

-------------------------
R..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 05:36:15

or

order by replace(col2,'D_PERIOD_','')*1

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 05:36:41

hi waterduck

Its working fine... thanks a lot friend..

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 05:37:41

Hi bklr

we can discuss now ....Thanks friend

-------------------------
R..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 05:53:38

What about my suggestions?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 05:54:20

wahaha...first post that accept by people

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 05:54:55

quote:
Originally posted by waterduck

wahaha...first post that accept by people

Hope can help...but advise to wait pros with confirmation...

Congratulations

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-24 : 05:57:13

madhi, can you try http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129946 ...i wish to learn that too

Hope can help...but advise to wait pros with confirmation...

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 06:04:01

Hi madhi

I didn't try your solution...let you know..anyway thanks...

-------------------------
R..

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources