Reputation: 35
I have a table where I parsed a whole bunch of VPN logs. The table does not have a primary key field and the guid field is how everything is correlated. I want to select rows grouped by the guid. and run a sub select within the group for a specific string. Not sure if this makes sense, here's the query:
SELECT [guid]
,Max([username]) AS [User]
,MIN([datetime]) AS Minimum
,Max([datetime]) AS Maximum
,DATEDIFF(mi, MIN([datetime]), Max([datetime])) AS "Duration in Minutes"
,(SELECT top 1 [data] FROM [Admin].[dbo].[VPNLogs] WHERE [data] LIKE '%The user was active for%') AS "Data"
FROM [Admin].[dbo].[temp_VPNLogs]
GROUP BY [guid]
ORDER BY "Duration in Minutes" DESC
This works well with the exception that the sub select for the data field just returns the same row for all results when I want it to select this based on the Group By. Every VPN connection entry has 4 lines with the same guid this is why I group by the guid, and I want to specifically select the data field that has the string "The user was active for" for each group. Let me know if further clarification is needed.
Thanks!
Upvotes: 0
Views: 2079
Reputation: 2303
co related sub query is what you need here:
SELECT [guid]
,Max([username]) AS [User]
,MIN([datetime]) AS Minimum
,Max([datetime]) AS Maximum
,DATEDIFF(mi, MIN([datetime]), Max([datetime])) AS "Duration in Minutes"
,(SELECT top 1 [data] FROM [Admin].[dbo].[VPNLogs] V1 WHERE [data] LIKE '%The user was active for%' AND v1.guid = v.guid) AS "Data"
FROM [Admin].[dbo].[temp_VPNLogs] V
GROUP BY [guid]
ORDER BY "Duration in Minutes" DESC
Upvotes: 1