Link to home
Start Free TrialLog in
Avatar of Philippe Renaud
Philippe RenaudFlag for Canada

asked on

SQL select problem

Hello,

I have a table with 3 columns :  tableID, passNumber and LineNo

There are some rows that have the same tableId and the same Passnumber but a different LineNo.

I know it because if I do a select distinct tableId, passNumber   I receive less rows in total...

I need to know wich one are duplicated because its an error to have both tableId and passnumber with a different LineNo So I have to check wich one I will keep.

any idea how to get only those duplicated?
ASKER CERTIFIED SOLUTION
Avatar of bprojoe
bprojoe

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Philippe Renaud

ASKER

Seems to work, but how come if I do  select count(*) from myTable  it returns: 6795

if I do : select distinct tableID, passNumber from myTable   :  the total is: 6322

and your code returns me:  232 rows.     (6795 - 6322 = 473)      473 different of  232 ..


am i doing an error ?
Avatar of bprojoe
bprojoe

doing..
select count(*) From yourtable group by tableid, passnumber
should give you the same row count as doing distinct.  the group by is the key here.
the query i gave you only returns the distinct/group by of tableid and passnumber when there are more than one record of those that are the same.
Avatar of Patrick Matthews
PhilippeRenaud said:
>>am i doing an error ?

Nope.  There are 232 combinatons of tbaleID / passNumber that appear >1 time.  Of those, at least 1 appears
at least 3 times.
so what is the 473 then ?
thanks for the correction.
when running your distinct it includes ones that don't have duplicates along with the duplicates.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect, thank you.