Query help please!

Question

Post reply

Query help please!

bcoon

Grasshopper

Points: 22
More actions
July 11, 2008 at 10:09 am

#189395

Hello everyone. Can anyone provide some assistance. We have 2 tables, one is the customer table, and the other is the payment table. We need to join those tables and return only the newest payment date from the payment table.
Customer table:
CustID
1
2
3
4
Payment table:
CustID PaymentDate
1 1/1/2008
1 2/1/2008
1 3/1/2008
2 1/1/2008
2 2/1/2008
3 1/1/2008
3 2/1/2008
3 3/1/2008
4 1/1/2008
4 2/1/2008
4 3/1/2008
Results I need:
CustID PaymentDate
1 3/1/2008
2 2/1/2008
3 3/1/2008
4 3/1/2008
What would the query look like?
Thanks for your help!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 1

This kind of sounds like a test question, so I'll ask you to show some work.

As a hint, a subquery and a max() aggregate can help.

bcoon Grasshopper Points: 22 More actions · Answer 2

Not sure I understand what you mean by a "test question" Steve.

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 3

He means it sounds like homework.

I'd suggest using a subqery with a TOP and ORDER BY.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 4

You mean a correlated subquery, Grant?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 5

Depending on what kind of data you're trying to get back, yeah. Generally I've seen either a derived table or a correlated sub-query within the ON clause work extremely well (assuming the indexing is there to support it).

I wrote a whole comparison between TOP, MAX & ROW_NUMBER for this type of versioning and submitted it to Chuck Heinzelman over at SQL Server Standard. If he ever publishes it...

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

bkrishnasamy SSC Enthusiast Points: 114 More actions · Answer 6

Hi ,

you doesn't need to join the main table (customer table).your expected result can get from second table(payment table). Is join the tables is mandatory?

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 7

Well, that depends. If you have the keys needed to find the right row or rows in the child table and all the columns you want are in the child table... then that's the only one you need to access. If, on the other hand, all the columns you want to access are in the child table, but the only keys you have are in the parent table, the you'll still need to do the join in order to filter the data appropriately. There are a gazillion other combinations. It just depends on the requirements and the structure.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

AmitChoudhary Old Hand Points: 345 More actions · Answer 8

One answer could be

SELECT CustID,

MAX(PaymentDate)

FROM #payment

GROUP BY CustID

ORDER BY CustID

Thanks,

Amit Choudhary