<< Useful Visual Studio 2005 Feature Discovered!
|
Home
|
Strive for 4-5 failures per month! >>
posted @ Tuesday, November 14, 2006 8:39 PM
Now, I don't profess to be a SQL guru, but I'm not half-bad either. Tonight I was stuck at work going batty over how to do a specific SQL Query. Here's an example of what I was trying to do:
Venue table:
- VenueID int [Primary Key]
- VenueName varchar(50)
Event table:
- EventID int [Primary Key]
- VenueID int [Foreign Key]
- EventName varchar(50)
- StartDate datetime
I had to do a single flat file export of all my venues, one row per venue, with the following information:
- VenueName
- NextEventName
- NextEventStartDate
So, I needed to be able to join the Event table to the Venue table, and return only the FIRST event with a NextEventStartDate > GETDATE()
I'm sure there are people out there going "THAT'S EASY!!!" Well, I couldn't figure it out for the life of me, and it was getting late, and I was losing it. I could easily GROUP BY VenueID and get MIN(StartDate), but that wouldn't get me the EventName. I could do a klugey UDF but that made me feel icky.
I felt like there had to be a way to order events by Venue and assign a sequence number. ROW_NUMBER() to the rescue!!! Not only does ROW_NUMBER() assign numbers based on an ORDER BY clause you give it, it also can partition your result set with a clause similar to a GROUP BY. Here was the SQL that saved me:
SELECT *,
ROW_NUMBER() OVER(PARTITION BY EventID ORDER BY StartDate) as SequenceNo
FROM Event
WHERE StartDate > GETDATE()
This created a result set something like this:
| EventID |
VenueID |
EventName |
StartDate |
SequenceNo |
| 20 |
1 |
Big Party |
12/1/2006 |
1 |
| 21 |
1 |
Smaller Party |
12/5/2006 |
2 |
| 28 |
2 |
Reception |
11/1/2006 |
1 |
| 29 |
2 |
Bowling Night! |
11/15/2006 |
2 |
| 35 |
2 |
Poker Night |
1/15/2007 |
3 |
Once I had that view, I could JOIN on
UpcomingEventsView.VenueID = Venue.VenueID
AND UpcomingEventsView.SequenceNo = 1
Order was restored to my world, and I can go home and read my son a good night story!