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....
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....
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
Tech_Men
ASKER
hi
thanks for your message
i didn't understand i need a query that work
thanks for your message
i didn't understand i need a query that work
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.
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
A++
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.