using DISTINCT?

Question

Post reply

using DISTINCT?

mick burden

SSCarpal Tunnel

Points: 4432
More actions
September 29, 2007 at 3:12 am

#177717

hi everyone, I've a problem with using DISTINCT, I'd like to get distinct values from field one but ordering on say field 2, but unless I put field 2 in the select statement it won't accept it, but doing that I return all the records as rhe 2 combined are distinct, can anyone help?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

As Joe Celko would put it :

SELECT Squid FROM FOO ORDER BY Britney Spears

I don't see how the heck the server could handle this one.

Can you tell us more about the problem you want to solve. Right now I have no clue about what you want, except maybe that you want to drop duplicate rows from a table. If it's the case, just search these forums, you'll get plenty of exemples.

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 2

as an example as to what I'm after

First Last id

======================

John Brown 1

Joe Smith 2

Jane Brown 3

Brian Smith 4

Carol Jones 5

What I'd like is to return

Brown

Jones

Smith

in order of the ID

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Select LastName, MIN(id) As mID FROM dbo.FOO GROUP BY LastName ORDER BY mID

I still don't get why you want to do this thought... have no clue what you want to do now however.

aleatha SSC Veteran Points: 252 More actions · Answer 4

try group by

SELECT DISTINCT field1

FROM Table1

WHERE (conditions)

GROUP BY field2 - hope this helps

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Group by DOESN'T guarantee the order of the results. While it often does, this is not the way to do it. The only construct that guarantees the order of the results is ORDER BY.