I have a table that basically takes inserts all day long. Every time a call agents phone changes status (ex: hangs up or places a call) a new row is inserted into this table. So for example agent 222 can have 20 rows inserted in 1 hour if he makes 20 calls. I need to find a way to get only the most recent records and the distinct ID's of all the agents. So we can see who is on the phone at the time of the query. I tried the following:
select top 50 * from agentStateDetail order by eventDateTime desc
This pulls the records but, agent 222 will show up a few times. I need a query that pulls the most recent rows with eventType ='5' and the distinct agent ID's. So I should probably get no more than 5 or 10 rows back.
I have attached the create statement for the table and the top 50 rows of the table.
CREATE TABLE [dbo].[AgentStateDetail](
[agentID] [int] NOT NULL,
[eventDateTime] [datetime] NOT NULL,
[gmtOffset] [smallint] NOT NULL,
[eventType] [tinyint] NOT NULL,
[reasonCode] [smallint] NOT NULL DEFAULT (0),
[profileID] [int] NOT NULL,
CONSTRAINT [PK_AgentStatusDetail] PRIMARY KEY NONCLUSTERED