CODEWITHSUNDEEP
phpmysqlsqlresultset
Justin
Justin

Reputation: 2530

Select the latest MySQL Data but unique resort

I have a 1 table database that has a list of advertisements. I am trying to grab the LATEST advertisement for EACH resort. I know it should probably be a table database, but how would I go about doing so.

enter image description here

Upvotes: 2

Views: 243

Answers (4)

Adrian Iftode
Adrian Iftode

Reputation: 15663

select t.*
from YourTable t
join
    (select resort, max(dateAdded) dateAdded
    from YourTable
    group by resort) m on t.dateAdded = m.dateAdded and t.resort = m.resort
order by t.resort

First group the rows by resort to find the max of dateAdded, then query (join) the rows that have the same dateAdded with the max.
One problem is, if the same resort b is added in the same time, twice or more, it will take only the first row. But I think is slightly possible.

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838416

Assuming the Id column is unique:

SELECT T3.*
FROM yourtable AS T3
JOIN
(
    SELECT T2.resort, T2.date_added, MAX(T2.id) AS id
    FROM yourtable AS T2
    JOIN
    (
        SELECT resort, MAX(date_added) AS date_added
        FROM yourtable
        GROUP BY resort
    ) AS T1
    ON T1.resort = T2.resort AND T1.date_added = T2.date_added
    GROUP BY T2.resort, T2.date_added
) AS T4
ON T4.id = T3.id

Upvotes: 3

endyourif
endyourif

Reputation: 2202

I think this should do it:

SELECT * FROM advertisements GROUP BY resort ORDER BY dateAdded DESC

Upvotes: 1

Peter
Peter

Reputation: 16933

Use GROUP BY function

SELECT MAX(dateAdded), resort, linkToUrl FROM `your_table` GROUP BY resort

Upvotes: 1

Related Questions