Select Distinct...kind of

I have a table that is generated daily by one of our servers. The table has about 15 fields, one of which is called TicketNumber, which should be unique for each row. The problem is that every now and then a row is duplicated in the table. I need to be able to select all of the rows, minus duplicates...meaning any duplicated rows should show up, but only one time.

I found the following with a google search:

select *
from edata
where TicketNumber
in ( select TicketNumber
from edata
group
by TicketNumber
having count(*) = 1 )

That would show all fields from all distinct rows, however I need to also have the rows that are duplicated show up one time. I hope that makes sense.

to determine the duplicates you can do something like this

Do you know how I could combine all the non-dupes along with one occurrence of each of the duplicated rows in the same recordset?

First I would remove all of the duplicates.
But to do what you are asking just do a select distinct query.

By removing all of the duplicates, I would lose a row of important data, so I would need to either remove all but one row of each duplicated record and then just SELECT * from edata, or I would need to somehow get only one occurrence of each row - duplicated or not - in a recordset.

The problem with SELECT DISTINCT TicketNumber FROM edata is that I need to have all columns in the recordset. Also, would that return one instance of a duplicated row, or would it only return the rows that were not duplicated?

I hope this is making sense...thanks for your help btw.

if each row contains important data (should be unique), then I don't understand why you are allowing duplicates?

"would that return one instance of a duplicated row, or would it only return the rows that were not duplicated"

It would return instance of a duplicated row


You also stated "By removing all of the duplicates, I would lose a row of important data"

If there is data that is going to be lost then it must not truly be duplicate data. If all data in every field was the same and you removed one, whats lost?

S-

Sorry, I must have misunderstood. I was thinking that removing duplicates would remove the original row as well as the dupe. My mistake.

Say I have the following in a table:

#-name-ticketnumber
---------------------
1-joe-1234
1-joe-1234
2-bob-4321

I would like to get the following recordset:
1-joe-1234
2-bob-4321

i.e., I need distinct rows, but I need to return all of the fields/columns. The table that is getting all the dupes in it to begin with is generated by a proprietary bit of code that I can not change, so preventing the dupes from occuring in the first place is unfortunately not a possibility.

Again, thanks for your help and patience.

I can't believe I was making that so difficult. Thanks for all your help.