Thread: Selecting the 3rd highests item from a table?

1. Registered User
Join Date
Feb 2004
Posts
3

Unanswered: Selecting the 3rd highests item from a table?

I want to figure out if a student has not been to class in the last 3 scheduled days. The days do not have to be consecutive.

I have 2 tables. One table with schedules and one with attended time.

What I really need is to the find the 3rd highest MAX() from the schedule table for each student. I was wondering if someone has any idea how I could find these record?

Example scheduled records
student1 2/4/2004 7 hours scheduled
student2 2/4/2004 8 hours scheduled
student1 2/3/2004 8 hours scheduled
student1 2/2/2004 6 hours scheduled <---Need this one
student2 2/2/2004 8 hours scheduled
student2 1/30/2004 4 hours scheduled <---Need this one
...thousands more...

It has been a real brain teaser for me. If anyone has even an inelegant solution, I would love to see it.

2. Registered User
Join Date
Jul 2002
Posts
58

Re: Selecting the 3rd highests item from a table?

Originally posted by GeoPoko
I want to figure out if a student has not been to class in the last 3 scheduled days. The days do not have to be consecutive.

I have 2 tables. One table with schedules and one with attended time.

What I really need is to the find the 3rd highest MAX() from the schedule table for each student. I was wondering if someone has any idea how I could find these record?

Example scheduled records
student1 2/4/2004 7 hours scheduled
student2 2/4/2004 8 hours scheduled
student1 2/3/2004 8 hours scheduled
student1 2/2/2004 6 hours scheduled <---Need this one
student2 2/2/2004 8 hours scheduled
student2 1/30/2004 4 hours scheduled <---Need this one
...thousands more...

It has been a real brain teaser for me. If anyone has even an inelegant solution, I would love to see it.

Code:
```SELECT MIN(q)
FROM (SELECT TOP 3 q = ?
FROM <your table, JOIN clauses as necessary>
ORDER BY ? DESC) AS u```
? of course is the column you're looking for the 3rd highest of. I'd have to know your table structure to complete the inner query.

3. Registered User
Join Date
Dec 2003
Posts
454
Do you want to select the students's records in which the students appears in the table Schedules the third time?

4. Registered User
Join Date
Feb 2004
Posts
3

Thanks...I got it

Thanks large Steve,

That did it. I put your code into a sub select and it works surprisingling fast (about .5 secs for 343 students and over 700,000 StudentSchedule records). I first select all active students (with the inner join to the ClassMaster table). Then for each active student I do the select you suggested to get the 3rd oldest StudentSchedule record and save the ID.
I will just save this record set into a temp table then join and pair down those that have not been in class since the date on the record found.

SELECT
s.StudentID
, ( SELECT MIN(ssID)
FROM ( SELECT TOP 3 ssID = ss2.StudentScheduleID
FROM StudentSchedule ss2
WHERE ss2.StudentID = s.StudentID
ORDER BY StudentScheduleDate DESC
) as temp1
) AS StudentScheduleID
FROM Student s
INNER JOIN StatusMaster sm ON s.StudentStatusID = sm.StatusID
WHERE sm.IsActive = 1

George Pokorny

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•