SQL for Selecting/Deleting the First Row
Hi All,
I have a table in the database which I use for logging user activity (Database is MySQL 5.0.37, I can upgrade to higher version if needed). I have made a stored-procedure that inserts new row for a user activity. The new functionality I want to add to the stored procedure is ...
a. There can be atmost 20 rows of a particular user's activity.
b. Incase of inserting a new row, if the total rows are more or equal to 20, i delete the first row, the store a time-stamp of the activity as well (so I have the latest 20 activities in the database of a particular user).
I am a real newb on SQL. Could anyone please help me writing an SQL statement for the above mentioned points?
Thanks in Advance,
Regards,
Usman.
[776 byte] By [
usman999_1] at [2007-11-20 7:46:14]
# 1 Re: SQL for Selecting/Deleting the First Row
Simplest..
1) Select count()
2) Calculate n = count()-max
3) If greater than 0, select top "n" keys, and pass to DELETE.
# 2 Re: SQL for Selecting/Deleting the First Row
Thanks CPUWizard, Could you please sprinkle some SQL on your earlier post?
Thanks in Advance,
Regards,
Usman.
# 3 Re: SQL for Selecting/Deleting the First Row
Perhaps someone else could,. since you are using MySQL..I tend to use SQLServer specific constructs without even thinking...
# 4 Re: SQL for Selecting/Deleting the First Row
Hi all.
If I've understood correctly what you're trying doing, you want keep the newies user's activities.
Well.
I suppose that your table where you store logs (called LOG_ACTIVITIES for example) has the fields:
LOG_ID: key, generated by an autoincrement value.
USER_ID: ID user
TYPE_ACTIVITY_ID: type of activity
Then I suppose that your sored procedure has _user_id and _type_activity_id as parameters, where _user_id identifies the user and _type_activity_id identifies the type of activity.
As TheCPUWizard said:
1) Count the actual user's activities related to a particulare type:
SELECT COUNT(*)
FROM LOG_ACTIVITIES
WHERE USER_ID = _user_id
AND TYPE_ACTIVITY_ID = _type_activity_id
2) Check if the actual user's activities is greater than the max number.
3) Delete the oldiest activity
DELETE LOG_ACTIVITIES
WHERE LOG_ID = (SELECT MIN(LOG_ID)
FROM LOG_ACTIVITIES
WHERE USER_ID = _user_id
AND TYPE_ACTIVITY_ID = _type_activity_id)
I hope this will help you.
# 5 Re: SQL for Selecting/Deleting the First Row
Davide++,
Thanks for the sample. Assuming that the log has not previously exceed the desired number of entries, that will work nicely. I was going for one better where the delete would kill all of the entries that exceeded count [by using a TOP function on a select..]
# 6 Re: SQL for Selecting/Deleting the First Row
Hi TheCPUWizard.
Well, the basic problem is "find the first n records that...", but it seems that there isn't a general (and safe) way to do this using standard SQL (I hope that someone could deny this sentence).
You have to use specifing facilities offered by database you're using, such as "TOP n" for SQLServer or ROWNUM for Oracle, and so on. So if you want use standard SQL, you'll have to make further assumptions, for example that the table is "right" at the start, as you've been observing.
Another way, that complies with your requirement should be:
DELETE LOG_ACTIVITIES
WHERE LOG_ID <= (SELECT MAX(LOG_ID) - 20
FROM LOG_ACTIVITIES
WHERE USER_ID = _user_id
AND TYPE_ACTIVITY_ID = _type_activity_id)
This works fine if LOG_ID was increased according to USER_ID and TYPE_ACTIVITY_ID pairs (then the key should be LOG_ID - USER_ID - TYPE_ACTIVITY_ID) without gaps. As you can see, there're another assumption we have to do...
# 7 Re: SQL for Selecting/Deleting the First Row
Thanks for the clarification [once again proving I use SQLServer extensions without even thinking about it... :blush: ]
