Queries

[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.

 

[XPS350]

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..

Like this?

SELECT Group, Min(Number) AS Minimum
FROM YourTable
GROUP BY Group


Groeten,

Peter
http://access.xps350.com

 

[KARL DEWEY]

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);

 

[KARL DEWEY]

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.

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.

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.