CODEWITHSUNDEEP
phpmysqldatabase
user902633
user902633

Reputation: 75

Cannot select latest and not same data from mysql

I have value like this.

id  website   time
1   aaa.com   11.10
2   bbb.com   11.10
3   ccc.com   11.10
4   aaa.com   11.11
5   bbb.com   11.11
6   ccc.com   11.11

I want to select latest and not same data in table. It should be .

4   aaa.com   11.11
5   bbb.com   11.11
6   ccc.com   11.11

I query like this

"SELECT * FROM name GROUP BY website ORDER BY id DESC";

It alway show DESC and ASC.

1   aaa.com   11.10
2   bbb.com   11.10
3   ccc.com   11.10

How to select latest value with not same data ?

Upvotes: 0

Views: 46

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

Join to a subquery which finds the latest record for each website:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT website, MAX(time) AS max_time
    FROM yourTable
    GROUP BY website
) t2
    ON t1.website = t2.website AND
       t1.time = t2.max_time;

You are storing your times apparently in some text format. This isn't desirable, and it would be better to store date times instead. If the width of the time is always the same (5 telling from your sample data), then my query should work though.

Upvotes: 2

Related Questions