asked on

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....

PortletPaul

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.

Tech_Men

ASKER

hi
thanks for your message
i didn't understand i need a query that work

PortletPaul

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.

Tech_Men

ASKER

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tech_Men

ASKER

A++