SQL Help needed! - Printable Version

SQL Help needed! - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: SQL Help needed! (/showthread.php?tid=625294)

SQL Help needed! - RedFusion - 30.12.2016

Hello!

I'd really appreciate some help from someone with experience in MySQL (multithreaded)

What i want to accomplish is selecting deaths from a table within a datetime range, and then sort them by amount of kills by each user ID. (it's for a leaderboard)

Let's call the column names like this:

pawn Код:

deathid // auto incrementing index
deaduserid // userid of dead player
killuserid // userid of killer player

How would one fix this in an efficient way?

Pointing me in the right direction would be appreciated, but if you can provide a long answer i'd appreciate it even more ofcourse.

Re: SQL Help needed! - iLearner - 30.12.2016

Quote:

selecting deaths from a table within a datetime range, and then sort them by amount of kills by each user ID

I am a bit confused on this.

Re: SQL Help needed! - RedFusion - 30.12.2016

I think i explained it poorly.

Basically, whenever a player dies a row gets inserted to this table.

The userid of the killed player, killer player and an auto incremented ID is saved to the table.

Let's call the table "deaths".

What i would like to do, is to find out who killed the most players during a certain month.

Re: SQL Help needed! - BiosMarcel - 30.12.2016

Here is the table structure you'd need:

PHP код:



CREATE TABLE deaths (ID INT AUTO_INCREMENT, DeathID INT NON NULL, KillerID INT NON NULL, TimeOfDeath TIMESTAMP DEFAULT CURDATE());

This will set the TimeOfDeath to the actual Date & Time as soon as you ceate a new tupel (row).

So, next lets go over to the Select Statements

PHP код:



SELECT DeathID, KillerID, TimeOfDeath, COUNT(KillerID) AS Kills FROM deaths WHERE MONTH(TimeOfDeath)=!!!INSERT MONTH HERE!!! GROUP BY KillerID ORDER BY Kills DESC

So, if i didn't do anything wrong, this should give you all the kills within 1 Month grouped by the killers id and ordered by kills

Re: SQL Help needed! - Vince - 30.12.2016

Selecting rows within a range is simple enough, but if you're not storing time and date with each kill then you don't have anything to apply that range to.

You can count kills for each userid, however:

PHP код:



select killuserid, count(*) as numkills from kills group by killuserid order by numkills desc limit 10

Re: SQL Help needed! - RedFusion - 30.12.2016

Oh it's that simple?
I've tried some different queries now and it's working perfectly.
Thanks for the help!!