CODEWITHSUNDEEP
mysqlsql
chetan
chetan

Reputation: 76

Query to select last record which has same column value(date)

table schema

id              date
----            -------
1020            1990-02-15
1020            1990-02-15
1060            1990-02-15
1020            1990-03-15
1020            1990-03-15
1020            1990-02-10

query to select last inserted record which has same date and where id = 1020 date format yyyy-mm-dd

Upvotes: 1

Views: 8611

Answers (5)

Badrus Sholeh
Badrus Sholeh

Reputation: 99

use max() and group your main column

select id, max(date) from tableName group by id;

Upvotes: 0

GautamD31
GautamD31

Reputation: 28763

Try with

SELECT * FROM schema WHERE id=1020 ORDERBY date DESC limit 1;

or choose max of the date you have like 

SELECT MAX(date) from schema WHERE id = 1020;

Upvotes: 0

John Woo
John Woo

Reputation: 263693

Since it's MySQL

SELECT * 
FROM table 
WHERE id=1020 
ORDER BY `date` desc 
LIMIT 1

but i still have a question for you, what if you have records like this?

1020            1/11/12
1020            1/12/12
1020            1/12/12
1020            1/13/12

what will it return?

In your case, the date is not currently formatted (i guess it was saved as VARCHAR) that is why you need to format it back to date using STR_TO_DATE function and from that display using your desired format.

SELECT DATE_FORMAT(STR_TO_DATE(`date`, '%m/%d/%y'), '%d-%m-%Y') lastDate
FROM table 
WHERE id = 1020 
ORDER BY `date` desc 
LIMIT 1

UPDATE 1

So you need the record which has the same date right? Try this edit one. For this, it created a subquery which returns the latest date and has the same date.

SELECT *
FROM tablename
WHERE id = 1020 AND
        `Date` = (
                    SELECT  MAX(`date`) maxDate
                    FROM    tableName
                    GROUP BY `date`
                    HAVING COUNT(`DATE`) > 1
                 )

Upvotes: 2

Pulkit Mittal
Pulkit Mittal

Reputation: 6076

Can this help?

select max(date) from table where id=1020;

Upvotes: 0

Icarus
Icarus

Reputation: 63956

By last record I assume you mean the one with the most-current date....

select * from table where id=1020 order by date desc limit 1

Upvotes: 2

Related Questions