为一天选择一个记录

Question

下面的查询根据三个站点的订购日期给我地址。如果一个站点已完成多个订单，则仅选择该网站的最新记录。

SELECT DISTINCT ORDERS.Address, ORDERS.ORDERDATE  
FROM ORDERS
Left JOIN PHONEDATA AS P
   ON ORDERS.RECID = P.OrderID
where client IN ('site1','site2','site3')

结果

Address orderdate
------- -----------------------
Site1   2014-02-13 14:58:22.427
site1   2014-02-13 14:48:57.413
site1   2014-02-13 15:03:32.403
Site2   2014-02-13 13:48:22.427
site2   2014-02-13 13:30:57.413
site2   2014-02-13 13:03:32.403
Site3   2014-02-13 14:12:22.427
site3   2014-02-13 11:10:57.413
site3   2014-02-13 13:03:32.403
Site1   2014-02-14 14:58:22.427
site1   2014-02-14 14:48:57.413
site1   2014-02-14 15:03:32.403
Site2   2014-02-14 13:48:22.427
site2   2014-02-14 13:30:57.413
site2   2014-02-14 13:03:32.403
Site3   2014-02-14 14:12:22.427
site3   2014-02-14 11:10:57.413
site3   2014-02-14 13:03:32.403

预期结果

site1   2014-02-13 15:03:32.403
Site2   2014-02-13 13:48:22.427
Site3   2014-02-13 14:12:22.427
site1   2014-02-14 15:03:32.403
Site2   2014-02-14 13:48:22.427
Site3   2014-02-14 14:12:22.427

所以选择最新的记录

更新:对不起，我应该提一下，我想知道那天的最新价值。我已经更新了预期的结果，所以我不选择site1的总体最新值，而是希望显示给定一天站点1的最新值，如果该站点有值，则每天重复显示该值。

Answer 1

假设client在orders表中：

SELECT o.Address, o.ORDERDATE  
FROM (select o.*, row_number() over (partition by client order by orderdate desc) as seqnum
      from ORDERS o
     ) o Left JOIN
     PHONEDATA  P
     ON o.RECID = P.OrderID
where o.client IN ('site1', 'site2', 'site3') and
      o.seqnum = 1;

请注意，这将给您的地址，从最近的订单，以及日期。

编辑：

修改上面的内容来处理最近的每天都是很容易的。唯一的改变是对seqnum的定义

SELECT o.Address, o.ORDERDATE  
FROM (select o.*, row_number() over (partition by client, cast(orderdate as date)
                                     order by orderdate desc
                                    ) as seqnum
      from ORDERS o
     ) o Left JOIN
     PHONEDATA  P
     ON o.RECID = P.OrderID
where o.client IN ('site1', 'site2', 'site3') and
      o.seqnum = 1;

Answer 2

试试这个：

SELECT ORDERS.Address, MAX(ORDERS.ORDERDATE) AS ORDERDATE
FROM ORDERS O
LEFT JOIN PHONEDATA AS P
   ON O.RECID = P.OrderID
WHERE client IN ('site1','site2','site3')
GROUP BY ORDERS.Address

Answer 3

使用以下查询：

SELECT ORDERS.Address, MAX(ORDERS.ORDERDATE)
FROM ORDERS
Left JOIN PHONEDATA AS P
   ON ORDERS.RECID = P.OrderID
where client IN ('site1','site2','site3')
GROUP BY ORDERS.Address, CAST(ORDERS.OrderDate AS DATE)