Displaying Distinct Max Value autonumber rows from tables in MySql [duplicate]

Question

i am trying to execute the distinct records from mysql table having max value of it sno column. I have tried the following query but having no luck.

SELECT DISTINCT p.sno AS sno, p.short_order,p.fir_sno 
FROM pre_trials p, fir f 
WHERE p.fir_sno = f.sno 
GROUP BY p.fir_sno 
ORDER BY p.sno ASC

For the above query i have the following table:

It should return sno 4,5 because both having the maximum value of sno column. I don't know how to retrieve the record according to my requirements? Any help will be appreciated. thanks

EDIT: Show Create Table fir,pre_trials

Answer 1

Assuming that fir is the master table and (sno) its primary key:

SELECT p.sno, p.short_order, p.fir_sno 
FROM fir AS f
  JOIN pre_trials AS p
    ON  p.fir_sno = f.sno 
    AND p.sno = 
        ( SELECT pi.sno
          FROM pre_trials AS pi
          WHERE pi.fir_sno = f.sno 
          ORDER BY pi.sno DESC
          LIMIT 1
        ) ;

A composite index on (fir_sno, sno) would help greatly with efficiency of the query.

Also note that I used DESC while your code has ASC beacuse in your explanation and wanted results you mention you want the row with maximum sno.

Look also at a similar question, for further explanation of how the query works: Select ONE most recent post for each author