Talk:Useful SQL Queries

From Mysqlgame

Just added the most money/fuel by clique queries. I believe they are correct, but someone may want to check them.. --lolzj

"select vulnerable rows" pseudosql scribblings:

SELECT rows.row_id, money, fuel, attackers, AM, defenders, DM, rows.owner AS owner FROM players, rows WHERE (UNIX_TIMESTAMP(owner's total upgrades across all their rows converted into #days) < (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(players.last_logged_in)) ORDER BY rows.row_id

if (to_unix(total upgrades in days) < (unix(now()) - unix(last_log)) then vulnerable

if (small chunk of time) < (large chunk of time) then vulnerable

(owner as id)'s total upgrades:

SELECT player_id, SUM(value) value FROM (SELECT player_id, SUM(money_factories)+SUM(fuel_factories)+SUM(attack_multiplier)+SUM(defense_multiplier) value FROM rows, players WHERE owner = player_id GROUP BY player_id) value GROUP BY player_id

use INTERVAL or something?

I just added the "When will row ### be vulnerable?" query, but it could be improved. I've not done date calculations in SQL before, so I don't know how to add the number of idle days to the last login date. If someone could fix that, it would be more helpful to display the date on which the target row will be vulnerable. TheAmigo 16:40, 4 October 2008 (EDT)

I just added an enhanced version of the vulnerability query that provides the vulnerable date. I left Amigo's original query present until someone confirms it is working properly - my first attempt at calculation in SQL. Saiboogu 00:09, 5 October 2008 (EDT)

Thanks, I tried it on a few rows and it looks like it's working great. I removed my earlier query and left your modified version. TheAmigo 11:40, 5 October 2008 (EDT)

Here's a different take on what I was trying to achieve:

SELECT rows.row_id, players.last_logged_in FROM players INNER JOIN `rows` ON players.player_id = rows.owner ORDER BY players.last_logged_in

I'm trying to return a list of rows that will be vulnerable in 'sooner' to 'later' order, taking into account the number of upgrades they have. Ideally it would return a list like:

row_id | days | when

 50   | -28  | 9/9/2008
 10   |  1   | 10/7/2008
 1    |  10  | 10/16/2008

The problem is that my SQL-fu is terrible --218.215.12.85 02:09, 5 October 2008 (EDT)

I added a query to the bottom of the page that should accomplish what you are looking for. Only problem, it lists players rather than rows. It should be possible to include the rows, I'm just missing something here.. my SQL-fu is nearly as bad. Saiboogu 01:50, 6 October 2008 (EDT)

My SQL-fu is perhaps a bit stronger, I added row listing to it and added another query on the bottom Danalog 15:03, 7 October 2008 (EDT)

I fixed the SQL on the "List next 30 vulnerable players by vulnerability date" query. The extra subselect that was in there caused it to count player summery stats per row, thus effectively making it be (MF+FF+AM+DM)*num_rows. I thought something was up when removing the "limit 0,30" showed me as having >10K days until vulnerable. It now reports 707 witch is correct. The "...and their rows..." variant has the same problem, but I didn't change it because it has an additional problem: the "rows" output column not listing the rows, just "[BLOB - 4 B]" (when run in Tom's PhpMyAdmin Interface). TheAmigo 01:07, 12 October 2008 (EDT)

Ok, I fixed the "...and their rows..." query. It's apparently a quirk with MySQL that it only returns VARCHAR data when group_concat_max_len <= 512, hence the "SET ...;" at the beginning. TheAmigo 19:54, 12 October 2008 (EDT)

Thanks, it worked just fine on my local MySQL instance, I wasn't running it on Tom's interface, so I didn't know there was a problem. Danalog 17:13, 14 October 2008 (EDT)