[FIXED] Is it possible to have a DISTICT statement only on one varible?

Issue

I only want the SELECT DISTINCT statement on 'yearLevel' (as I don't want any duplicates only on yearLevel)

image of data displayed on screen

For example I only want one 'yearLevel' 12 record in the table, and for that to be the one with the lowest 'totalSeconds'

code

$sql = "SELECT firstName, lastName, yearLevel, totalSeconds 

FROM studentInformation
  JOIN record
    ON studentInformation.studentId = record.student_Id

    ORDER BY totalSeconds ASC 
    LIMIT  1  "; 

Is it possible to do this -

$sql = "SELECT firstName, lastName, DISTINCT yearLevel, totalSeconds 

    FROM studentInformation
      JOIN record
        ON studentInformation.studentId = record.student_Id

        ORDER BY totalSeconds ASC 
        LIMIT  1  "; 

Solution

Provided that "firstName" and "lastName" are in table "studentInformation", and "yearLevel" as well as "totalSeconds" are in table "record" this query should be working. It uses a correlated subquery. I did not test this; if it doesn't work please let me know.

    SELECT a.firstName, a.lastName, b.yearLevel, b.totalSeconds
      FROM studentInformation a
INNER JOIN record b ON a.studentId = b.studentId
     WHERE b.totalSeconds = ( SELECT min(totalSeconds)
                                FROM record
                               WHERE yearLevel = b.yearLevel )
  ORDER BY b.totalSeconds ASC

Assuming that only "totalSeconds" is in table "record" this could work, too.

    SELECT a.firstName, a.lastName, a.yearLevel, b.totalSeconds
      FROM studentInformation a
INNER JOIN record b ON a.studentId = b.studentId
     WHERE b.totalSeconds = ( SELECT MIN(d.totalSeconds)
                                FROM studentInformation c
                          INNER JOIN record d ON c.studentId = d.studentId
                               WHERE c.yearLevel = a.yearLevel )
  ORDER BY b.totalSeconds ASC

Answered By - rf1234