Re^2: UPDATE ON DUPLICATE and sorting

I know I can't do an update on duplicate with an auto increment, that was the main question. I am using the auto id to get the last 10. I would change the primary key to "url" because that's what I need to check duplicates for.

The thing is, I believe I tried to change the primary key to "url" already today with the auto increment. It errored out saying I can't have that and a primary key (or some error like that). So I think an auto_increment has to be the primary key.

Comment on Re^2: UPDATE ON DUPLICATE and sorting

Replies are listed 'Best First'.
Re^3: UPDATE ON DUPLICATE and sorting by graff (Chancellor) on Feb 06, 2006 at 05:14 UTC
Okay, it turns out that mysql does not allow you to declare an "auto_increment" field unless it's going to be the primary key. So don't use an auto_increment field. Use a date_time field instead (call it something like "create_date"), and whenever a new row is inserted, set its value with the "NOW()" function. Then you'll be able to "order by create_date desc limit 10". You don't need an auto_increment field -- you just need "url" to be the primary key, and you need something chronological to sort on. Update: About the 2nd part of your initial question: you could try to work out a two-stage query: find the "create_date" value for the row that is currently 10 rows "back" from the most recent row, then select all rows where create_date is greater than that value, and order by "Total". But I think it would be a lot easier to do a single query ordered chronologically, store those into an AoA or AoH and let the perl script sort them based on "Total".	[reply]

Replies are listed 'Best First'.

Re^3: UPDATE ON DUPLICATE and sorting
by graff (Chancellor) on Feb 06, 2006 at 05:14 UTC

So don't use an auto_increment field. Use a date_time field instead (call it something like "create_date"), and whenever a new row is inserted, set its value with the "NOW()" function. Then you'll be able to "order by create_date desc limit 10".

You don't need an auto_increment field -- you just need "url" to be the primary key, and you need something chronological to sort on.

Update: About the 2nd part of your initial question: you could try to work out a two-stage query: find the "create_date" value for the row that is currently 10 rows "back" from the most recent row, then select all rows where create_date is greater than that value, and order by "Total".

But I think it would be a lot easier to do a single query ordered chronologically, store those into an AoA or AoH and let the perl script sort them based on "Total".

[reply]