| Author |
 Topic  |
|
|
LittlePaint
Starting Member
2 Posts |
 Posted - 09/06/2012 : 14:29:01
|
I have the following query:
SELECT X.MigrationID, MachineName, X.MessagePostDateEST, Y.CommentText FROM TABLE1 AS X
LEFT JOIN TABLE2 AS Y ON X.MigrationID=Y.MigrationID
WHERE X.MessagePostDateEST = (SELECT MAX(CAST(MessagePostDateEST AS datetime)) FROM TABLE1 WHERE MigrationID=X.MigrationID)
ORDER BY CAST(X.MessagePostDateEST AS datetime) DESC
Which produces results like this:
MigrationID MachineName MessagePostDateEST CommentText
{103310B73F8A} LM74 09/06/2012 11:46:24.903 AM NULL
{40050427F350} LV82 09/06/2012 11:30:53.967 AM Comment 1
{40050427F350} LV82 09/06/2012 11:30:53.967 AM Comment 2
{40050427F350} LV82 09/06/2012 11:30:53.967 AM Comment 3
{72DF3E618FE0} LM60 09/06/2012 10:29:07.020 AM NULL
What I need is a way to modify the query so that it will only return one record that has multiple comments associated with it. For example, I want results that look like this:
MigrationID MachineName MessagePostDateEST CommentText
{103310B73F8A} LM74 09/06/2012 11:46:24.903 AM NULL
{40050427F350} LV82 09/06/2012 11:30:53.967 AM Comment 1
{72DF3E618FE0} LM60 09/06/2012 10:29:07.020 AM NULL |
|
|
chadmat
The Chadinator
USA
1947 Posts |
Posted - 09/06/2012 : 15:00:04
|
Try this...I am randomly selecting the MAX() comment since you gave no requirement for how to decide which one to pick.
SELECT X.MigrationID, MachineName, X.MessagePostDateEST, Y.CommentText FROM TABLE1 AS X
LEFT JOIN (SELCT MigrationID, MAX(CommentText) FROM TABLE2 GROUP BY MigrationID) Y ON X.MigrationID=Y.MigrationID
WHERE X.MessagePostDateEST = (SELECT MAX(CAST(MessagePostDateEST AS datetime)) FROM TABLE1 WHERE MigrationID=X.MigrationID)
ORDER BY CAST(X.MessagePostDateEST AS datetime) DESC
-Chad |
 |
|
|
LittlePaint
Starting Member
2 Posts |
Posted - 09/13/2012 : 11:08:17
|
Chad, many thanks, that is exactly what I was looking for. I only had to add a column alias from what you posted in order to have it work:
SELECT X.MigrationID, MachineName, X.MessagePostDateEST, Y.MCT FROM TABLE1 AS X
LEFT JOIN (SELECT MigrationID, MAX(CommentText) AS MCT FROM TABLE2 GROUP BY MigrationID) AS Y ON X.MigrationID=Y.MigrationID
WHERE X.MessagePostDateEST = (SELECT MAX(CAST(MessagePostDateEST AS datetime)) FROM TABLE1 WHERE MigrationID=X.MigrationID)
ORDER BY CAST(X.MessagePostDateEST AS datetime) DESC
|
|
|
|
chadmat
The Chadinator
USA
1947 Posts |
Posted - 09/13/2012 : 12:35:12
|
You are welcome
-Chad |
|
|
| |
Topic |
|
left join Distinct alternative
