CODEWITHSUNDEEP
mysqlselectdistinct
Methew
Methew

Reputation: 365

Select last distinct records in mysql

I have a table in mysql have a lot of data.i want to display only distinct and last saved data...here is what i have

CREATE TABLE amounts(
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    bank_id INT,
    amount INT,
    PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO `amounts` (`id`, `bank_id`, `amount`) VALUES (NULL, '1', '100'),
(NULL, '2', '200'),(NULL, '3', '300'),(NULL, '1', '500'),(NULL, '2', '600'),
(NULL, '1',   '800'),(NULL, '2', '50');

I want result like this

  bank_id    amount
  1           800
  2            50
  3            300

how will be select query for this?

Upvotes: 1

Views: 2934

Answers (3)

Arnold Daniels
Arnold Daniels

Reputation: 16553

Use a self join. This is almost always faster than a subquery.

SELECT a.*
  FROM amounts a LEFT JOIN amounts b ON a.bank_id = b.bank_id AND a.id < b.id
  WHERE b.id IS NULL

See a demo

Upvotes: 2

fthiella
fthiella

Reputation: 49049

You could use a subquery where you select max IDs for every bank_id, then you select all rows whose IDs are returned by this subquery:

SELECT amounts.*
FROM   amounts
WHERE  id IN (select max(id)
              from amounts
              group by bank_id)
ORDER BY bank_id

Please see this fiddle.

Upvotes: 1

Kermit
Kermit

Reputation: 34054

This should do it:

SELECT a.bank_id, 
       b.amount 
FROM   (SELECT bank_id, 
               Max(id) AS id 
        FROM   amounts 
        GROUP  BY bank_id) a 
       INNER JOIN amounts b 
               ON b.id = a.id

Result

| BANK_ID | AMOUNT |
--------------------
|       1 |    800 |
|       2 |     50 |
|       3 |    300 |

See a demo

Upvotes: 1

Related Questions