how can a get top each event in my related table
-
hi to all ho can i get top 1 from my table. i have a event log table that 3 event inserted in it i want to get top 1 each event by desc in it . my code is but this code is wrong and correct code probably similar to this code
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs
WHERE ( [Guid] = ( SELECT TOP ( 1 )
[Guid]
FROM EventLogs AS elog
WHERE ( ReferenceGuid = EventLogs.ReferenceGuid )
ORDER BY Time DESC
) )thanks for any help
-
hi to all ho can i get top 1 from my table. i have a event log table that 3 event inserted in it i want to get top 1 each event by desc in it . my code is but this code is wrong and correct code probably similar to this code
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs
WHERE ( [Guid] = ( SELECT TOP ( 1 )
[Guid]
FROM EventLogs AS elog
WHERE ( ReferenceGuid = EventLogs.ReferenceGuid )
ORDER BY Time DESC
) )thanks for any help
How about something like this:
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs AS elog
INNER JOIN (
SELECT [Guid]
FROM EventLogs elog1
INNER JOIN (
SELECT [Event], MAX([Time]) AS MTime
FROM EventLogs
GROUP BY [Event] ) AS t ON elog1.[Event] = t.[Event] AND elog1.[Time] = t.MTime
) AS elog3 ON elog.[Guid] = elog3.[Guid] -
hi to all ho can i get top 1 from my table. i have a event log table that 3 event inserted in it i want to get top 1 each event by desc in it . my code is but this code is wrong and correct code probably similar to this code
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs
WHERE ( [Guid] = ( SELECT TOP ( 1 )
[Guid]
FROM EventLogs AS elog
WHERE ( ReferenceGuid = EventLogs.ReferenceGuid )
ORDER BY Time DESC
) )thanks for any help
Or you can use row_number, can't remember the syntax but there are good examples in BOL.
Never underestimate the power of human stupidity RAH
-
How about something like this:
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs AS elog
INNER JOIN (
SELECT [Guid]
FROM EventLogs elog1
INNER JOIN (
SELECT [Event], MAX([Time]) AS MTime
FROM EventLogs
GROUP BY [Event] ) AS t ON elog1.[Event] = t.[Event] AND elog1.[Time] = t.MTime
) AS elog3 ON elog.[Guid] = elog3.[Guid]thanks for your answer this problem has been fix with some few change like this:
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs
WHERE ( [Guid] = ( SELECT TOP ( 1 )
[Guid]
FROM EventLogs AS elog
WHERE ( ReferenceGuid = EventLogs.ReferenceGuid AND Event=EventLogs.Event)
ORDER BY Time DESC
) ) -
hi to all ho can i get top 1 from my table. i have a event log table that 3 event inserted in it i want to get top 1 each event by desc in it . my code is but this code is wrong and correct code probably similar to this code
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs
WHERE ( [Guid] = ( SELECT TOP ( 1 )
[Guid]
FROM EventLogs AS elog
WHERE ( ReferenceGuid = EventLogs.ReferenceGuid )
ORDER BY Time DESC
) )thanks for any help
With SQL Server 2005, we can use Row_Number() function and Partition By clause to number rows in categories So you can partition your data in Event category and filter only the rows with row number is 1 So you will get only 1 row per category
with cte as (
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress,
rn = ROW_NUMBER() OVER (Partition By Event Order By Time Desc)
FROM EventLogs
)
select * from cte where rn = 1Please check the following URL for similar sample code http://www.kodyaz.com/articles/top-n-random-rows-foreach-category-or-group.aspx[^]
-
Or you can use row_number, can't remember the syntax but there are good examples in BOL.
Never underestimate the power of human stupidity RAH