CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
John
John

Reputation: 1538

How to select the row of a minimum value of a group, while also using a where clause

I have a table that tracks attendance in a course. The columns are the courseid, lesson, personid, and date. I have a query (below) that extracts the earliest date a person appears along with the associated course, lesson, and personid. This is used to determine when a person started a particular course and ensure they started with the first lesson. This works fine, but where I am stuck is running this query per course. For example, finding the first date each person in a particular course started it rather than for every course. Right now I am just running the more general query and filtering it in the biz layer.

I obfuscated this a bit so forgive any typos:

select a.courseid, 
       a.lesson, 
       a.personid, 
       a.thedate 
from (select personid, 
             min(thedate) as earliestdate 
      from attendance 
      group by personid) as x 
inner join attendance as a on (a.personid = x.personid and a.thedate = x.thedate)

Upvotes: 2

Views: 2492

Answers (2)

Uday
Uday

Reputation: 1490

I have a small doubt here. Currently all the information is maintained in single data object/table. How would it be if we have different data model like below...?

Objext1:

  1. Course table: Course details having lession with a relation

  2. Student table: Contains student details.

Will the querying would be simplified in this way....?

Sorry if anything sounds immatur... Regards, UDAY

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

Just group over person_id, course in the inner query: 

select a.courseid, a.lesson, a.personid, a.thedate 
from (
    select personid, courseid, min(thedate) as earliestdate 
    from attendance 
    group by personid, courseid
) as x 
inner join attendance as a 
  on (a.personid = x.personid and 
      a.thedate = x.thedate and 
      a.courseid=x.course_id)

Upvotes: 5

Related Questions