Get MAX value in mysql query

Question

I have a table

       id     mid    userid    remarks
       1       2       8          7 
       2       2       8          6
       3       2       8          4 
       4       2       8          5
       5       2       8          2
       6       2       8          3
       7       2       8          7
       8       2       8          0
       9       2       8          1
       10      2       8          8

I need the last row of remark before that row. i.e., remark '1'

SELECT MAX(id),mid,userid,remarks FROM sample

You need the remark with the largest or 2'nd largest id? – Magnus Aug 11 '12 at 09:15 — Magnus, Aug 11 '12 at 09:15
Yes Absolutely I need 2nd largest – thersa Aug 11 '12 at 09:17 — thersa, Aug 11 '12 at 09:17

score 20 · Accepted Answer · answered Aug 11 '12 at 09:18

20

Select Id,mid,userid,remarks from sample Where id<(select max(Id) from sample)
order by id desc limit 1

Or

Select Id,mid,userid,remarks from sample 
order by id desc limit 1 offset 1

answered Aug 11 '12 at 09:18

valex

23,966
7
43
60

Thank You for your answer. I want to know what this offset do? – thersa Aug 11 '12 at 15:43
@thersa These LIMIT and OFFSET commands work only in MYSQL. "LIMIT 1" takes first ONE row from result set. OFFSET 1 shifts start row of "LIMIT 1" to the second row. "OFFSET 2" shifts it to the 3'd row... – valex Aug 11 '12 at 20:25

Prince Jea · Answer 2 · 2012-08-11T09:41:19.830

4

Try this:

    SELECT MAX(id),mid,userid,remarks 
    FROM sample WHERE id NOT IN  (
    SELECT MAX(id) FROM sample
    )
    GROUP BY mid,userid,remarks

EDIT

See if this works

SQL FIDDLE DEMO

edited Aug 11 '12 at 09:41

answered Aug 11 '12 at 09:15

Prince Jea

5,524
7
28
46

Looks like I misunderstood the question – Prince Jea Aug 11 '12 at 09:17
It shows #1052 - Column 'id' in IN/ALL/ANY subquery is ambiguous – thersa Aug 11 '12 at 15:41

WasiF · Answer 3 · 2019-03-11T05:26:05.140

for mysql Node.js developers especially

I was unable to get direct-value in mysql-driver for node.js i.e. when I run the following query

SELECT MAX(id) FROM Users

and I got the result as [RowDataPacket { MAX(id): 1 }]

I tried to retrieve the value from object console.log(MAX(id)), but I couldn't

so I set an alias i.e. maxid

SELECT MAX(id) as maxid FROM Users

and got it as [RowDataPacket { maxid: 1 }]

Which I can retrieve as console.log(maxid)

Get MAX value in mysql query

3 Answers3

Linked