SQL Newest Insert

[Peve]

HI,

I have a table similar to this stored in a MySQL database. I have change the column names and data just to make it simpler.



What I want to do is select the newest row based on the Insert Time and keep each unique users so then I would end up with something like this.



Any help would be great as google is not being much help.

 

[Deanje]

Hi,

Off the top of my head, if I understand the question correctly:

SELECT Name, Highest Jump, MAX(Insert Time)
FROM table
GROUP BY Name, HIGHEST JUMP

Disclaimer: It may be wrong, but fiddle with that sort of thing.

 

[brainchylde]

select [name], max(insertdate), jump from table as a

where insertdate = 

(select max(i.insertdate) 
from table i 
where i.name = a.name)

group by [name],jump

Bit messy but that's how you do it

 

[Peve]

select [name], max(insertdate), jump from table as a

where insertdate = 

(select max(i.insertdate) 
from table i 
where i.name = a.name)

group by [name],jump

Bit messy but that's how you do it

Works like a dream thanks.

 

[Confused]

Thank you also, I was trying to work out how I could get my online shop to dynamically display my 4 most recent additions to the catalogue in a gallery on the front page. I hadn't thought to timestamp each listing!

Or, if your products each have a unique, auto-ascending ID - just take the 4 highest numbers

But, having a "Product added on:" bit might look quite nice everywhere else, too

 

[Whitman]

You don't have to use MAX() just to get the latest. You could just SELECT * FROM table ORDER BY date DESC LIMIT 4;