order by problem - 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

General SQL Server Forums

New to SQL Server Programming

order by problem

Author

Topic

john20
Starting Member

30 Posts

Posted - 2008-08-08 : 09:26:42

Hi All,

I am using order by clause to sort the record but it is not sorting the record.

below is my query :

select distinct userid, Username from User order by Username

is there any other way to do this.

Thanks
John

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-08 : 09:28:53

what do you mean when it's not sorting? give us some examples. is there whitespace at the start?

Em

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-08 : 09:28:57

?

that should should sort for you fine. (by default in ascending order)

Post a dataset and your results.

-------------
Charlie

john20
Starting Member

30 Posts

Posted - 2008-08-08 : 09:34:40

I am just executing query on sql editor.

and i m not getting the record in order.

-john

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 09:36:15

quote:
Originally posted by john20

I am just executing query on sql editor.

and i m not getting the record in order.

-john

which sql editor?
also you didnt post sample data as requested

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-08 : 09:36:18

what is your required ordering ? Post the sample data and show us the required result

KH
[spoiler]Time is always against us[/spoiler]

john20
Starting Member

30 Posts

Posted - 2008-08-08 : 10:08:13

I am using toad sql editor.

Below is sample data :

UserName
-------------
xyz
Cascade Test
Document Info
COP Test Cat
Test & cat
Casecade ABC
abc

i am trying to order this as :

abc
Casecade ABC
COP Test Cat
Document Info
Test & cat
xyz

Regards
-john

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-08-08 : 10:21:04

you have distinct on userid AND username, which means you might see username repeated for some records

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-08 : 11:29:45

[code]select distinct ~~userid,~~ Username from User order by Username[/code]

KH
[spoiler]Time is always against us[/spoiler]

john20
Starting Member

30 Posts

Posted - 2008-08-08 : 11:36:51

Thanks for your reply.

tried below query

select distinct Username from User order by Username

still same problem.

thanks
john

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-08-08 : 11:39:45

post your query output

john20
Starting Member

30 Posts

Posted - 2008-08-08 : 11:51:56

Tis is the output i m getting:

xyz
Cascade Test
Document Info
COP Test Cat
Test & cat
Casecade ABC
abc

Note: 'Casecade ABC' and 'abc' records i have added recently.

Because of that it is not ordering the records??

Regards,
john

john20
Starting Member

30 Posts

Posted - 2008-08-08 : 11:54:09

I think it is because od upper and lower case.

how to resolve that ??

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-08 : 11:58:00

I've got toad. I'll post some table ddl and query results in a few minutes.

-------------
Charlie

john20
Starting Member

30 Posts

Posted - 2008-08-08 : 11:59:03

Hi all,

I found the problem now it is because records are in upper and lower
case in the table.

can u please tell me how can i sort upper ans lower case data.

Regards,
John

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-08-08 : 11:59:21

try this and post the results

select distinct upper(Username) from User order by upper(Username)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-08 : 12:05:08

Here's my code


DECLARE @myTable TABLE (
	[userId] INT IDENTITY(1,1)
	, [userName] VARCHAR(50)
	)

INSERT INTO @myTable SELECT 'abc'
INSERT INTO @myTable SELECT 'Casecade ABC'
INSERT INTO @myTable SELECT 'xyz'
INSERT INTO @myTable SELECT 'COP Test Cat'
INSERT INTO @myTable SELECT 'Document Info'
INSERT INTO @myTable SELECT 'Test & cat'

SELECT * FROM @myTable
SELECT * FROM @myTable ORDER BY [username]
SELECT [userId], [username] FROM @myTable ORDER BY [username]

The two selects return different orders.

SELECT * (with no order) returns.


userId	userName
6	Test & cat
5	Document Info
4	COP Test Cat
3	xyz
2	Casecade ABC
1	abc

SELECT * (with order) returns


userId	userName
1	abc
2	Casecade ABC
4	COP Test Cat
5	Document Info
6	Test & cat
3	xyz

SELECT [userId], [username] FROM @myTable ORDER BY [username]
Returns..


('1', 'abc'), 
('2', 'Casecade ABC'), 
('4', 'COP Test Cat'), 
('5', 'Document Info'), 
('6', 'Test & cat'), 
('3', 'xyz')

I can't replicate your issue.

Can you please publish your table definition.

-------------
Charlie

john20
Starting Member

30 Posts

Posted - 2008-08-08 : 12:07:10

Hi Rohit,

Yes it is working fine now.... it is returning all record in order But in upper case.

Thank you so much for your all help.

take care

Thanks again

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-08 : 12:16:51

I think you have a collation issue.

Try


SELECT [username] FROM users ORDER BY [username] COLLATE Latin1_General_CI_AI

This way you won't have the overheads of the UPPER() function

-------------
Charlie

john20
Starting Member

30 Posts

Posted - 2008-08-11 : 11:13:14

Hi Charlie,

Thanks for your reply, i have tried the below query

but it is not executing giving error : 'Missing expression'

SELECT [username] FROM users ORDER BY [username] COLLATE Latin1_General_CI_AI

Regards,
John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 11:51:07

choose the correct collation. is there a collation named Latin1_General_CI_AI? i doubt. see the article below to determine collation to be used
http://msdn.microsoft.com/en-us/library/ms144250.aspx

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources