# SQL选择组查询[英] SQL select group query

### 问题描述

table1

```+--------+----------+---------+
| amount | make     | product |
+--------+----------+---------+
|    100 | Nokia    | Mobiles |
|    300 | Samesung | Mobiles |
|    700 | Micromax | Mobiles |
|   1000 | Karbonn  | Mobiles |
|    500 | Lava     | Mobiles |
|    100 | Floyer   | Gift    |
|    500 | Arichies | Gift    |
|    300 | Feeling  | Gift    |
+--------+----------+---------+
```

```+--------+----------+---------+
| amount | make     | product |
+--------+----------+---------+
|   1000 | Karbonn  | Mobiles |
|    700 | Micromax | Mobiles |
|    500 | Arichies | Gift    |
|    300 | Feeling  | Gift    |
+--------+----------+---------+
```

## 推荐答案

```SELECT a.*
FROM Table1 a
INNER JOIN Table1 b ON a.product = b.product AND a.amount <= b.amount
GROUP BY a.amount, a.product
HAVING COUNT(*) <= 2
```

.

```select product, make, amount, rnk
from (
select l.product, l.make, l.amount, count(*) as rnk
from table1 as l
left join table1 as r
on (r.product = l.product and l.amount <= r.amount)
group by l.product, l.make
) a
where rnk <= 2
```

sql小提琴基于Zane bien测试数据.

## 其他推荐答案

```SELECT a.*
FROM Table1 a
INNER JOIN Table1 b ON a.product = b.product AND a.amount <= b.amount
GROUP BY a.amount, a.product
HAVING COUNT(*) <= 2
ORDER BY a.amount desc
```

### 问题描述

Below is my Table

Table1

```+--------+----------+---------+
| amount | make     | product |
+--------+----------+---------+
|    100 | Nokia    | Mobiles |
|    300 | Samesung | Mobiles |
|    700 | Micromax | Mobiles |
|   1000 | Karbonn  | Mobiles |
|    500 | Lava     | Mobiles |
|    100 | Floyer   | Gift    |
|    500 | Arichies | Gift    |
|    300 | Feeling  | Gift    |
+--------+----------+---------+
```

Now I want to display the two highest amount for each product...

So I want to build single SQL query which gives me result as below..

```+--------+----------+---------+
| amount | make     | product |
+--------+----------+---------+
|   1000 | Karbonn  | Mobiles |
|    700 | Micromax | Mobiles |
|    500 | Arichies | Gift    |
|    300 | Feeling  | Gift    |
+--------+----------+---------+
```

Kindly help me to build such query..

## 推荐答案

You can use this solution to retrieve the "group-wise maximum" based on the amount:

```SELECT a.*
FROM Table1 a
INNER JOIN Table1 b ON a.product = b.product AND a.amount <= b.amount
GROUP BY a.amount, a.product
HAVING COUNT(*) <= 2
```

Simply change the 2 to however many of the top rows you want to retrieve per product.

If you wanted to retrieve the lowest two rows per product, you can simply change the <= sign in the INNER JOIN to a >=.

You can fiddle around with this solution here: SQL-Fiddle Demo

## 其他推荐答案

```select product, make, amount, rnk
from (
select l.product, l.make, l.amount, count(*) as rnk
from table1 as l
left join table1 as r
on (r.product = l.product and l.amount <= r.amount)
group by l.product, l.make
) a
where rnk <= 2
```

see the ideea and other examples here: http://www.xaprb.com/blog/2005/09/27/simulating-the-sql-row_number-function/

and sql fiddle based on zane bien test data.

## 其他推荐答案

```SELECT a.*
FROM Table1 a
INNER JOIN Table1 b ON a.product = b.product AND a.amount <= b.amount
GROUP BY a.amount, a.product
HAVING COUNT(*) <= 2
ORDER BY a.amount desc
```