Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table of transactions with 880,095 records that I am querying.

There are duplicate Transaction ID's in the table that I would like hidden/removed when querying the table.

Eg 1.

  • Transaction ID 001 is has two records
  • The data in both rows is identical except for Interest Rate.
  • Row 1 has interest rate of 5%
  • Row 2 has interest rate has no rate and value in the cell is 0

I would like all duplicate Transaction ID's with no interest rate to be removed. This is simple enough, the complication is the following.

Eg 2.

  • Transaction ID 002 has 4 records
  • The data in all 4 rows is identical
  • The interest rate in all rows is 0, ie there is no interest rate.
  • I would like the query to return any 1 of these rows, and remove the other 3.

In summary, I need help designing an MS Access query where duplicate transaction Id's are removed where:

  • Transaction ID has multiple rows, remove the row which has a 0 Interest Rate value
  • Transaction ID has multiple rows, with all Interest Rates values as 0. Remove all duplicates leaving just one row.

Please let me know if more explanation is needed.

share|improve this question
    
If you don't want to simply select the non-duplicates, but actually remove (DELETE) them, please clarify. –  ypercube Dec 31 '12 at 18:59
    
I do have a view of deleting the duplicates in the future. How would I go about doing this? –  user16631 Jan 2 '13 at 11:35
add comment

1 Answer

up vote 3 down vote accepted

If all the "duplicate" rows differ only at the InterestRate and you never have more than one row with rate over 0, and (one more assumption) there is no negative interest rate, then you can simply:

SELECT 
    TransactionID, MAX(InterestRate) AS InterestRate,
    col1, col2, ..., colN               -- all the other columns

FROM
    tableX AS t
GROUP BY
    TransactionID,
    col1, col2, ..., colN ;             -- all the other columns
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.