help with SQL syntax for duplicates

[madrock]

Hello!



I have three columns: UNIQID, ID, and QUANTITY.

I'd like to write a query such that where there is a duplicate in ID, choose the UNIQID (or the entire row) that corresponds to the minimum QUANTITY for that duplicate.

For example, if:

UNIQID ID QUANTITY
1 123 100
2 123 50
3 444 100
4 654 75
5 654 100

I'd like the final result to be:

UNIQID ID QUANTITY
2 123 50
3 444 100
4 654 75

Any thoughts? I've tried Totals Queries and Duplicate Queries, both of which get me part of the way there, but I can't get the final result I'm looking for.

thank you.

[jzwp11]

Welcome to the forum!

You would first use an aggregate query to get the Min quantity for each ID


query name: qryMinQty
SELECT ID, Min(Quantity) as MinOfQty
FROM tablename
GROUP BY ID

Then create another query that joins the original table (I call it tablename below) to the aggregate query by both the ID and quantity

SELECT tablename.UNIQID, qryMinQty.ID, qryMinQty.MinOfQuantity
FROM tablename INNER JOIN qryMinQty ON (tablename.Quantity = qryMinQty.MinOfQuantity) AND (tablename.ID = qryMinQty.ID);

[madrock]

jzwp11 - thank you so much, it works like a charm. I also see now how I could alternatively do it without directly inputting SQL code:

1. Run a Totals Query choosing Group By for the ID and Min for QUANTITY.
2. Run a Select Query with the original table and the Totals Query with inner joins between IDs and QUANTITIES.

I had been trying to include my UNIQID in my aggregate/totals query, which is where I was messing up. I really appreciate your help.

[jzwp11]

Glad that worked for you; good luck!