SQL Query Question

[gstelmack]

MySQL is the database in question.

select p.firstname,p.nickname,p.lastname,s.team,sum(s.passyards)
from fof_playergamestats s, fof_playerhistorical p 
where s.passyards>0 and p.id=s.playerid
order by sum(s.passyards) desc
group by s.playerid
limit 10

Works great for getting a list of the top 10 passers (by yards) in a league. But I'd like the returned team column to be smarter. How can I change the "s.team" in the select to be "the s.team that appears the most in the grouped rows"?

[MikeVic]

Don't you need another query? Or use MAX somehow?

[Fidatelo]

I'm assuming you are trying to list the team that the player in question played the most games for? I think there might be a way to do this all in one query, but I'd use two for simplicity's sake.

[cuervo72]

I don't know how to do that using a group command. I found this doing a search, but I haven't examined it closely enough to see if it would do what you need (and it doesn't look trivial). If you just ask for a 'team' field, it could return anything (and could even vary per query).

~jk groupwise max

[lordscarlet]

If the assumption above is correct, and you want players, ordered by total passing yards, with the team they played the most games for, then a quick and dirty query would be:

select p.firstname,
  p.nickname,
  p.lastname,
  (select 
   top 1 
     team 
   from 
     fof_playergamestats 
     where playerid = p.id 
     order by count(team) 
     group by team)
  ,sum(s.passyards)   
  from fof_playergamestats s, fof_playerhistorical p 
where 
  s.passyards>0 and p.id=s.playerid
order by 
  sum(s.passyards) desc
group 
  by s.playerid
limit 10

...but let me think about a better way to do it.

[gstelmack]

I tried something similar last night with the subquery (I think I did limit 1 instead of top 1, which maybe would help), but it took FOREVER on the page I'm working on, which is executing 12 of those queries for various stats (and I'm adding more). For now I'm just not displaying the team, but if there is an efficient way to get it, I'll add it back.

[lordscarlet]

Originally Posted by gstelmack

I tried something similar last night with the subquery (I think I did limit 1 instead of top 1, which maybe would help), but it took FOREVER on the page I'm working on, which is executing 12 of those queries for various stats (and I'm adding more). For now I'm just not displaying the team, but if there is an efficient way to get it, I'll add it back.

Could you IM me and see if I can get some sample data to play with? I'm too lazy to make dummy data.

[lordscarlet]

..hmm.. The added problem is I am working in SQL Server which does not allow you to have an item in the select list that is not in the group by clause.

[lordscarlet]

Triple dola:

What exactly is the data you are trying to retrieve?

[cuervo72]

Originally Posted by gstelmack

I tried something similar last night with the subquery (I think I did limit 1 instead of top 1, which maybe would help), but it took FOREVER on the page I'm working on, which is executing 12 of those queries for various stats (and I'm adding more). For now I'm just not displaying the team, but if there is an efficient way to get it, I'll add it back.

For career stats I just didn't bother. I have the team on the yearly leader page (much greater chance that just throwing team in there will hit the right one, as you don't have that many changes in-season for real impact guys)...but interestingly don't for the single season records page (I have to include year there, so it could have been a space issue).

[gstelmack]

Originally Posted by cuervo72

For career stats I just didn't bother. I have the team on the yearly leader page (much greater chance that just throwing team in there will hit the right one, as you don't have that many changes in-season for real impact guys)...but interestingly don't for the single season records page (I have to include year there, so it could have been a space issue).

For the moment, I'm not either. But the season page looks so much better...

[cuervo72]

Yep, little team images ftw.

(Oh ha ha look your league has a J.B. "Magic" how uproariously funny and/or original!)

[gstelmack]

Nicknames in FOF are user-entered...

[lordscarlet]

The good news? I made a query that will work.

The bad news? It appears to be incredibly slow on this data. It is very fast on my dummy data, though.

Maybe it will give you some ideas though?

select	
		p.firstname,
		p.lastname,
		p.nickname,
		sum(s.passyards) as total_passyards,
		s.playerid, 
		s.team, 
		count(s.playerid) as games_played, 
		max_games_played
from fof_playergamestats s
join 
	(select playerid, max(games_played) as max_games_played
	from (
		select s.playerid, s.team, count(s.playerid) as games_played
		from fof_playergamestats s 
		group by s.playerid, s.team
	) t 
	group by playerid
	) t on t.playerid = s.playerid 
join fof_playerhistorical p on p.id = s.playerid
group by s.playerid, s.team, max_games_played, p.firstname, p.lastname, p.nickname
having count(s.playerid) = max_games_played
order by sum(s.passyards) desc
limit 10

[cuervo72]

Originally Posted by gstelmack

Nicknames in FOF are user-entered...

Yeah...that was a general comment on FOF users that are fond of nicknames.

[Fidatelo]

I might not have a good handle on the restrictions you face within your environment (mysql + whatever else), but I still don't see why you don't just use 2 queries.

First query is exactly as written in the first post, except don't bother returning the s.team at all, do bother to return the playerid, and place the results in a table variable (or temp table or whatever mysql equivalent is or into a recordset in your code or whatever is calling this query).

Second query does something like:

select top 1 count(s.team) as counter, s.playerid, s.team, q1.firstname,q1.nickname,q1.lastname,q1.passyards
from fof_playergamestats s
inner join query1results q1 on q1.playerid = s.playerid
group by s.playerid, s.team, q1.firstname,q1.nickname,q1.lastname,q1.passyards
order by counter desc

I think you could then just show all the columns from the second query except the 'counter' column.

[Fidatelo]

Dola

I think this should perform quite well, because you are just joining an indexed key to a 10 row temp table.

[lordscarlet]

What version of mysql is it? If it is lower than 5, there are no stored procedures. I can say for sure that the phpmyadmin interface to it does not have stored procedures. If that is the case, I'm not sure how a temp table (presuming mysql has them?) could be used.

[Fidatelo]

No stored procs? Good grief. Then my advice is to get a real database

[lordscarlet]

Not until mysql 5. I do not know what easycgi has.

According to their site, it is 5.0.7. So you may just need to dig deeper than using phpmyadmin.

[gstelmack]

I usually use the MySQL Query Browser from home. I'm only on phpmyadmin at the office. And yes, it's MySQL 5, we upgraded it a while back.

[Fritz]

I think even with a stored procedure that query looks to be an execution hog.

try running part of it as an insert into a temp table, and then query that.

In fact, you may be able to make a few slim temp tables, then execute your queries for the various stats, and then drop your tables.

Temporary Tables With MySQL

[gstelmack]

DELIMITER $$
DROP PROCEDURE IF EXISTS `wooftest`.`GetTopPassersForSeason` $$
CREATE PROCEDURE `wooftest`.`GetTopPassersForSeason` ()
BEGIN
create temporary table tmp_players (player_id int primary key, fname varchar(128), nname varchar(128), lname varchar(128), yards long);
insert into tmp_players (player_id,fname,nname,lname,yards)
    select p.id,p.firstname,p.nickname,p.lastname,sum(s.passyards)
      from fof_playergamestats s, fof_playerhistorical p
      where s.passyards>0 and p.id=s.playerid
      order by sum(s.passyards) desc
      group by s.playerid
      limit 10;
select top 1 count(s.team) as counter, s.playerid, s.team, t.fname,t.nname,t.lname,t.yards
   from fof_playergamestats s
   inner join tmp_players t on t.playerid = s.playerid
   group by s.playerid
   order by counter desc;
END $$
DELIMITER ;

That gets me a syntax error on the insert into ... select statement, and I can't see why. But that seems to be the approach to take. Of course it means one of these for every stat I want to run on...

[cuervo72]

Could be possible that temp tables aren't allowed on the server (rather than a mysql limitation). I looked into that a while ago for something but ran into obstacles, just can't remember what.

[Fritz]

So, create a regular table and drop it. that should be legal.

[lordscarlet]

Originally Posted by gstelmack

DELIMITER $$
DROP PROCEDURE IF EXISTS `wooftest`.`GetTopPassersForSeason` $$
CREATE PROCEDURE `wooftest`.`GetTopPassersForSeason` ()
BEGIN
create temporary table tmp_players (player_id int primary key, fname varchar(128), nname varchar(128), lname varchar(128), yards long);
insert into tmp_players (player_id,fname,nname,lname,yards)
    select p.id,p.firstname,p.nickname,p.lastname,sum(s.passyards)
      from fof_playergamestats s, fof_playerhistorical p
      where s.passyards>0 and p.id=s.playerid
      order by sum(s.passyards) desc
      group by s.playerid
      limit 10;
select top 1 count(s.team) as counter, s.playerid, s.team, t.fname,t.nname,t.lname,t.yards
   from fof_playergamestats s
   inner join tmp_players t on t.playerid = s.playerid
   group by s.playerid
   order by counter desc;
END $$
DELIMITER ;

That gets me a syntax error on the insert into ... select statement, and I can't see why. But that seems to be the approach to take. Of course it means one of these for every stat I want to run on...

One for each stat is ok.. everything should be in stored procedures, in theory, anyway. (Although there will always be people that argue the other way)

[Fighter of Foo]

OK I'm stuck...

I've got two tables:

Marketing: Campaign ID, Item ID, User ID
Detail: Campaign ID, Item ID, Date

I need to join them using both the Campaign ID AND Item ID.

[lordscarlet]

SELECT * FROM Marketing m JOIN DETAIL d on m.campaign_id = d.campaign_id and m.item_id = d.item_id

[Fighter of Foo]

Thanks. Apologies for being slow.

[lordscarlet]

And, really, you "should never" use "SELECT *", but it was easiest for the example.

[fantom1979]

Originally Posted by lordscarlet

And, really, you "should never" use "SELECT *", but it was easiest for the example.

Sorry, I am pretty new with php/sql. Why do you not use Select *? Does it slow things down?

[lordscarlet]

Originally Posted by fantom1979

Sorry, I am pretty new with php/sql. Why do you not use Select *? Does it slow things down?

Yep. Especially if you have a text field or blob or something. It can also be a problem if you have joins and there are overlapping field names that do not represent the same data ("Name" for instance). Its generally a good idea to explicitly name the columns.

In addition, if you're referencing columns by integers rather than names (which is probably a bad idea anyway) it could definitely cause problems. But, primarily, it has the potential to slow things down.

edit: if you have a text, blob, or other large field that you do not want to pull into your dataset

[fantom1979]

thanks

[Fidatelo]

Originally Posted by lordscarlet

Yep. Especially if you have a text field or blob or something. It can also be a problem if you have joins and there are overlapping field names that do not represent the same data ("Name" for instance). Its generally a good idea to explicitly name the columns.

In addition, if you're referencing columns by integers rather than names (which is probably a bad idea anyway) it could definitely cause problems. But, primarily, it has the potential to slow things down.

edit: if you have a text, blob, or other large field that you do not want to pull into your dataset

You allude to this with the 'referencing columns by integers', but I'd argue the biggest reason you don't do select * is not performance, but maintainability. Nothing more annoying than adding a column into a table and breaking a dozen chunks of code that are using a select *.

Of course, then there is the school of thought that you never actually query a table, only a view... but since I don't agree with that one I just won't go there

[lordscarlet]

Originally Posted by Fidatelo

You allude to this with the 'referencing columns by integers', but I'd argue the biggest reason you don't do select * is not performance, but maintainability. Nothing more annoying than adding a column into a table and breaking a dozen chunks of code that are using a select *.

Of course, then there is the school of thought that you never actually query a table, only a view... but since I don't agree with that one I just won't go there

Exactly. There are several reasons, but they pretty much boil down to:

1) Performance
2) Knowing exactly what you are pulling down at all times