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 |
|
matrixr
Starting Member
26 Posts |
 Posted - 2006-10-09 : 16:41:56
|
| select a.OrderID, a.UserID, b.Note, b.CreatedOnfrom Orders aleft outer join Notes b on a.OrderID = b.OrderIDmy problem is that Notes can have multiple notes for a specific order, eg:orderID | note | created on1 | xyz | today1 | xyz2 | yesterdaywhen i run that select it displaysorderID | userID | note | created on1 | 2 | xyz | today <-- i only want this row shown1 | 3 | xyz2 | yesterdaywhat i want is to display the order with the latest note. how do i do that? i tried using a sub query with top 1 but that wont work. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-10-09 : 16:53:46
|
| http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxBe One with the OptimizerTG |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-09 : 16:54:14
|
You're on the right track, but use the max aggregate rather than TOP to get the one you want in the where clauseselect a.OrderID, a.UserID, b.Note, b.CreatedOnfrom Orders aleft outer join Notes b on a.OrderID = b.OrderIDWHERE b.[created on] = (SELECT max([created on] FROM Notes WHERE Notes.OrderID = a.OrderID) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-10-09 : 17:04:03
|
| snSQL,I'm assuming the LEFT OUTER JOIN is because there can be orders without notes so your solution may need some tweeking to preserve those rows.Also, are the values in [created on] really 'yesterday' and 'today' ??? I'm hoping they are datetime...that's why I posted Brett's link.Be One with the OptimizerTG |
|
|
|
matrixr
Starting Member
26 Posts |
Posted - 2006-10-09 : 17:07:46
|
| hehe, they are datetime values |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-09 : 17:16:13
|
Yeah, if you have orders without notes then you'll need to check for that tooselect a.OrderID, a.UserID, b.Note, b.CreatedOnfrom Orders aleft outer join Notes b on a.OrderID = b.OrderIDWHERE b.[created on] IS NULL OR b.[created on] = (SELECT max([created on] FROM Notes WHERE Notes.OrderID = a.OrderID) |
|
|
|
matrixr
Starting Member
26 Posts |
Posted - 2006-10-09 : 17:22:01
|
| thanks snSQL, that works perfectly |
|
|
|
|
|
|
|
|
help with select query