CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Anthony Litterio
Anthony Litterio

Reputation: 35

Sub SELECT within a GROUP BY SQL statement

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

Answers (1)

cosmos
cosmos

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

Related Questions