Displaying Distinct Max Value autonumber rows from tables in MySql

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

what do you mean by 'maximim value of sno column'? Do you mean maximum value for each fir_sno? — Derek Downey, Jan 19 '16 at 15:17
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 '16 at 21:40
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 '16 at 02:21
@Rick James i have multiple occurrences of fir_sno in the pre_trials table and pre_trials is the child table of fir table which is the master table, fir table's sno is the fir_sno in the pre_trials table and it is a foreign key herein pre_trials table. My question is that "How to access the last occurrence of fir_sno in the pre_trials table" or in other words I want to display all the distinct rows having DESC order limit 1" ? — Abdul Rahman, Jan 20 '16 at 05:37
For each distinct fir_sno (values 1,2), you want the 'latest' row (max(date))? Hence you want 2 rows: ids 4,5? That is what "groupwise max" is s about -- group by fir_sno, but deliver the last (max date) row for for each. — Rick James, Jan 20 '16 at 20:59
fir_sno, according to your sample data, cannot be the PRIMARY KEY, at least not by itself. The sample data show multiples rows for the same value of fir_sno. Please provideSHOW CREATE TABLE`. — Rick James, Jan 22 '16 at 01:08
@RickJames, i have added the "Show Create Table fir and pre_trials — Abdul Rahman, Jan 22 '16 at 17:46
Please add the CREATE TABLE statements as text, not as screenshots. — ypercubeᵀᴹ, Jan 22 '16 at 17:53

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

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

Displaying Distinct Max Value autonumber rows from tables in MySql

1 Answers1