Raised This Month: $12 Target: $400
 3% 

SQL - Updating ID's efficiently


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
xice1337
AlliedModders Donor
Join Date: Dec 2015
Old 08-21-2021 , 11:34   SQL - Updating ID's efficiently
Reply With Quote #1

Hello,
i have a table where i want to fill in numbers for the ID starting from 1 then 2 and so on.
The table is ordered by time so the most recent time should get number 1.

There is a query which works on phpmyadmin but not directly in sourcemod:
SET @i:=0; UPDATE mytable SET ID = @i:=(@i+1) ORDER BY time DESC;

The table before
HTML Code:
mytable
ID                  time
null                1629156967
null                1628735917
null                1628513592
How the result should be:
HTML Code:
mytable
ID                  time
1                   1629156967
2                   1628735917
3                   1628513592
Is there a good way to do this? I dont really wanna loop over every row to achieve this.
xice1337 is offline
DarkDeviL
SourceMod Moderator
Join Date: Apr 2012
Old 08-21-2021 , 15:40   Re: SQL - Updating ID's efficiently
Reply With Quote #2

Can you elaborate on what exactly this is for, and what your final goal will be?

Quote:
Originally Posted by xice1337 View Post
The table is ordered by time so the most recent time should get number 1.
This one sounds like you want it dynamically, - similar to a ranking system?


If I'm the newest (highest number) right now, according to the Unix timestamp / EPOCH field "time", then I should be #1?

If then you're the newest in five minutes, then you need to be first, and I need to be #2 ( ... or even a later number )?


Quote:
Originally Posted by xice1337 View Post
Is there a good way to do this? I dont really wanna loop over every row to achieve this.
If you really insist on getting you database updated accordingly, in the "dynamic" style as explained above, you will need to "loop over every row" every time it needs an update.


However, if you're solely looking for the current rank (position) of the actual result, at the time of a SELECT query, you can use e.g.:

Code:
SET @position=0; SELECT (@position:=@position+1) AS _position, time FROM mytable ORDER BY time DESC;
to accomplish what you want.
__________________
Mostly known as "DarkDeviL".

Dropbox FastDL: Public folder will no longer work after March 15, 2017!
For more info, see the [SRCDS Thread], or the [HLDS Thread].
DarkDeviL is offline
xice1337
AlliedModders Donor
Join Date: Dec 2015
Old 08-23-2021 , 10:14   Re: SQL - Updating ID's efficiently
Reply With Quote #3

Quote:
Originally Posted by DarkDeviL View Post
This one sounds like you want it dynamically, - similar to a ranking system?
That is correct.
My workaround for that is now that i made a http request via SteamWorks to my apache server which handles this task.
xice1337 is offline
Austin
Senior Member
Join Date: Oct 2005
Old 09-04-2021 , 07:20   Re: SQL - Updating ID's efficiently
Reply With Quote #4

You can create your ID column in the db as autoincrement.
Then when you do your insert you just insert the time column and the DB automaticially handles the ID column.

For SQLite see this.
https://www.tutorialspoint.com/sqlit...oincrement.htm
Austin is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 10:26.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode