Philippe Renaud
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
>>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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, thank you.
ASKER
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 ?