Highest value only

[Dafydd]

I want a query that will only take the rows with the highest value
into consideration. eg

Sequence Number Item Code
1 1223
1 52
2 445
2 5468
2 415
4 445
4 452
4 256

Therefore, in this example, taking the sequence number as the field of
intrest, only the last three row of data should be used.

Can anyone help me??

All help or advice will be very much appreciated.

Kind regards

Dafydd Hughes

 

[Phobos]

SELECT *
FROM MyTable
WHERE MyField = DMax("MyField", "MyTable");

P

 

[John Spencer (MVP)]

Dafydd,

You might try.

SELECT *
FROM MyTable
Where [SequenceNumber] =
(SELECT Max(Temp.[SequenceNumber])
FROM MyTable as Temp)

The above may be faster than the perfectly correct version that Phobos posted.

 

[Phobos]

SELECT *
FROM MyTable
Where [SequenceNumber] =
(SELECT Max(Temp.[SequenceNumber])
FROM MyTable as Temp)

The above may be faster than the perfectly correct version that Phobos

posted.

And would have the added advantage of being portable. <g>

P