selecting distinct and non distinct fields?

Adamthenewbie

I have this SQL statement

SELECT DISTINCT fName, lName, sex FROM Users

But I also want to return the id field corresponding to each row in the Users table.

So something like this

SELECT id, DISTINCT fName, lName, sex FROM Users (which obviously doesnt work)

Is there a way to write an sql statement to do this?

bastien

put id at the end of the field list

Adamthenewbie

Thanks,

the problem with that though is the id field is an auto incrementing key. So all the IDs are different. When you do this:

SELECT DISTINCT fName, lName, sex, id FROM Users (Is this what you mean?)

It returns all rows because the id is distinct in all rows.

bradgrafelman

Well... what if you did something like this?

SELECT id, CONCAT(fName, ' ', lName) as fullName, sex FROM users GROUP BY fullName

bastien

try

select distinct(concat(fName,' ',lName)), sex, id from users

bradgrafelman

bastien's code still doesnt provide rows with unique first and last name combinations... just tested both of our queries out now that I have more time.

I believe my query is what you're looking for, though I'm not 100% sure if that's what you're looking for. I assumed that you wanted to know the name, sex, and row id# for all rows with unique first/last name combinations.

Lars_Berg

The request does not make sense. If you select distinct values from some columns there is no corresponding ID value. E.g.

Table T

ID C

1 A
2 A

If you select distinct values from the column c, which id should be returned?

Adamthenewbie

Lars Berg wrote:
The request does not make sense. If you select distinct values from some columns there is no corresponding ID value. E.g.

Table T

ID C

1 A
2 A

If you select distinct values from the column c, which id should be returned?

Thanks for the reply guys. I also wanted to have an order by in the SQL statement.

So for example:
Table Users
ID FirstName LastName Time
1 Adam TheNewbie 50
2 Matt Richard 55
3 Adam TheNewbie 61
4 Mike Roberts 57

I want to return the fastest times for distinct names
Example:

Return
ID FirstName LastName Time
1 Adam TheNewbie 50
2 Matt Richard 55
4 Mike Roberts 57

bradgrafelman

EDIT: Hmm... guess it's not that easy... nevermind this post...

EDIT2: Okay, think I've got it... instead of selecting 'Time', select 'MIN(Time)' like so:

SELECT `ID`, CONCAT(`FirstName`, ' ', `LastName`) as `fullName`, MIN (`Time`) as `Time` FROM `users` GROUP BY `fullName`

Adamthenewbie

bradgrafelman wrote:
EDIT: Hmm... guess it's not that easy... nevermind this post...

EDIT2: Okay, think I've got it... instead of selecting 'Time', select 'MIN(Time)' like so:
SELECT `ID`, CONCAT(`FirstName`, ' ', `LastName`) as `fullName`, MIN (`Time`) as `Time` FROM `users` GROUP BY `fullName`

Thanks I think that will do the trick!

Adam

Lars_Berg

Using that query you can not be certain that the ID value comes from the same record as the one with the minimum time.

 SELECT ID, FirstName, LastName, `Time`
   FROM users as u
  where `time` =
  (select min(`time`)
     from users
    where u.FirstName = users.FirstName
      and u.lastName = users.lastName)

Sxooter

Why not do:

select max(id), lname, fname, sex from table group by lname, fname, sex;

bradgrafelman

But... how would that sort the rows to make sure the time field with the lowest value will be returned?

Sxooter

just as a min(timefield) to it.