Group ID and ID Column

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]