CODEWITHSUNDEEP
phpmysqlsql
LiveEn
LiveEn

Reputation: 3253

Select last inserted records and grouping mysql

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

Upvotes: 2

Views: 2098

Answers (4)

Omesh
Omesh

Reputation: 29111

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

Upvotes: 3

phant0m
phant0m

Reputation: 16905

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.

Upvotes: 2

Bhuvan Rikka
Bhuvan Rikka

Reputation: 2703

Use

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

Upvotes: 2

mschr
mschr

Reputation: 8641

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

Upvotes: 2

Related Questions