6 Replies - 686 Views - Last Post: 17 July 2012 - 08:03 AM Rate Topic: -----

#1 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Finding Last(n) in a sorted query.

Posted 16 July 2012 - 02:03 PM

To start off with. This is being done in Access :( and I'm not getting the results I expect.

My data is
RecordID     EmpID     Type      ClockTime  ClockDate
1            35        CLOCKOUT  2:55 PM    7/16/2012
2            35        CLOCKIN   1:55 PM    7/16/2012



As you can see they are backwards in the database because an employee forgot to clockin and it was entered by the administrator putting it in the next available row.

What I'm trying to do is find the last type performed on the given date by the operator. Of course a simple
SELECT Last(Type) as LastType FROM TimeClock
WHERE EmployeeID = 35 And ClockDate = #7/16/2012# 


yields CLOCKIN but the employee has actually clocked out and that should be the last type found. So I figured I needed to order the data.

Since adding time so that I can order it would produce both rows except in chronological order I needed to select from the result of that select statement. I thought this would work.
SELECT Last(Type) as LastType FROM (
SELECT Type, ClockTime FROM TimeClock
WHERE EmpID = 35 and ClockDate = #7/16/2012#
ORDER BY ClockTime)

Again though this produced CLOCKIN as the last(n) record.
Am I going about this wrong? I was under the impression that the Select inside the FROM that has CLOCKOUT as the last result would be the result of the Last(ClockType) in the sorted result.

Is This A Good Question/Topic? 0
  • +

Replies To: Finding Last(n) in a sorted query.

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Finding Last(n) in a sorted query.

Posted 17 July 2012 - 01:30 AM

may i know what is the field type of clock time,if it is datetime then use the max funtion or else we need to work a little bit
now i considered the clocktime as a datetime field
SELECT Type as LastType FROM TimeClock
WHERE EmpID = 35 and ClockDate = #7/16/2012#
 AND ClockTime(
SELECT max(ClockTime) FROM TimeClock
WHERE EmpID = 35 and ClockDate = #7/16/2012#
)


hopr this will solve your problem
Was This Post Helpful? 1
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5881
  • View blog
  • Posts: 12,758
  • Joined: 16-October 07

Re: Finding Last(n) in a sorted query.

Posted 17 July 2012 - 05:39 AM

In the above you forgot an =. However, while you're at it, I'd prefer a join and to only type parameters once, so:
SELECT a.Type as LastType
	FROM TimeClock a
		INNER JOIN (
			SELECT EmpID, ClockDate, MAX(ClockTime) as ClockTime
				FROM TimeClock
				WHERE EmpID = 35 
					AND ClockDate = #7/16/2012#
				GROUP BY EmpID, ClockDate
			) b 
				ON a.EmpID=b.EmpID 
					AND a.ClockDate=b.ClockDate
					AND a.ClockTime=b.ClockTime


Was This Post Helpful? 1
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Finding Last(n) in a sorted query.

Posted 17 July 2012 - 07:04 AM

thava, thanks. That works.

baavgai, Thanks, I saw the missing = when I read his code last night. Giving your example a try first gave a circular reference on the As ClockTime so I just renamed the aggregate field name. Then, it prompted for Enter Parameter Value b.ClockTime. I tried removing the last line and a result was produced but it just displayed all the ClockTypes for that specified date, and empID instead of the one with the greatest time. And then it hit me. I had to remove the last line because b.ClockTime no longer existed due to renaming it. So once I fixed that it worked.

Thanks guys. Both examples produce the result I needed. I really need to get a lot stronger with my sql. Time to purchase a book on the subject, what would you recommend for someone that want's to go beyond simple select statments and parsing the results to letting the query produce the exact result like this one?
Was This Post Helpful? 0
  • +
  • -

#5 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 484
  • View blog
  • Posts: 3,244
  • Joined: 12-January 10

Re: Finding Last(n) in a sorted query.

Posted 17 July 2012 - 07:20 AM

Are you trying to find the last (n) as in the last one created or the newest one created or the last numerical (n)?
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Finding Last(n) in a sorted query.

Posted 17 July 2012 - 07:52 AM

DarenR, Both examples above solved the problem. But it was finding the last chronological occurrence of a given employee and date regardless of where it appeared in the table. As the example above shows. From the table (taking the time out) it would appear that the operator clocked out and then clocked in and last(n) of the type would produce clockin. But with the time involved, it shows that the employee clocked in and then clocked out. I was just trying to find the last(n) of the type as it would appear chronologically.
Was This Post Helpful? 0
  • +
  • -

#7 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 484
  • View blog
  • Posts: 3,244
  • Joined: 12-January 10

Re: Finding Last(n) in a sorted query.

Posted 17 July 2012 - 08:03 AM

yeah, I see that now--- your previous post wasnt showing up on my end or I wouldn't have even bothered typing what I did. Glad you got it resolved.. Have a good one...........
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1