How to get the number of users with less time in each clan?

Question

How to get the number of users with less time in each clan?

Navigation

#1 by (1 votes)

1

I have 2 tables:

CREATE TABLE 'deathrun_records' (
    'sid64' STRING,
    'mapname'   STRING,
    'seconds'   REAL
);

CREATE TABLE 'clan_members' (
    'sid64' STRING,
    'nome_clan' STRING,
    'prop'  STRING
);

The first table saves the users 'time on each map, and the second saves the users' clan.

I need a SELECT to return the clans with users who have more records (shorter time on a map).

What I've done so far:

SELECT COUNT(*) AS recordes FROM deathrun_records d JOIN clan_members c ON c.sid64 = d.sid64 GROUP BY c.nome_clan ORDER BY recordes

My problem is to create a WHERE CLAUSE to catch only the player with the shortest time on each map. How to do?

sqlite

asked by anonymous 08.12.2017 / 12:53

1 answer

Project Standards: Singleton Use the laravel diff in two collections

score 1 · Accepted Answer

First you need to make a query by grouping the shortest time per clan, and then an outside query to pick up who has the shortest time of that clan, so the query would look like this:

SELECT
    d2.*,
    c2.*
FROM deathrun_records d2
INNER JOIN clan_members c2
ON c2.sid64 = d2.sid64
INNER JOIN(
SELECT 
    d.mapname,
    d.sid64,
    d.seconds,
    c.nome_clan,
    MIN(d.seconds) seconds_join
FROM deathrun_records d 
JOIN clan_members c 
ON c.sid64 = d.sid64 
GROUP BY c.nome_clan
ORDER BY nome_clan, seconds DESC) as aux
ON (aux.nome_clan = c2.nome_clan 
    AND seconds_join = d2.seconds 
)

So you can count the result of query grouping by d2.seconds and c2.nome_clan so you will have as many players per clan as possible, so we are returning users with shorter clan time, you want to see by global time only remove from the GROUP BY of the first query the field nome_clan .

In the query I made above you will have the players with the shortest CLAN time in that I have done you qtd players per clan has the shortest GLOBAL time,

SELECT
    COUNT(*) AS qtd_player,
    c2.nome_clan
FROM deathrun_records d2
INNER JOIN clan_members c2
ON c2.sid64 = d2.sid64
INNER JOIN(
SELECT 
    d.mapname,
    d.sid64,
    d.seconds,
    c.nome_clan,
    MIN(d.seconds) seconds_join
FROM deathrun_records d 
JOIN clan_members c 
ON c.sid64 = d.sid64 
ORDER BY nome_clan, seconds DESC) as aux
ON seconds_join = d2.seconds 
GROUP BY nome_clan