select row from table and most update row from the second table for each customer

hi there ,
i have customer table like this :
Customers
CID NAME DATE ADDRESS CITY
1     dan     1.1.16  xxxx        yyyy
2     dana    5.5.16 ttttt        ccccc
Orders
OID  CID  OrderDate  OrderSum
1       1      10.1.16          100
2       2      5.5.16             40
3      1       10.2.16          100
4      2       10.06.16       250

now for each customer there is some orders i want to pull out the customer and the most new order to the same row
for dan i get this :
CID NAME DATE ADDRESS CITY OID  CID  OrderDate  OrderSum
1     dan      01.01.16 xxxx   yyyy  3      1     10.2.16         100
2    dana     05.05.16 tttt     cccc   4     2      10.6.16         250

how can i get it ?
thanks....
Tech_MenAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
Use a LEFT JOIN instead of the INNER JOIN and change d.rn=1 to a join condition
SELECT
  *
FROM customers c
LEFT JOIN (
          SELECT
            *
            , ROW_NUMBER() OVER (PARTITION BY cid ORDER BY orderdate DESC) AS rn
          FROM orders
          ) d ON c.cid = d.cid AND d.rn = 1

Open in new window

0
 
PortletPaulfreelancerCommented:
Use row_number() over() in a subquery of the orders table partitioned by [CID] ordered by [OrderDate]

select *
From customers c
Inner join (
  Select *
, row_number() over(partition by cid order by orderdate desc) as rn
  From orders
 ) d on c.cid=d.cid
Where d.rn=1

Note if you ordered the date ASCending you get the first order instead. So to get the latest date use DESCending.
0
 
Tech_MenAuthor Commented:
hi
thanks for your message
i didn't understand i need a query that work
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
PortletPaulfreelancerCommented:
Please refresh page and relook. I am using a phone on a train and it is v.hard to write accurate code. It the  best i can do right now.
0
 
Tech_MenAuthor Commented:
ok
now its work but i need more 1 thing
i still need to see the customer that they not have any orders

Customers
CID NAME DATE ADDRESS CITY
1     dan     1.1.16  xxxx        yyyy
2     dana    5.5.16 ttttt        ccccc
3    ran        1.1.16  ffff f      pffff
Orders
OID  CID  OrderDate  OrderSum
1       1      10.1.16          100
2       2      5.5.16             40
3      1       10.2.16          100
4      2       10.06.16       250
CID NAME DATE ADDRESS CITY OID  CID  OrderDate  OrderSum
1     dan      01.01.16 xxxx   yyyy  3      1     10.2.16         100
2    dana     05.05.16 tttt     cccc   4     2      10.6.16         250
3   simon    01.01.16 ffff  ff  off
0
 
Tech_MenAuthor Commented:
A++
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.