Select last inserted records and grouping mysql

Question

Im having a table with notes. Below is a small sample of data of my db.

Id  register_no     name        classification      possibility
1   112             Ben         Red                 10%
2   112             Ben         Red                 10%
3   113             Ben         Red                 20%
4   112             Ben         Amber               30%
5   112             Ben         Amber               10%
6   113             Ben         Amber               30%
7   113             Ben         Red                 10%
8   112             Ben         Red                 50%

I’m sorry i interpreted the question in a wrong manner.

Actually what i wanted is to retrieve the last inserted record for each register_no because sales_log table holds a list progress...

for an example Register_no 112 has 12 records in the database and the last record is 90% (it progressed from 10,40,60% so all of these records are in the db)

what i want is to get the 90% of register_no 112 and so on... its not just one record... there is around 500 register_no and i want to get the last entry for each register_no

hope it makes sense this time

is it that you want to fetch only maximum records for each register id? e.g. ids 6 & 8 are selected. — Omesh, Jul 24 '12 at 11:18

score 3 · Accepted Answer · edited May 23 '17 at 10:09

3

You should try this query to get the notes for register numbers having maximum id value:

SELECT n1.*
FROM   notes n1
       LEFT JOIN notes n2
           ON (n1.register_no = n2.register_no AND n1.id < n2.id)
WHERE  n2.id IS NULL;

Source: Retrieving the last record in each group

edited May 23 '17 at 10:09

Community

1
1

answered Jul 24 '12 at 10:03

Omesh

27,801
6
42
51

there are around 200 register numbersand various counts of records. i cant use the limit. – LiveEn Jul 24 '12 at 10:25
okay then you can remove LIMIT clause. can you elaborate your exact requirements? – Omesh Jul 24 '12 at 10:31
sorry that i had put the question up wrong. i have explained it will hope it makes sense now.. – LiveEn Jul 24 '12 at 10:47

score 2 · Answer 2 · answered Jul 24 '12 at 10:05

2

Try

SELECT * FROM (`sales_log`) WHERE 1 ORDER BY `id` DESC LIMIT 2

Telling msyql to match 'name = ben' will never find mandy's id 7

answered Jul 24 '12 at 10:05

mschr

8,531
3
21
35

Why `WHERE 1`? That is a tautology. – phant0m Jul 24 '12 at 10:14
@mschr there was a problem in the question... i have corrected it. If you could check it again and provide with a solution it would be great. – LiveEn Jul 24 '12 at 10:49

score 2 · Answer 3 · answered Jul 24 '12 at 10:12

2

Use

SELECT * FROM sales_log WHERE `classification` =  'red' ORDER BY `Id` DESC LIMIT 2;

answered Jul 24 '12 at 10:12

Bhuvan Rikka

2,683
1
17
27

phant0m · Answer 4 · 2012-07-24T11:22:38.417

2

This should work:

SELECT data.* 
FROM sales_log AS data
JOIN 
    (
        SELECT MAX(Id) as Id 
        FROM sales_log 
        GROUP BY register_no
    ) AS newest
    ON data.Id = newest.Id

Result:

'8', '112', 'Ben', 'Red', '50'
'7', '113', 'Ben', 'Red', '10'

Edit: I just looked at @Omesh's link, apparently the solution he posted is a lot faster. It produces the exact same output as my query.

edited Jul 24 '12 at 11:22

answered Jul 24 '12 at 11:17

phant0m

16,595
5
50
82

Select last inserted records and grouping mysql

4 Answers4