使用group by和返回行的标识 [英] Use group by in and return identity of the row

问题描述

  id类型食品价格
 --------- ----------------- 
 1 veg carrot 10 
 2 veg萝卜11 
 3水果荆棘6 
 4水果锉刀4 
 5水果当前9 
 ... 
  

我可以返回最高价格对于每种食物类型来说，最昂贵的食物就是这样...

 从tableName组中按类型选择max（price） 
  

但是我想返回每行包含最昂贵食物的ID号食物类型。每种食物类型只返回一行。即返回这.... ....

  id 
 ---- 
 2 
 5 
 ... 
  

这是我真正问题的简化版本。

解决方案

  SELECT id 
 FROM（SELECT id，type 
 FROM table 
 ORDER BY price DESC）AS h 
 GROUP BY类型
  

For this table...

id  type   food     price 
--------------------------
1   veg    carrot   10    
2   veg    turnip   11
3   fruit  bramble  6
4   fruit  rasp     4
5   fruit  current  9
...

I can return the max price of the most expensive food for each food type like this...

select max(price) from tableName group by type;

But I'd like to return the id number of each row that contains the most expensive food for each food type. And return one and only one row per food type. Ie return this....

id  
----
2
5 
...

This is a simplified version of my real problem.

解决方案

SELECT id
FROM ( SELECT id, type
       FROM table
       ORDER BY price DESC) AS h
GROUP BY type

这篇关于使用group by和返回行的标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！