Hi,
I want to get the value from a row that contains the minimum value of a field AND for which I group by a 3rd field. For example:
The table tbl1 has:
X Quantity Location
1 20 slot 1
1 34 slot 1
3 17 slot 1
42 12 slot 5
5 65 slot 5
If I just want the quantities and location I can do:
Select Location, Min(Quantity) from tbl1 group by Location
However if I want X (or any other field in the table) I cannot do:
Select X, Location, Min(Quantity) from tbl1 group by Location
because X is not in the group by clause.
And putting X in the group by clause causes incorrect results.
Does anyone know the correct select statement?
jerry
It might help if you provided sample output from the query you are trying to write.|||In the case of a group by, any non grouped columns in the select must be contained in an aggregate function.
Think about your example, If you group by Location which value of x should you get for location = 'slot 1' 1 or 3.
so, use MIN(X) or MAX(X) in your select and it should work
|||Can you explain what you are trying to accomplish?
;with cte
as
(
select X, Quantity, Location, row_number() over(partition by Location order by Quantity) as rn
from dbo.t1
)
select *
from cte
where rn = 1;
AMB
|||If you use sql server 2000,
Code Snippet
Create Table #qty (
[X] Varchar(100) ,
[Quantity] Varchar(100) ,
[Location] Varchar(100)
);
Insert Into #qty Values('1','20','slot1');
Insert Into #qty Values('1','34','slot1');
Insert Into #qty Values('3','17','slot1');
Insert Into #qty Values('42','12','slot5');
Insert Into #qty Values('5','65','slot5');
Select * From #qty [Main]
Join (
Select
[Location]
,Min([Quantity])[Quantity]
From
#qty
Group By
[Location]
) as [Data] On [Data].[Quantity] = [Main].[Quantity]
And [Data].[Location] = [Main].[Location]
