How to select distinct fields in a query

Database
Enthusiast

How to select distinct fields in a query

Hello,

I have a table with the following fields:

Staff_id
Staff_name
postion
date

If one person has been promoted, it will appear multiple times in my table:

Staff_id Staff_name postion date

233 Matt Smith Assistant 2010-01-02
233 Matt Smith Manager 2010-05-02
211 Bob reynolds Assistant 2010-03-01

I would like to select all fields but just for uniques staff_id's. I just want to see matt Smith one.

What query can I use for this??

Thanks, Maria
Tags (1)
1 REPLY
Enthusiast

Re: How to select distinct fields in a query

select
z.staff_id
,z.staff_name
,z.position
,z.date
from
dbname.tablename z
inner join
(select
staff_id
,count(*) as tally
from dbname.tablename
group by 1
having count(*)>1) x
on (z.staff_id = x.staff_id);