How to find the lowest values of a table 1

[jarzac]

I have a table with Journal Entries and I need to retrieve all the fields for each record grouped by EntryNumber where LineNumber is the lowest.

The table looks like this

ID EntryNumber LineNumber Amount
1 1 1 20
2 1 2 20
3 1 3 20
4 2 2 23
5 2 3 20


And when I run my query I need to get:

ID EntryNumber LineNumber Amount
1 1 1 20
4 2 2 23


So far I can get the records needed with the following query but I can't get all the fields:


SELECT DISTINCT EntryNumber, MIN(LineNumber) AS Expr1
FROM myTable
GROUP BY EntryNumber
ORDER BY Expr1

The problem is that when I add more fields to the SELECT DISTINCT then I start getting wrong results because I have to add them to the GROUP BY clause. I tried doing a general query and attaching the above as a subquery but I just can't get it to work properly.

Can anybody help me with this one?

 

[Golom]

SELECT A.ID, A.EntryNumber, A.LineNumber, A.Amount

FROM myTable As A

Where A.LineNumber = 
      (Select MIN(LineNumber) From myTable As B
       Where B.EntryNumber = A.EntryNumber)

ORDER BY A.EntryNumber, A.LineNumber

 

[PHV]

Another way:
SELECT A.ID, A.EntryNumber, A.LineNumber, A.Amount
FROM myTable AS A INNER JOIN (
SELECT EntryNumber, MIN(LineNumber) AS MinLine FROM myTable GROUP BY EntryNumber
) AS B ON A.EntryNumber = B.EntryNumber AND A.LineNumber = B.MinLine


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886