September 22, 2005 at 7:59 am
Is there an Aggregate Function for getting the last value of a column in SQL Server (similiar the the Access last Function?), or does somebody have a suggestion on another way to find this value? Thanks.
September 22, 2005 at 8:29 am
You could try select top 1 [column_name] from table_name order by [column_name] desc
September 22, 2005 at 8:32 am
There's no first or last row in a table. To get the first something you MUST use order by in combinaison with top 1.
You could also check out the max and min functions and they may help in such a context.
September 22, 2005 at 8:49 am
Hmmm, I don't just need one though, I need the last entry per each user ID.
Can I use Top on a per column basis e.g.:
(Top 1 MyColumn) as TopColumn?
Hmmm....I would guess not.
September 22, 2005 at 8:54 am
Alright.
Post the table definition, some sample data and the expected results and we'll help you figure it out.
September 22, 2005 at 8:59 am
I'm trying to convert something somebody wrote in Access to SQL (Hence the last). Here is the original query:
SELECT qryDLOnCall1st.Emp_Dist, qryDLOnCall1st.Emp_Dept, qryDLOnCall1st.Emp_ID, qryDLOnCall1st.FullName, qryDLOnCall1st.Emp_ClassName, qryDLOnCall1st.Emp_Phone1, qryDLOnCall1st.Emp_Phone2, Max(qryDLOnCall1st.MaxOfCallDate) AS MaxOfMaxOfCallDate, Last(tblCallOuts.CallTime) AS LastOfCallTime
FROM (tblEmpName INNER JOIN qryDLOnCall1st ON tblEmpName.Emp_ID = qryDLOnCall1st.Emp_ID) LEFT JOIN tblCallOuts ON tblEmpName.Emp_ID = tblCallOuts.Emp_ID
GROUP BY qryDLOnCall1st.Emp_Dist, qryDLOnCall1st.Emp_Dept, qryDLOnCall1st.Emp_ID, qryDLOnCall1st.FullName, qryDLOnCall1st.Emp_ClassName, qryDLOnCall1st.Emp_Phone1, qryDLOnCall1st.Emp_Phone2
ORDER BY Max(qryDLOnCall1st.MaxOfCallDate), Last(tblCallOuts.CallTime);
The table is ordered by date and time, and basically is just pulling the last record by date/time for each employee.
September 22, 2005 at 9:04 am
This is how it's done in sql server.
Select * from dbo.Employees E inner join (Select EmpID, MAX(Date) AS Date from dbo.Employees) dtMax on E.EmpID = dtMax.EmpID and E.Date = dtMax.Date
September 22, 2005 at 9:12 am
Hey thanks for your help by the way.
The problem I am having is the Date and Time are split into two different fields. If it was one DateTime field I could just do Max and be done with it, but how do I get the associated Time field?
September 22, 2005 at 9:16 am
can you just recreate the date??
MAX (CAST (field1 + field as datetime))
September 22, 2005 at 9:33 am
Awesome, that works. Thanks alot, I really appreciate the help.
September 22, 2005 at 9:51 am
HTH.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply