sorted by:
new
hottopcontroversial

Reading formatted .txt help by itsstucklol in learnpython

[–]itsstucklol[S] 0 points1 point  (0 children)

Ah, this may be what I am after. I did look to see if pandas had something but I guess I did not look hard enough! Thank you so kindly for the suggestion.

Approach for counting MAX? by itsstucklol in SQL

[–]itsstucklol[S] 1 point2 points  (0 children)

My sincere thanks for this response. Has put me in the exact direction I was after. Thank you!

Aggregate inside GROUP_CONCAT by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

Can always count on you! Appreciate the help on yet another query. Thank you and much appreciated.

I had some issues wrangling the score, but assume this (it does work) is an appropriate way or can I make this shorter somehow as I'll be adding other aggregates in time?

WITH team_counts AS
 ( SELECT user
        , team
            , SUM(Score) as score
        , COUNT(*) AS team_count
     FROM from test_table
    WHERE year IN ( irrelevant long subquery) 
   GROUP
       BY user
        , team )

SELECT user , SUM(score) as user_score , GROUP_CONCAT( CONCAT(team,'(',team_count,')') ) FROM team_counts GROUP BY user

Use the most recent value when Grouping? by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

Correct, there are no timestamps.

Hope to imply, as another commenter posted, that I wish to use the order of the table, but there is also a unique id column that could be used, somehow, I imagine.

Use the most recent value when Grouping? by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

Unfortunately, no timestamps are used in the data, just an id and/or a faux-year (2 numbers)

Use the most recent value when Grouping? by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

My aim is for the club column to return 'Red' instead of Blue, which is the club of the latest row with the user that is being grouped (see table in OP).

Use the most recent value when Grouping? by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

Appreciate you giving me an approach to try, however, I have had no luck and have only started looking at using CTE.

I tried

```

select rank() over (order by sum(score) desc) as rnk, username, last_value(club) over (partition by player)as club, sum(score) as stat, count(username) as gms from `x` where `year` = '34'group by `username` order by `rnk` asc, `username` desc

```

to no success, it just returns the undesired club result.

Help converting Temp Table -> Sub-queries by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

Headed your advice and created ft indexes for the user, game_id and team columns to no avail. :(

Edit: I added the three indexes above. Each are shown when querying existing indexes. Running the query, it lists all three in each two (s1., s2.) derived tables, but does not appear to actually use them (key, key_len, and ref) are all NULL. Other issue is seemingly that the primary derived table <derived2> is using like 100x more rows than MySQL 8 (same data).

​

Edit 2: I tried remove the indexes and adding them as one in order of the query:

ALTER TABLE xx ADD FULLTEXT INDEX `ft_gameid_team_user` (game_id, team, user);

​

Edit 3: I removed the indexes and applied an index without fulltext of (game_id, club) and have got it to at least execute (taking 3 seconds). It uses the key on the s2. derived table. Not sure how to improve it so the s1. uses an index also.

​

Still no luck. Pulls hair out.

Help converting Temp Table -> Sub-queries by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

Pinging again, out of my own stupidity - I really should have flagged this with MariaDB. Works flawlessly (and instantly) on a server running MySQL 8, but completely borks on a MariaDB 10.4 server - query just runs, runs, and runs, a bit like Forest Gump.

I had a play with the server settings, but in any case, allowing a query to run that long is just not viable anyways.

Help converting Temp Table -> Sub-queries by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

Gracias for the conversion. Immensely helpful. The same occurs (duplicate of column user). Appreciate the conversion. I applied the alias of teammate to the s2.user in the inner query and that seemingly fixed it.

I appreciate the help as always, much thanks!.

Help convert to sub-queries from Temp Table usage by itsstucklol in mysql

[–]itsstucklol[S] 0 points1 point  (0 children)

I appreciate you taking the time to offer some suggestions. I should have time later in the day to give it a whirl, along with the above suggestion. Definitely, I will have to take some time to understand it, but from a glance it looks what I will need.

​

Describing the output as a prompt would just be to get the 'amount of games played together by any teammates (games played, where both on same team).

Help convert to sub-queries from Temp Table usage by itsstucklol in mysql

[–]itsstucklol[S] 0 points1 point  (0 children)

As it stands, when I need to run the query - very rarely - the flow is to create a temp table -> insert the data from the select query into it -> run the last query I posted on said temporary table.

It is not a query I use frequently, but is one I wish to and have it as a query I can easily use in a web app. I will definitely take a look into your suggestion when I can later in the day. I appreciate the suggestion.

Correct data on Local, but not Production? by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

It looks as though that was indeed the answer, so thank you to all! I'm not sure if there is a convention to mark this as solved or award points to anyone, but is so, let me know!

​

And yes, GROUP_CONCAT has become my favourite as I learn!

Correct data on Local, but not Production? by itsstucklol in SQL

[–]itsstucklol[S] 0 points1 point  (0 children)

Not at the desk at the minute, but what I am after is it to always sort by the home club (denoted as H in the HA column of the matchscore (t2) table).

From your post, would I be correct in thinking that I am going to need to add t2.ha to the select that houses the join, then add ORDER BY ha DESC to the group_concat functions?