buliding a query:selecting unique records

[theEclipse]

Hello,

I am nearly positive that there is some way to have mysql select only the first occurance of a data entity on a select.

Maybe a better explanation is given by example. Think of having a table of employees, with one of the columns being their bosses email address. Now I want to do a select statement on the bosses email addresses that only returns one entry for each bosses email, even though that bosses email address will occour > 1 in the table. (more than 1 employee has the same boss...)

I could parse out the duplicates when I get to PHP but I would rather have mysql do the work for me ;-).

Thanks
Robert

Robert Carpenter
"Disobedience to conscience is voluntary; bad poetry, on the other hand, is usually not made on purpose." - C.S. Lewis (Preface to Paradise Lost)
ô¿ô

http://www.robertcarpenter.net

 

[theEclipse]

Welp....I discovered something

Turns out that you can use the modifier "DISTINCT" in your select query.

SELECT DISTINCT FROM `table` WHERE....

Robert Carpenter
"Disobedience to conscience is voluntary; bad poetry, on the other hand, is usually not made on purpose." - C.S. Lewis (Preface to Paradise Lost)
ô¿ô

http://www.robertcarpenter.net

 

[r937]

yeah, but DISTINCT doesn't help you at all in the scenario you described with the boss's email

r937.com | rudy.ca

 

[johnwm]

You might also look at TOP 1

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[r937]

1) TOP doesn't work in mysql
2) it doesn't get one employee per boss

r937.com | rudy.ca

 

[KarveR]

select count(employees) from somewhere group by bosses_email

should return a list of the number of employees and a bosses email.
example

employee - boss
ann - stevo
john - stevo
terry - stevo
fred - binny
trev - binny
brian - sally
terri - sally

result:

3 stevo
2 binny
2 sally

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[johnwm]

Thanks Rudy, I crossed DBs, it should of course be LIMIT

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[r937]

I crossed DBs

i do that all the time too ;-)





r937.com | rudy.ca