Query to find lowest item in a group (1 Viewer)

[Danick]

Trying to use the query criteria to show only a single row in a batch of records with the lowest item. Something like this

Batch	Item
001 001 001	01 02 03
002 002 002	02 03 04
003 003 003	02 04 05

So I'd like the results to show this

Batch	Item
001	01
002	02
003	02

Tried using Dmin, but can't get that to work. Any ideas? Maybe using a helper column?

 

[CJ_London]

use an aggregate query

SELECT Batch, min(Item) as minItem
FROM myTable
GROUP BY Batch
ORDER By Batch

DMin should work as well - but saying 'can't get that to work' doesn't help us to help you

 

[Danick]

use an aggregate query

SELECT Batch, min(Item) as minItem
FROM myTable
GROUP BY Batch
ORDER By Batch

DMin should work as well - but saying 'can't get that to work' doesn't help us to help you

Yes this works, but I was trying to avoid an aggregate query in order to keep the table editable.

 

[CJ_London]

so use DMin - show what you tried and what the result was

 

[ebs17]

SELECT
   T.Batch,
   T.Item
FROM
   TableX AS T
WHERE
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            TableX AS X
         WHERE
            X.Batch = T.Batch
               AND
            X.Item < T.Item
      )

A design using TOP-X would also be conceivable, but the narrow table should at least have an additional unique ID.

 

[Danick]

Thanks for the suggestions. I wasn't able to use either methods to be able to modify the original table. So I decided to convert the aggregate query into another make table query. Not the cleanest solution, but am able to create a new editable table with exactly what is needed.

Thanks again for all your help.

 

[ebs17]

Query to find <> modify the original table
Some know exactly what they want.

 

[Pat Hartman]

Domain functions in this situation would only work IF you used a separate function for each batch number which is completely infeasible. So --- a domain function would not be feasible in this situation.

The fact that you need the query to be updateable implies that you are trying to save the calculated values. You've been around here a long time. Certainly long enough to know that storing calculated values is wrong and you should even know the reasons why so I won't trouble you with repeating them.