Return all BUT max records

[Alex]

I have the following find duplicates query, which returns the maximum value
in the SASNODemout.NPIEC field. What I need instead is the query to return
all BUT the maximum value. Min won't work because I could have 3 duplicate
records and I need two of the three returned; the two with the lowest NPIEC.
Can this be achieved? Thank you.

SELECT SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS,
Min(SASNoDemout.NPIEC) AS MinOfNPIEC, 1 AS NPIECHigh
FROM SASNoDemout
GROUP BY SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS, 1
HAVING (((SASNoDemout.MODEL) In (SELECT [MODEL] FROM [SASNoDemout] As Tmp
GROUP BY [MODEL],[PT],[VERS] HAVING Count(*)>1 And [PT] = [SASNoDemout].[PT]
And [VERS] = [SASNoDemout].[VERS])))
ORDER BY SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS;

 

[Michel Walsh]

Hi,


SELECT DISTINCT a.*
FROM myTable As a INNER JOIN myTable As b
ON a.model=b.model and a.pt < b.pt


For the maximum value pt, for a given model, the ON condition will be false,
since there will be no b.pt > the_max (I used a strict inequality). So,
the inner join pick all the records, except the one with the max (for each
different model).


You can replace the DISTINCT with an appropriate GROUP BY:


SELECT a.model, LAST(a.pt), LAST(a.vers)
FROM myTable As a INNER JOIN myTable As b
ON a.model=b.model and a.pt < b.pt
GROUP BY a.model



as example

 

[Randy Harris]

Hi,


SELECT DISTINCT a.*
FROM myTable As a INNER JOIN myTable As b
ON a.model=b.model and a.pt < b.pt


For the maximum value pt, for a given model, the ON condition will be false,
since there will be no b.pt > the_max (I used a strict inequality). So,
the inner join pick all the records, except the one with the max (for each
different model).

Intriguing solution. Very clever. Wouldn't that, however, exclude the
minumum value from the result, as well as the maximum value?

 

[Michel Walsh]

Hi,


Should not, unless the min = the max (just one candidate, or many
candidates but all candidates have the same value).


Indeed, if a.pt is minimum, there is at least a value greater than it (under
our assumptions), so the inner join will keep the record.



Hoping it may help,
Vanderghast, Access MVP