Include ID field in GROUP BY statement

I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.

The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':

ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83

And run the query:

select max(Cost),FruitName From Fruits
group by FruitName

It'll correctly return:

FruitName Cost
Apple 0.63
Pear 0.89

Now i need the ID also returned by my query so i go:

select max(Cost),FruitName,ID From Fruits
group by FruitName,ID

This doesnt return the above results with the ID appended to it, it instead returns:

ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83

As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:

ID FruitName Cost
2 Apple 0.63
4 Pear 0.89

Thanks.

I don't think you can do that with the ID field because there are multiple Ids for each item while using the group by. Can you use a Having clause? ie

select Cost,FruitName From Fruits
group by FruitName
having max(Cost)

This is a shot in the dark as i did not actually try your data. Hope it works though :)

|||

Thanks but dont think that would work as the 'HAVING' as i see it is just used to filter the results of the query so you'd use it if for example you wanted to only show max fuit more than ￡0.10

select Cost,FruitName From Fruit
group by FruitName,Cost
having max(Cost) > 0.1

Thats how i see it (could be wrong). But it still doesnt handle the problem of the ID.

|||

Hi,

This is a round about way, but it works

Select

FruitName,cost,(Select IDfrom Fruits fWhere f.Cost=frt.cost)as IDfrom

(

select FruitName,max(cost)as costFrom Fruits

group

by FruitName) frt|||

Hi TheGrox,

The below query may help you.

select id,fruitname,costfrom (select row_number()over (partitionby fruitnameorder by costdesc)as rownum,id,fruitname,costfrom Fruits) Fwhere rownum = 1

The main trick here is using the ROW_NUMBER() OVER (PARTITION BY...) clause

This is very like getting top n records from a table belonging to each category. You can follow the article athttp://www.kodyaz.com/articles/top-n-random-rows-foreach-category-or-group.aspx for more details and a sample for ROW_NUMBER() OVER (PARTITION BY...)

Eralper

http://www.kodyaz.com

|||

If you are using SQL 2000 you can try this:

Select t1.FruitName,t2.cost, Idfrom Fruits t1join (select FruitName,max(cost)as costFrom Fruitsgroup by FruitName ) t2on t1.FruitName = t2.fruitnameand t1.Cost = t2.cost

|||

Here is another one:

select t1.*from fruitsas t1where t1.ID= (select top 1 t2.IDfrom fruitsas t2where t2.FruitName = t1.FruitNameorder by t2.costDESC)

|||

Limno

I like your query better than mine. I am joining on name and cost. If there are multiple records with same name and costs, the join could return multiple records for same "Fruit" which might be incorrect.

|||I learned that solution from Umachandar Jayachandran. He is super.