如何在SQL中选择GROUP并选择最低值

I have table consisting of these fields:

我有这些字段组成的表格：

 id  |   date_from  | date_to      |   price
 --------------------------------------------
 CK1     22-12-2012   29-12-2012       800
 CK1     22-12-2012   29-12-2012       1200
 CK2     22-12-2012   29-12-2012       1400
 CK2     22-12-2012   29-12-2012       1800
 CK2     22-12-2012   29-12-2012       2200

How do I create SQL select that groups results by ID, DATE_FROM, DATE_TO and picks lowest value from price.

如何创建SQL选择按ID，DATE_FROM，DATE_TO分组结果并从价格中选择最低值。

So result would be

结果就是这样

 CK1     22-12-2012   29-12-2012       800
 CK2     22-12-2012   29-12-2012       1400

5 个解决方案

#1

select id, date_from, date_to, min(price)
from table
group by id, date_from, date_to

#2

Like this:

喜欢这个：

SELECT id, date_from, date_to, MIN(price)
FROM TableName
GROUP BY id, date_from, date_to

#3

If your dbms support cte then you can do like this;

如果您的dbms支持cte，那么您可以这样做;

Test data

测试数据

DECLARE @tbl TABLE
(
    id VARCHAR(100),
    date_from VARCHAR(100),
    date_to VARCHAR(100),
    price INT
)

INSERT INTO @tbl
VALUES
    ('CK1','22-12-2012','29-12-2012',800),
    ('CK1','22-12-2012','29-12-2012',1200),
    ('CK2','22-12-2012','29-12-2012',1400),
    ('CK2','22-12-2012','29-12-2012',1800),
    ('CK2','22-12-2012','29-12-2012',2200)

Query

询问

;WITH CTE
AS
(   
    SELECT
        RANK() OVER(PARTITION BY id ORDER BY price ASC) AS RowNbr,
        tbl.*
    FROM
        @tbl AS tbl
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.RowNbr=1

#4

SELECT id, date_from, date_to, min(price)
FROM my_table
GROUP BY id, date_from, date_to

#5

I notice that the above answers have associated data with the min(price).

我注意到上面的答案与min（价格）有关联的数据。

Whenever ive tried to use the min function with associated data, it is possible that data will be incorrect. for example:

每当香港专业教育学院尝试将min函数与关联数据一起使用时，数据可能会不正确。例如：

SELECT funder, customer,  min(sq.rate) 'Highest Average Rate', max(sq.rate) 'Highest Average Rate'
FROM tbl_x 
group by funder, customer
order by rate desc

gives

给

"funder"    "customer"  "HighestRate"         "Highest Rate"
"1"         "john"           "14.50"             "14.50"
"2"         "matt"           "13.00"             "13.00"

as you can see there the min and max values are the same.

正如你所看到的那样，最小值和最大值是相同的。

I'm sure there may be a work around this somewhere, but this is just a heads up

我相信在某个地方可能会有一项工作，但这只是一个提醒

#1

select id, date_from, date_to, min(price)
from table
group by id, date_from, date_to

#2