Select the last record in each group

Question

There is a table Remark that contains data as shown below:

       SerialNo | RemarkNo  | Desp
=============================================
             10 |         1 | rainy
             10 |         2 | sunny
             11 |         1 | sunny
             11 |         2 | rainy
             11 |         3 | cloudy
             12 |         1 | rainy

If I run a query SELECT * FROM remark WHERE remark_no IN (SELECT MAX(remark_no) FROM remark GROUP BY serial_no);, I still get the above result:

What query will return the following result:

             10 |         2 | sunny
             11 |         3 | cloudy
             12 |         1 | rainy

That is, the last record in each group should be returned??

possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — newtover, Mar 16 '13 at 18:38

score 1 · Accepted Answer · edited Mar 16 '13 at 18:36

1

SELECT
  r1.*
FROM remark r1
  LEFT JOIN remark r2
    ON (r1.serial_no = r2.serial_no
    AND r1.remark_no < r2.remark_no)
WHERE r2.remark_no IS NULL;

edited Mar 16 '13 at 18:36

Muhammad Raheel

19,823
7
67
103

answered Mar 16 '13 at 18:33

Kim Stacks

10,202
35
151
282

AjV Jsy · Answer 2 · 2013-03-16T21:00:57.383

0

For the top RemarkNo for each SerialNo (along with the other fields from the same row) :
Select all records where there isn't a higher RemarkNo for the same SerialNo

SELECT *
FROM remark r1
WHERE NOT EXISTS
  (SELECT SerialNo FROM remark r2
   WHERE (r2.RemarkNo>r1.RemarkNo)
     AND (r2.SerialNo=r1.SerialNo)
  )

http://sqlfiddle.com/#!2/7da1b/21

edited Mar 16 '13 at 21:00

answered Mar 16 '13 at 18:11

AjV Jsy

5,799
4
34
30

score -2 · Answer 3 · answered Mar 16 '13 at 18:12

-2

You can do this by returning your results and using a sort order. For example...

SELECT * from GROUP Order By GROUP.ID DESC

This will return results in the order of last record first. Then if you do not loop through results you will return only one record... the last one recorded.

answered Mar 16 '13 at 18:12

WilliamK

821
1
13
32

Ever wondered what can slow down the performance of database results the most? It is joined tables. – WilliamK Mar 16 '13 at 18:40

Select the last record in each group

3 Answers3