Multiple records - querying latest result

[Janus9217]

I have a personnel database and a qualification database. Personnel can
qualify mulitple times per year at an event. I wish to build a query that
only pulls the latest qualification based on date. I have tried to use the
Max function but it still pulls multiple records if there is any other
information that is dissimilar between the two records due to the Group By
function in the query. How can I pull only the single most recent record
with all corresponding data, i.e. score, qualification rating? Should I
change the Group By to something else?

 

[Ofer]

You can sort the table by the date Desc, and then select only the top records

Select Top 10 * from TableName Order By DateField Desc

 

[peregenem]

I have a personnel database and a qualification database. Personnel can
qualify mulitple times per year at an event. I wish to build a query that
only pulls the latest qualification based on date. I have tried to use the
Max function but it still pulls multiple records if there is any other
information that is dissimilar between the two records due to the Group By
function in the query. How can I pull only the single most recent record
with all corresponding data, i.e. score, qualification rating? Should I
change the Group By to something else?

You need to reduce the info in the GROUP BY clause to only the info
required to identity the row. If you need other details, create a JOIN.
This example which uses the Northwind test database:

SELECT O1.OrderID, O1.CustomerID, O1.OrderDate,
O1.EmployeeID, O1.RequiredDate, O1.ShippedDate
FROM Orders AS O1
INNER JOIN (
SELECT CustomerID,
MAX(OrderDate) as last_order_date
FROM Orders
GROUP BY CustomerID
) AS LastOrders
ON O1.CustomerID = LastOrders.CustomerID
AND O1.OrderDate = LastOrders.last_order_date;

In the above, the 'LastOrders' derived table subquery could instead be
persisted in a Query object (i.e. a VIEW).