S
stokefolk
I'm trying to produce a list box with a most recently used list at the
top. I've got a working query to extract the 5 most recent, and another
to extract the complete list, but when I try the two together, the
ORDER BY from the original list applies to the entire query, not just
the results following the UNION clause as intended. Below is my query -
any ideas?
SELECT TOP 5 TSuppliers.[Supplier ID], TSuppliers.[Supplier Name],
TSuppliers.[Address 1]
FROM TSuppliers
INNER JOIN (TOrders As A
INNER JOIN TOrders As B
ON A.[Supplier ID]=B.[Supplier ID])
ON TSuppliers.[Supplier ID]=B.[Supplier ID]
GROUP BY TSuppliers.[Supplier ID], TSuppliers.[Supplier Name],
TSuppliers.[Address 1], A.[Date]
HAVING A.[Date] = MAX(B.[Date])
ORDER BY A.[Date] DESC
UNION ALL
SELECT TSuppliers.[Supplier ID], TSuppliers.[Supplier Name],
TSuppliers.[Address 1]
FROM TSuppliers
ORDER BY TSuppliers.[Supplier Name];
top. I've got a working query to extract the 5 most recent, and another
to extract the complete list, but when I try the two together, the
ORDER BY from the original list applies to the entire query, not just
the results following the UNION clause as intended. Below is my query -
any ideas?
SELECT TOP 5 TSuppliers.[Supplier ID], TSuppliers.[Supplier Name],
TSuppliers.[Address 1]
FROM TSuppliers
INNER JOIN (TOrders As A
INNER JOIN TOrders As B
ON A.[Supplier ID]=B.[Supplier ID])
ON TSuppliers.[Supplier ID]=B.[Supplier ID]
GROUP BY TSuppliers.[Supplier ID], TSuppliers.[Supplier Name],
TSuppliers.[Address 1], A.[Date]
HAVING A.[Date] = MAX(B.[Date])
ORDER BY A.[Date] DESC
UNION ALL
SELECT TSuppliers.[Supplier ID], TSuppliers.[Supplier Name],
TSuppliers.[Address 1]
FROM TSuppliers
ORDER BY TSuppliers.[Supplier Name];