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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy