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

Author  Topic 

john20
Starting Member

30 Posts

Posted - 2009-01-15 : 09:24:57
Hi All,

I have a problem using order by and distinct.
below is the query :

'select distinct DataType from Test order by FileClass asc'

in the above qurey i want to sort the record with the field which is
not is the select query but the 'Fileclass' column is exist in the
same table i.e: Test

if i remove Distinct from the query than it is working fine but
returning duplicate records.

how can i achieve that ?

Thanks
John

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 09:25:44
select DataType from Test group by DataType order by MIN(FileClass)


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

john20
Starting Member

30 Posts

Posted - 2009-01-15 : 09:57:26

Hi peso,

Thanks for your reply.

I have tried the solution given by you it is working but the problem is it is not sorting properly as per the alphabetical order.

and if i replace min to Max then it is working fine but with max if i want to order in asc then with max i have to give desc.

like below:

select DataType from CascadeTest group by DataType order by
MAX(FileClass) desc

this query is returning result in ascending order.

why it is hapenning.

Thanks
john




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 23:50:44
shos with some sample data the problem you're facing
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-16 : 02:20:21
quote:
Originally posted by john20

I have tried the solution given by you it is working but the problem is it is not sorting properly as per the alphabetical order.
The reason this happens is that one or more DataType has more than one FileClass.
You will have to decide which FileClass to keep with each unique DataType.

It's called business rule.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

- Advertisement -