Solved

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

Posted on 2016-08-07
6
38 Views
Last Modified: 2016-08-07
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....
0
Comment
Question by:Tech_Men
  • 3
  • 3
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41746472
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
 

Author Comment

by:Tech_Men
ID: 41746476
hi
thanks for your message
i didn't understand i need a query that work
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41746478
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Tech_Men
ID: 41746484
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41746497
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
 

Author Closing Comment

by:Tech_Men
ID: 41746503
A++
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Execution Plan 3 30
sql query 7 35
SQL Select Query help 3 31
BULK LOGGED - log full 9 15
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now