Databaser & SQLur Databashanterare & SQL
4 svar · 440 visningar · startad av greyhound
DBMS: Mysql 5.0.34
Jag skulle vilja sortera så Group by tar det högsta PS_Upload, alltså det nyaste värdet.
Just nu väljer den det första som är postat av varje användare, det finns flera poster från samma användare men olika tidpunkter som datan är sparad.
Jag skulle vilja sorta innan jag gruperar typ det skulle funka, men jag har testat med lite olika Order by men det hjälper inte.
SELECT WO_Players.P_Username AS Username, WO_PlayerSkills.PS_Value AS Value
FROM WO_PlayerSkills
INNER JOIN WO_Players ON WO_PlayerSkills.PS_Player = WO_Players.P_Id
WHERE PS_Skill = 89
GROUP BY PS_Player
ORDER BY PS_Value DESC
LIMIT 25
Lite luddigt, men jag försöker
SELECT WO_Players.P_Username AS Username, WO_PlayerSkills.PS_Value AS Value
FROM WO_PlayerSkills
INNER JOIN WO_Players ON WO_PlayerSkills.PS_Player = WO_Players.P_Id
WHERE PS_Skill = 89
GROUP BY WO_Players.P_Username, WO_PlayerSkills.PS_Value
ORDER BY MAX(PS_Value) DESC
LIMIT 25
Funkar inte, jag var inne på mysqls IRC kanal. Där sa de att det inte gick att lösa utan en subselect.
Tabeller:
CREATE TABLE `WO_Players` (
`P_Id` int(10) NOT NULL auto_increment,
`P_Date` int(10) NOT NULL,
`P_Username` varchar(255) NOT NULL,
`P_Religion` int(2) NOT NULL,
`P_Server` int(2) NOT NULL,
`P_Password` varchar(255) NOT NULL,
PRIMARY KEY (`P_Id`),
UNIQUE KEY `P_Username` (`P_Username`)
)
CREATE TABLE `WO_PlayerSkills` (
`PS_Id` int(10) NOT NULL auto_increment,
`PS_Player` int(10) NOT NULL,
`PS_Skill` int(10) NOT NULL,
`PS_Value` float NOT NULL,
`PS_Upload` int(10) NOT NULL,
PRIMARY KEY (`PS_Id`)
)
PS_Upload är datumet när värdet laddades upp, jag vill bara ha det sista värdet för varje PS_Player
Löste det äntligen.
SELECT DISTINCT PS_Player AS Player, (SELECT PS_Value FROM WO_PlayerSkills WHERE PS_Player = Player AND PS_Skill = 13 ORDER BY PS_Upload DESC LIMIT 1) AS Value, WO_Players.P_Username AS Username
FROM WO_PlayerSkills
INNER JOIN WO_Players ON WO_PlayerSkills.PS_Player = WO_Players.P_Id
WHERE PS_Skill = 13
ORDER BY Value DESC LIMIT 20