DELETE from table

[The Little Guy]

How can I delete from a table when their is more than 10 of the same IP addresses, and when the 11th is inserted, it deletes the oldest row for that IP address.

 

I have a column called DATE which is datetime type.

[Vikas Jayna]

I'm assuming that you're using mysql. Here's how it can be done:-

 

delete from TABLENAME where IP='111.111.111.111' order by DATE asc limit 1

[The Little Guy]

That doesn't sound correct.

 

I am trying to make a last viewed items list, that will display your IP's last 10 viewed items. Once the 11th item is added, the oldest item of the ten items is removed, and the second oldest item takes its place, and the 11th item now becomes the 10th item.

 

Yours sounds like it will only allow 1 item then remove it. Yes I am using MySQL (thats why its in the MySQL board).

[Yesideez]

Why worry about deleting anything past the last 10 IPs?

 

You can keep them all and use something like this to view only the 10 newest:

SELECT * FROM ip_list ORDER BY ip_id DESC LIMIT 10

At least that way if you do need to check for duplicate IPs or anything else then the data is there, it's just that its not being used.

[The Little Guy]

I guess thats not a bad Idea, I could place a date in the row, and just update it when they view the product again.

[fenway]

There are actually some very fancy way of having "last 10" rolling groups with mysql, but they're fairly exotic... if you care, which you probably shouldn't, I can post the relevant links.