Top1 record for each employee 2

[emerickson]

Hello,

I have a table with all kinds of absence records for all employees, but I only want to get the last absence for each employee. Here is what I tried:

SELECT DISTINCT SocSec, BeginAbsenceDate, AbsenceCode,
(SELECT TOP 1 BeginAbsenceDate
FROM tblAttendance
WHERE socsec = tblAttendance.SocSec
ORDER BY BeginAbsenceDate DESC) FROM tblAttendance
ORDER BY SocSec

Instead of giving me the latest record of each employee, it gives me all records with the date of the last record attached to each employee's record.

If it can't be solved with SQL, is there a way I can just have a plain SQL select statement, but then use some ColdFusion to retrieve the last record for each employee from the query?

Thanks,
Eva Erickson

 

[webmigit]

Could use

Select DISTINCT SocSec FROM tableAttendance
WHERE [AbsenceIdentifierFieldName] = [AbsenceIdentifier]
order by BeginAbsenceDate DESC

(AbsenceIdentifier being the name of the field that identifies this attendance entry as an absence.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[pcorreia]

I think what you want is this:

SELECT 
   a1.SocSec, 
   a1.BeginAbsenceDate, 
   a1.AbsenceCode
FROM tblAttendance AS a1
WHERE
   a1.BeginAbsenceDate = (
      SELECT MAX(a2.BeginAbsenceDate)
      FROM a2.tblAttendance
      WHERE a2.SocSec = a1.SocSec
   )
ORDER BY SocSec

The columns of data you want to retrieve are simple: SSN, date of absence, and type of absence. There's nothing complicated about the data you are selecting, so there will probably be nothing complicated about the SELECT clause. Where it gets complicated is in narrowing the set of records returned -- so, try to express the criteria in a WHERE clause.

 

[liark]

I needed the last record entered by a user too - thanks for this code - works for me like this:

SELECT 
	a1.SocSec, 
	a1.BeginAbsenceDate, 
	a1.AbsenceCode
FROM tblAttendance AS a1
WHERE
	a1.BeginAbsenceDate = (
		SELECT MAX(a2.BeginAbsenceDate)
		FROM tblAttendance as a2
		WHERE a2.SocSec = a1.SocSec
		)
ORDER BY SocSec

Liark