0

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  ";
Luna Steven
  • 3
  • 2
  • please give the clear requirement , what exactly you want to fetch – Ashish Shetkar Apr 21 '20 at 07:06
  • You seem to be looking for the row with the groupwise minimum. See e.g. [here](https://stackoverflow.com/q/15211479) or [here](https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html). – Solarflare Apr 21 '20 at 08:19

1 Answers1

0

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
rf1234
  • 1,510
  • 12
  • 13
  • "firstName" , "lastName" , and "yearLevel" are in the table "studentInformation" . Only "totalSeconds" is in the "record" table. – Luna Steven Apr 22 '20 at 05:21
  • ok, I updated the query. But even if this doesn't work you should be able to fix it by yourself. Just google "correlated subquery" please. – rf1234 Apr 22 '20 at 07:07