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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-07-12 : 07:12:02
|
| John South writes "HiI always enjoy having a SQL question that stumps me: as I get to learn a bit more! This is a simplified description that gets to the heart of the question:SQL server 2000 standard instalation.2 tables:accounts: 2 columns: accountID and accountNameevents: 4 columns: eventID, accountID, description, eventDateEach account can be linked to any number of events on any date.I have a select that returns a list of events between any 2 dates, no problem.Question: how do I limit the select so that for any select between 2 dates I return at most 1 event record for each account record?This is on an asp.net website with growing volumes so SQL efficiency is a consideration.CheersJohn SouthPangbourne UK" |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-12 : 07:58:25
|
| [code]SELECT A.accountID, A.accountName, [Count] = COUNT(*)FROM dbo.accounts A JOIN events E ON E.accountID = A.accountIDWHERE E.eventDate BETWEEN '01-Jan-2005' AND '01-Feb-2005'GROUP BY A.accountID, A.accountName[/code]If you actually want to see columns from an event you'll need a sub select that finds the MIN eventid (or the MAX or somesuch) within the date range, and JOIN to that insteadKristen |
 |
|
|
|
|
|
select single linked records per master record