SQL Query Distnct - 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)

SQL Query Distnct

Author

Topic

klover
Starting Member

2 Posts

Posted - 2008-12-02 : 04:28:21

Hi all,

I was just wondering how come in SQL2005 Query


SELECT   Distinct BS
FROM     GeneralNF
ORDER BY           BS

It did not come out to what I expected like in Access where the values came out to be like
100
1000
125
1250
1500
160
1800
2000
250
315
3150

How do I make it in to proper order smalles to the biggest in value?

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 04:37:23

[code]SELECT BS
FROM GeneralNF
GROUP BY BS
ORDER BY LEN(BS),
BS[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 04:48:51

quote:
Originally posted by klover

Hi all,

I was just wondering how come in SQL2005 Query

SELECT Distinct BS
FROM GeneralNF
ORDER BY BS

It did not come out to what I expected like in Access where the values came out to be like
100
1000
125
1250
1500
160
1800
2000
250
315
3150

How do I make it in to proper order smalles to the biggest in value?

Thank you.

it seems like datatype of field was character data in access. If fieid is int then it will sorted only in correct order(100,125,160,...). if you want same sort order in t-sql, cast it as varchar in order by

klover
Starting Member

2 Posts

Posted - 2008-12-02 : 04:49:44

quote:
Originally posted by Peso

SELECT BS
FROM GeneralNF
GROUP BY BS
ORDER BY LEN(BS),
BS

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

Thanks for the answer...works great..
Time for me to study this.

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-02 : 16:22:18

why the LEN(BS) in the ORDER BY clause? Shouldn't ORDER BY BS work just as well?

___________________________
Geek At Large

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-02 : 16:46:26

ok, having read this again, it seems clear that these numbers are not numbers but actually strings(VARCHAR). If the results are supposed to be all numeric, consider:

SELECT BS
FROM GeneralNF
ORDER BY CAST(BS AS INT);

___________________________
Geek At Large

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources