# SQL Query - Pulling Last Value for Certain Records **URL:** \ **Category:** Ignition\ **Tags:** scripting\ **Created:** [June 6, 2023, 12:36pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629 "2023-06-06T12:36:41Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Robert\_Hrynciw](https://avatars.discourse-cdn.com/v4/letter/r/3bc359/32.png) [@Robert\_Hrynciw](https://forum.inductiveautomation.com/u/Robert_Hrynciw)\ **Post date:** [June 6, 2023, 12:36pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/1 "2023-06-06T12:36:41Z")
Hello all, I'm struggling with pulling out the last value for certain records. What I'm trying to do is pull out the last "CurrentHours" value for each "EID". I'm running something like this but I'm not sure if the "Last\_Value" function is the correct thing for me. The hard part is that an employee may not sign up at the most current date, so I can't just select TOP(X). I need to fetch that most recent date of signup (DateTime) and the CurrentHours associated with it. ````auto SELECT DISTINCT Fname, Lname, EID, TradeSkill, Shift, LAST_VALUE(CurrentHours) OVER (PARTITION BY EID ORDER BY DateTime desc) as CurrentHours, DateTime FROM [Maintenance].[dbo].[MOS_Signup] ORDER BY MOS_Signup.DateTime desc ``` ```` ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [June 6, 2023, 12:38pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/2 "2023-06-06T12:38:59Z")
That looks like MS SQL, so I imagine that you are looking for `SELECT TOP 1` ---
**Author:** ![Robert\_Hrynciw](https://avatars.discourse-cdn.com/v4/letter/r/3bc359/32.png) [@Robert\_Hrynciw](https://forum.inductiveautomation.com/u/Robert_Hrynciw)\ **Post date:** [June 6, 2023, 12:39pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/3 "2023-06-06T12:39:52Z")
The problem with SELECT TOP 1 is that it will only give me one EID's values, not each EID in the table values. ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [June 6, 2023, 12:49pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/4 "2023-06-06T12:49:33Z")
Have you tried using a subquery? ```auto SELECT [Fname], [Lname], [EID], [TradeSkill], [Shift], [CurrentHours], [DateTime] FROM (SELECT [Fname], [Lname], [EID], [TradeSkill], [Shift], [CurrentHours], [DateTime], ROW_NUMBER() OVER (PARTITION BY [EID] ORDER BY [DateTime] DESC) AS RowNum FROM [Maintenance].[dbo].[MOS_Signup]) AS Subquery WHERE RowNum = 1 ORDER BY [DateTime] DESC ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 6, 2023, 12:52pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/5 "2023-06-06T12:52:42Z")
I would normally subquery with group by EID to get the `max(DateTime)` and join that back to the full table to get the other columns. If you have an index on EID and DateTime, it should be very efficient. ---
**Author:** ![Robert\_Hrynciw](https://avatars.discourse-cdn.com/v4/letter/r/3bc359/32.png) [@Robert\_Hrynciw](https://forum.inductiveautomation.com/u/Robert_Hrynciw)\ **Post date:** [June 6, 2023, 1:01pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/6 "2023-06-06T13:01:34Z")
I'm now running this query, only running into one problem...When people change EID (Clock Number)..It's still getting old Clock Numbers. Is there a way to cut this out? Another subquery? ```auto SELECT old.Fname, old.Lname, old.EID, old.TradeSkill, old.Shift, old.CurrentHours, old.DateTime FROM [Maintenance].[dbo].[MOS_Signup] old INNER JOIN ( SELECT EID, max(DateTime) as MaxDate FROM [Maintenance].[dbo].[MOS_Signup] GROUP BY EID ) new ON old.EID = new.EID and old.DateTime = new.MaxDate WHERE old.TradeSkill = 0 or old.TradeSkill = 1 or old.TradeSkill = 3 or old.TradeSkill = 4 ORDER BY old.Lname asc ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 6, 2023, 1:15pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/7 "2023-06-06T13:15:44Z")
> [@Robert\_Hrynciw](#): > > When people change EID What's the unique part? Fname and Lname? If so, just group by those in the subquery instead. ---
**Author:** ![Robert\_Hrynciw](https://avatars.discourse-cdn.com/v4/letter/r/3bc359/32.png) [@Robert\_Hrynciw](https://forum.inductiveautomation.com/u/Robert_Hrynciw)\ **Post date:** [June 6, 2023, 1:28pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/8 "2023-06-06T13:28:39Z")
The EID is the unique part, the only problem is when someone gets a replacement they keep their first, last name and change clock number so now there's a duplicate in a sense. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 6, 2023, 1:52pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/9 "2023-06-06T13:52:49Z")
I would say if the same name can have multiple EIDs, then EID is not unique. 🤷‍♂️ I can't advise you further. Just choose which is more important to your case. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [June 6, 2023, 2:18pm UTC](https://forum.inductiveautomation.com/t/sql-query-pulling-last-value-for-certain-records/74629/10 "2023-06-06T14:18:28Z")
The combination of EID, Fname, and Lname is unique. Try this: ```auto SELECT old.Fname, old.Lname, old.EID, old.TradeSkill, old.Shift, old.CurrentHours, old.DateTime FROM [Maintenance].[dbo].[MOS_Signup] old INNER JOIN ( SELECT EID,Fname,Lname, max(DateTime) as MaxDate FROM [Maintenance].[dbo].[MOS_Signup] GROUP BY EID,Fname,Lname ) new ON old.EID = new.EID and old.DateTime = new.MaxDate WHERE old.TradeSkill = 0 or old.TradeSkill = 1 or old.TradeSkill = 3 or old.TradeSkill = 4 ORDER BY old.Lname asc ```