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 

For the above query i have the following table:

enter image description here

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

enter image description here

  • what do you mean by 'maximim value of sno column'? Do you mean maximum value for each fir_sno? Jan 19, 2016 at 15:17
  • yes, i exactly mean that. Jan 19, 2016 at 16:56
  • I need to pick the top most record of each fir_sno Jan 19, 2016 at 17:03
  • Give us SHOW CREATE TABLE My_Table\G for both tables, sample data (in the form INSERT INTO My_TABLE VALUES (......);. then give us your desired result andthe logic you used to get that result. Take the tour, check out the help us to help you blog (bottom left of page) and visit the help centre (top right). The clearer your question, the better your chances of getting a good answer.
    – Vérace
    Jan 19, 2016 at 21:40
  • 1
    You are into the groupwise max class of problems. As you will see from that link, it is not straightforward to get what you want.
    – Rick James
    Jan 20, 2016 at 2:21

1 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


Not the answer you're looking for? Browse other questions tagged or ask your own question.