GROUP BY och subselects/subqueries

Har en tabell som ser ut så här:

NAME, ID, SCORE, ONLINE

kalle_a, 1, 1400, 235
kalle_b, 1, 2400, 935

pelle_a, 2, 1300, 500
pelle_b, 2, 1200, 764
pelle_c, 2, 1100, 1026

nicklas_a, 3, 1000, 350
nicklas_b, 3, 400, 1200

Nu vill jag ha ut alla personers snittvärde på SCORE där ONLINE är störst. Exempel:

SELECT name, id, AVG(score) AS avg_score, SUM(online) FROM players GROUP BY id ORDER BY avg_score DESC

Ovanstående funkar, men namnet som kommer tillbaks är inte personens namn med högst ONLINE. Om ni förstår vad jag menar.

Ja, jag har MySQL 4.1

SELECT name, id, score , online
 FROM players p
where score = (
select max(score) 
 from players
where id = p.id)

Vet ej om jag har förstått riktigt. Förstår inte vad det är för skillnad på kalle_a och kalle_b. Är det samma spelare?

Japp, det är samma spelare fast olika alias. Jag vill alltså ha ut en persons alias som använts mest och ha ut ett snitt på hans score.

Det jag vill ha tillbaka blir alltså:

name (där online är max), id, avg(score), sum(online)

kalle_b, 1, 1900, 1170
pelle_c, 2, 1200, 2290
nicklas_b, 3, 700, 1550

Med andra ord vill jag kunna bestämma vilka fält som väljs ut i en GROUP BY...

SELECT name, id, (select avg(score) from players
where id = p.id) as avg_score
 , online
 FROM players p
where online = (
select max(online) 
 from players
where id = p.id)
order by avg_score desc

Nice, det fungerar. Du är kung :)
När vi ändå håller på, jag har en fråga till...

Tabellen ser ut så här:
playerid, playername, date

Som innan så kan det finnas flera instanser av samma spelare, fast "playerid" är alltid densamma. Hur ska jag nu kunna göra följande: "Ta bort en spelare där date är en vecka gammal."

Nu kan det vara så att en spelare har ett alias som är en vecka gammal, men då ska inget tas bort. Det ska bara tas bort ifall alla alias är en vecka gammal... om ni förstår :OO Detta hade jag helst velat lösa utan subqueries, denna databas ligger på en annan server med en äldre version av MySQL.

För att göra det lite enklare hade jag velat att denna query skulle funka:

select *, max(date) as lastplayed from war3users where lastplayed <= now() - interval 7 day group by playerid

Men det går ju icke, för man kan inte använda aliaset "lastplayed" i where-satsen.

Det går inte att få till som en fråga utan subselect.

delete from t as q
where not exists
(select * from t
where id = q.id
and date > current_date - interval 7 day)

Du kan få fram alla id med

select id from t
group by id
having max(date) < current_date - interval 7 day

och sedan kan man göra en delete där man anger alla id med en inklausul.

Hm, går det inte skriva:

delete from t
group by id
having max(date) < current_date - interval 7 day

:) Vad mysql ska vara omständigt...

Nope.

Ursäkta mitt okunnande, men hur ska jag kunna göra det som du skrivit ovan i en batch-fil (utan php alltså) ?

Utan någon form av programmering så går det inte. Varför kan du inte använda PHP i din batch?

Har inte php installerat på den servern. MySQL används av en CS-server.

Går det inte ens fixa med så kallade temptables?

Jo, det har du rätt i.

create temporary table ttid int)

insert into tt 
select id from t
group by id
having max(date) < current_date - interval 7 day

delete from t using tt where t.id = tt.id

drop table tt

Med reservation för att det är otestat, off course.

Syntaxen med flera tabeller i delete är ju specifik för Mysql och kräver 4.0.2 har jag för mig.

Tackar och bugar för all hjälp :birp