New SQL user - I'm trying to extract the latest record in my query. Accountant new to SQL

Question

For every AbsenceBalance.AbsenceTypesUID I want to return the latest record AbsenceBalance.BalanceTime for each AbsenceBalance.EmployeeUID

I have tried select max but it only returns the most recent entry for the entire table and not by AbsenceBalance.AbsenceTypesUID or AbsenceBalance.EmployeeUID

This is my query

SELECT TOP (1000) 
AbsenceBalance.[UID],
AbsenceBalance.BalanceTime,
AbsenceBalance.AbsenceTypesUID,
AbsenceBalance.Mins,
Employee.FullName,
Employee.FirstName,
Employee.LastName,
AbsenceBalance.EmployeeUID,
absencetypes.LongName

  from [RiteqDB].[dbo].[AbsenceBalance]


  
  
  LEFT JOIN [RiteqDB].[dbo].Employee on AbsenceBalance.EmployeeUID = Employee.UID

  LEFT JOIN [RiteqDB].[dbo].AbsenceTypes on absencebalance.AbsenceTypesUID = absencetypes.UID 

  where AbsenceBalance.[UID] = (select max (AbsenceBalance.[UID]) from [RiteqDB].[dbo].[AbsenceBalance] where AbsenceBalance.AbsenceTypesUID = AbsenceBalance.AbsenceTypesUID)

  --where Select Max(v) from (values (AbsenceBalance.BalanceTime)

  order by FullName, AbsenceTypesUID

By last do you mean most recent? "The last record that was added?" Or last as in, on a list of all records, I want the first one that was ever inputted? — ImCrimson, Nov 25 '20 at 02:46

score 1 · Answer 1 · answered Nov 25 '20 at 02:51

It sounds like you might need a group by link, then either use an inner select in a where (like you have) or use this with an inner join.

SELECT 
Max(AbsenceBalance.[UID]),
AbsenceBalance.AbsenceTypesUID,
AbsenceBalance.EmployeeUID,

from [RiteqDB].[dbo].[AbsenceBalance]

GROUP BY AbsenceTypesUID, EmployeeUID

score 0 · Answer 2 · answered Nov 25 '20 at 02:35

In your where condition your comparing with itself that might be the issue.

select max (AbsenceBalance.[UID]) from [RiteqDB].[dbo].[AbsenceBalance] 
where AbsenceBalance.AbsenceTypesUID = AbsenceBalance.AbsenceTypesUID

following is wrong

AbsenceBalance.AbsenceTypesUID = AbsenceBalance.AbsenceTypesUID

Sai Abhiram Inapala · Accepted Answer · 2020-11-25T03:35:31.833

0

;with cte as (
SELECT TOP (1000) AB.[UID]
                 ,AB.BalanceTime
                 ,AB.AbsenceTypesUID
                 ,AB.Mins
                 ,E.FullName
                 ,E.FirstName
                 ,E.LastName
                 ,AB.EmployeeUID
                 ,AT.LongName
                 , ROW_NUMBER() OVER(PARTITION BY AB.[UID], AB.EmployeeUID order by AB.BalanceTime DESC) AS RUN
FROM [RiteqDB].[dbo].[AbsenceBalance] AB
LEFT JOIN [RiteqDB].[dbo].Employee E ON AB.EmployeeUID = E.UID
LEFT JOIN [RiteqDB].[dbo].AbsenceTypes AT ON AB.AbsenceTypesUID = AT.UID    
)
select * from cte
where RUN = 1

edited Nov 25 '20 at 03:35

answered Nov 25 '20 at 03:13

Sai Abhiram Inapala

359
1
9

Thanks Sai! That worked even though I have no idea how it worked. – Aley Mohsen Nov 25 '20 at 03:44
For your understanding try to run just the script that in wrapped inside the CTE (line 2 to 13) and try to focus on the column Run and understand what it is doing. maybe select top 10 or 20. – Sai Abhiram Inapala Nov 25 '20 at 03:54

New SQL user - I'm trying to extract the latest record in my query. Accountant new to SQL

3 Answers3