Last One to Many Row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Last One to Many Row

Author

Topic

rbarlow
Starting Member

26 Posts

Posted - 2007-05-16 : 17:03:18

Hey,

I was wondering how I would go about doing the following query. Say I two tables in a one to many relationship.

CUSTOMER
cid int
name varchar

CUSTOMER_COMMUNICATIONS
ccid int
cid int
cdate datetime
notes varchar

I need to do a query that returns the following resultset

CID NAME CDATE NOTES

with a row for each customer and the cdate and notes for the last time the customer was contacted. From what I've read I believe in the SELECT statement there needs to be a

WHERE cdate = (SELECT MAX(cdate) FROM customer_communications.....

but I can't finish it off because I don't know how to specify the WHERE in the subquery the current customer.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-16 : 17:07:29

select cid, name, ccid, cdate, notes from (
select c.cid, c.name, cc.ccid, cc.cdate, cc.notes, row_number() over (partition by c.cid order by cc.cdate desc) as recid
from customer as c
inner join customer_communcations as cc on cc.cid = c.cid
) as q

Peter Larsson
Helsingborg, Sweden

rbarlow
Starting Member

26 Posts

Posted - 2007-05-16 : 17:33:26

Sorry.. I should have mentioned this is in SQL Server 2000 so that query doesn't work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-17 : 00:38:36

Try

Select columns from table T
WHERE cdate = (SELECT MAX(cdate) FROM customer_communications where cid=T.cid)

Madhivanan

Failing to plan is Planning to fail

rbarlow
Starting Member

26 Posts

Posted - 2007-05-17 : 16:50:49

Thanks a lot!! I knew it had to be something like that.

Another issue with this problem is that I still need customers to show even if there are no communications associated, with null for the date and notes column. I think the problem is in the WHERE clause, if the date is null then it doesn't retreive because it would be WHERE NULL = NULL which isn't allowed. I got past this by using CASE but I have a feel it can be done a lot better:

SELECT c.name, cc.date, cc.notes

FROM customer as c
LEFT OUTER JOIN customer_communications as cc ON (c.cid=cc.cid)

WHERE

CASE WHEN cc.cdate IS NULL THEN '01/01/1900' ELSE cc.cdate END

=

CASE WHEN (SELECT MAX(cdate) FROM customer_communications WHERE cid=c.cid) IS NULL THEN '01/01/1900' ELSE (SELECT MAX(cdate) FROM customer_communications WHERE cid=c.cid) END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources