Group Min Record

Hello Guyz,

A small problem here, I have the below table and I need to group and display the record that has the minimum value in the table (this table is derived from a query that permutates some records to give me this result).

F1 F2 F3
QQQ C 2
QQQ B 1
QQQ A 3

expected result:
QQQ B 1

my result:
when I group by F1, First(F2) and MIN(F3):
QQQ C 1

when I group by F1, MIN(F2) and MIN(F3):
QQQ A 1

when I group by F1, F2 and MIN(F3):
QQQ C 2
QQQ B 1
QQQ A 3

Any help would be very much appreciated..

CyherusYou don't GROUP BY the aggregate for one thing. What you need is something like this in this particular example:

SELECT
t1.F1,
t1.F2,
t1.F3
FROM
table t1
INNER JOIN (
SELECT MIN(F3) AS F3 FROM table) t2 ON t1.F3 = t2.F3

This is assuming you're using SQL Server, which you probably aren't.

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]