Duplicate Query Without Highest Value

[laman]

I am trying to run a Query to return duplicates which do not include the duplicate with the highest value in a specific field. For example if I have 3 dupicate records with values in Field3 of 40,30 and 20, I want the query to return only the records with the lower values (30 and 20).
I hope it is explained good enough.

Please Help!

 

[Darkshade]

Sort them by field3 and select all but first row

hope this help

 

[cmmrfrds]

This is kind of a guess but a subquery may work. I am not sure about including the max field3 in the query.

Select ID, field3 from tableA as A
where ID IN (select ID from tableA as B group by ID Having
count(*) > 1 and A.ID = B.ID) and ID Not IN
(select ID from tableA as C group by ID Having max(field3) = A.field3 and A.ID = C.ID)

 

[BoxHead]

First, I've got to acknowledge tlbroadbent and this forum because a month ago I wouldn't have had a clue, but the following should work for you.

I tried a subquery with a table named tblNanu with three fields, IdNum (an autoNumber), Field2 (People's names) and Field3 (Numeric values).

SELECT t.IdNum, t.Field2, t.Field3
FROM tblNanu AS t
WHERE t.IdNum Not In
(SELECT Top 1 tblNanu.IdNum
FROM tblNanu
WHERE tblNanu.Field2 = t.Field2
ORDER BY tblNanu.Field3 DESC);

Removing the word 'Not' returns the highest values for each name. Including the word 'not' returns everything but the highest value. If a record is not a duplicate, it is automatically the highest value and therefore excluded.

Be aware that using the Top 1 property will not notify you of any ties for the highest value. If you have two duplicate records with the same value in Field3, neither one of them will show up in this query's results because they are both considered the 'Top 1'.


HTH John

Use what you have,
Learn what you can,
Create what you need.