Results 1 to 4 of 4
  1. #1
    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. #2
    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.
    How about this.....

    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. #3
    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. #4
    Join Date
    Feb 2004
    Posts
    3

    Talking 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
  •