Queries

  • Thread starter Thread starter Steve in Louisiana
  • Start date Start date
S

Steve in Louisiana

How do I write a query that selects the two lowest values for each group?
The min function gives me the single lowest value for the entire database.
 
Two ways.
A query that pulls minimum for group joined in your other query.
qryGroupMin --
SELECT GroupItem, Min([YourData]) AS MinOfYourData
FROM YourTable;

SELECT YourTable.*
FROM YourTable INNER JOIN qryGroupMin ON YourTable.GroupItem =
qryGroupMin.GroupItem AND YourTable.YourData = qryGroupMin.MinOfYourData

Or a subquery --
SELECT YourTable.*
FROM YourTable
WHERE YourTable.YourData = (SELECT Min([YourData]) FROM YourTable AS [XX]
WHERE YourTable.GroupItem = [XX].GroupItem);
 
I made a mistake in that results only produce one per group.
Change to like this --
subquery --
SELECT YourTable.*
FROM YourTable
WHERE YourTable.YourData = (SELECT TOP 2 [XX].[YourData] FROM YourTable AS
[XX] WHERE YourTable.GroupItem = [XX].GroupItem ORDER BY [XX].[YourData] DESC)
ORDER BY [YourTable].[YourData];




--
Build a little, test a little.


KARL DEWEY said:
Two ways.
A query that pulls minimum for group joined in your other query.
qryGroupMin --
SELECT GroupItem, Min([YourData]) AS MinOfYourData
FROM YourTable;

SELECT YourTable.*
FROM YourTable INNER JOIN qryGroupMin ON YourTable.GroupItem =
qryGroupMin.GroupItem AND YourTable.YourData = qryGroupMin.MinOfYourData

Or a subquery --
SELECT YourTable.*
FROM YourTable
WHERE YourTable.YourData = (SELECT Min([YourData]) FROM YourTable AS [XX]
WHERE YourTable.GroupItem = [XX].GroupItem);

--
Build a little, test a little.


Steve in Louisiana said:
How do I write a query that selects the two lowest values for each group?
The min function gives me the single lowest value for the entire database.
Click to expand...
Click to expand...
 
Back
Top