distinct problems

[camy123]

hi huys i ahve a table which i need to get only disticnt values from for example

select disticnt staffname,creationdate,etcet
from tablename


but when this happens duplicates appear to ..
ive tried

select etc etc from
tablename
Where staff IN (Select staff from staffviewsource Group by staff Having Count(*) =1) but thuis only brings out the staff with 1 row in the table for example in the table i have admin 32 times i just want him/her(pc) once.

Please help

 

[SQLBill]

There is DISTINCT and there is DISTINCT.

Let's say I have Fname, Lname.

Fname Lname
Bill Smith
Joan Doe
Robert Cole
Dave Jones

Each one of those names is DISTINCT. Now let's say I have:

Fname Lname
Bill Smith
Joan Doe
Robert Doe
Joan Jones

If I do "SELECT DISTINCT Fname", I will get:

Fname
Bill
Joan
Robert

If I do "SELECT DISTINCT Lname", I will get:

Lname
Smith
Doe
Jones

BUT, if I do "SELECT DISTINCT Fname, Lname", then I will get:

Fname Lname
Bill Smith
Joan Doe
Robert Doe
Joan Jones

Why, because EACH row is DISTINCT. Bill Smith is not the same as Joan Doe or Robert Doe, or Joan Jones; Joan Doe is not the same as Bill Smith, or Robert Doe or Joan Jones, etc......

So, if you still think DISTINCT is returning duplicates post what you are getting and what you THINK you should be getting.

-SQLBill

 

[camy123]

so any ideas on how i just get the rows with with instance of teh staff column

there will be duplicates in the staff column

 

[SQLBill]

Can you give us some sample data and what you want the results to be?

-SQLBill

Posting advice: FAQ481-4875

 

[camy123]

Thank you for taking the time to help me..

ok i have a table which is(ill show you the create script)

creat table mytable(

userid
creationdate
)

now this table contains.. data from 60 databases tables so there will be the same userid in there

for example

admin
admin
admin

what i need is to bring out the rows which are no dupluicated so
with a table which has the follwoing data

admin
admin
admin
admin
admin
johnT
JohnT
Mike

It should just bring out
admin
johnT
mike

but becauase the creation date is diff distinct does not work

 

[SQLBill]

Since all you need is the userid (that's all you show you need):

SELECT DISTINCT userid
FROM tablename

-SQLBill

Posting advice: FAQ481-4875

 

[camy123]

sorry my explaination was rubbish i need al clumns in the table

 

[SQLBill]

Do you have a value that identifies a specific row? For example:

MyID UserID CreateDate
1 admin 1/2/2004
2 admin 1/3/2004
3 mike 2/2/2004

From what I understand you want all the values from the rows, but only one row per distinct UserID. How do you determine which of several rows you want? Your example shows five admin rows. How do we know which to return?

-SQLBill

 

[roccorocks]

SQLBill has a valid point. Multiple entries for an ID the way you have now will bring back multiple Distinct ROWS. Can you post all the collumns you plan on returning (instead of etc etc). Maybe a little description of the collumns and I am sure someone can get you the answer your looking for.

Tim R

 

[sgrahman]

do you know what createDate you need in the results set? or does it really matter?

you can use the following sql statement for most recent "createdate" and this will produce a results set with only one row per user id:

Select mytable.*
from mytable
where mytable.createdate = (select max(t2.createdate) from mytable t2 where mytable.userid=t2.userid)


--sgrahman