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.
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 isnot is the select query but the 'Fileclass' column is exist in thesame table i.e: Testif i remove Distinct from the query than it is working fine butreturning 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" |
|
|
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) descthis query is returning result in ascending order.why it is hapenning.Thanksjohn |
|
|
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 |
|
|
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" |
|
|
|
|
|