Need help in sql statement for 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help in sql statement for Order by

Author

Topic

anilkumarkatta
Starting Member

7 Posts

Posted - 2009-04-22 : 05:42:59

Hi All

I am new to oracle.
I have data in my table:
col1
----
AA
A
B
C
D
E
FS
AA
A
B
C
D
E
FS
.
.
.
.

Now when I execute this statement
SELECT distinct col1 FROM mytable order by col1

its shows A,AA,B,C,D,E,FS where as I require in the order of AA,A,B,C,D,E,FS

while I tried with this
SELECT distinct col1 FROM mytable order by LEN(col1) DESC, col1 ASC

but i'm getting as AA,FS,A,B,C,D

Please help me out..

thanks & regards,
-Anil Katta

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 05:44:37

Oracle?
This is a Microsoft SQL Server forum.

Try www.dbforums.com

E 12°55'05.63"
N 56°04'39.26"

anilkumarkatta
Starting Member

7 Posts

Posted - 2009-04-22 : 05:48:50

I need solution for MS SQL server only

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 05:50:30

Ok, then.
Please describe the business dictating that AA should be displayed before A.
Where would, for example, AAB be displayed?

E 12°55'05.63"
N 56°04'39.26"

anilkumarkatta
Starting Member

7 Posts

Posted - 2009-04-22 : 05:57:19

as of now the data is like that only...

AAA
AAB
AA
A
B
C
..

hope this is would help you..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 06:04:51

[code]DECLARE @Sample TABLE
(
Col1 VARCHAR(20)
)

INSERT @Sample
SELECT 'AA' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'FS' UNION ALL
SELECT 'AA' UNION ALL
SELECT 'AAA' UNION ALL
SELECT 'AAB' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'FS'

SELECT Col1
FROM (
SELECT DISTINCT Col1,
Col1 + REPLICATE('Z', 10) AS Peso
FROM @Sample
) AS d
ORDER BY Peso[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 06:12:45

Or this

SELECT		Col1
FROM		@Sample
GROUP BY	Col1
ORDER BY	Col1 + REPLICATE('Z', 10)

E 12°55'05.63"
N 56°04'39.26"

anilkumarkatta
Starting Member

7 Posts

Posted - 2009-04-22 : 06:19:59

thanks Peso last post is fixed my prob

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-22 : 09:38:37

CREATE TABLE [MyTable](
REC VARCHAR(5)
)
INSERT INTO MyTable VALUES('A')
INSERT INTO MyTable VALUES('AA')
INSERT INTO MyTable VALUES('B')
INSERT INTO MyTable VALUES('C')

SELECT * FROM MYTABLE
ORDER BY CASE WHEN DATALENGTH(REC)>1 THEN 1 ELSE 2 END, REC

Iam a slow walker but i never walk back

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 09:42:01

dineshrajan_it, add the sample data 'bbbbb' into your sample above.

E 12°55'05.63"
N 56°04'39.26"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-22 : 09:42:02

Hi Peso,

u r approach is good. but is this correct format since u r hard coding 'z' and replicating.

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-22 : 09:43:19

yes i accept my query works for length <=2.

Iam a slow walker but i never walk back

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 09:45:13

You can replace 'Z' with CHAR(255) if you want to.
It depends on the data in OP's table.

E 12°55'05.63"
N 56°04'39.26"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-22 : 09:46:44

iHope this solves the problem for n no of lengths

SELECT * FROM MYTABLE
ORDER BY CASE WHEN DATALENGTH(REC)>1 THEN 1 ELSE 2 END, CASE WHEN DATALENGTH(REC) > 1 THEN REC END DESC, REC

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-22 : 09:49:17

no iam wrong

Iam a slow walker but i never walk back

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 09:50:27

No. Using my sample data posted 04/22/2009 : 06:04:51
returns this data

FS
FS
AAB
AAA
AA
AA
A
A
B
B
C
C
D
D
E
E

E 12°55'05.63"
N 56°04'39.26"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-22 : 09:52:47

this works perfectly as far as iam concerned

SELECT * FROM MYTABLE
ORDER BY SUBSTRING(REC,1,1), CASE WHEN DATALENGTH(REC)>1 THEN 1 ELSE 2 END, CASE WHEN DATALENGTH(REC) > 1 THEN REC END DESC, REC

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-22 : 09:55:05

Peso,

i have reduced my query a bit further

SELECT * FROM MYTABLE
ORDER BY SUBSTRING(REC,1,1),CASE WHEN DATALENGTH(REC) > 1 THEN REC END DESC

It works fine. ur suggestions pls

Iam a slow walker but i never walk back

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 09:59:18

Now I get