CODEWITHSUNDEEP
sqlsql-servert-sql
Hakan Zim
Hakan Zim

Reputation: 305

Selecting one record for a day

the below query give me address against order date for three sites. if more than one orders are completed for a site i want to select only latest record for the site 

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

result 

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

Expected result

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

so picking the latest record

UPDATE: sorry guys i should have mentioned, i want the latest value for that day. i have updated the expected result, so rather than selecting the overall latest value for site1, i want to display the latest value for site 1 for a given day, repeated each day if there is a value for that site

Upvotes: 0

Views: 51

Answers (3)

mehdi lotfi
mehdi lotfi

Reputation: 11571

Use following query:

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Assuming that client is in the orders table:

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;

Note that this will give you the address from the most recent order as well as the date.

EDIT:

Modifying the above to handle most recent per day is easy. The only change is to the definition of 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;

Upvotes: 0

Dave C
Dave C

Reputation: 7392

Try this:

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

Upvotes: 1

Related Questions