[RESOLVED] GROUP BY and ORDER BY Problem

SirEvenhand

Hi ALL

I am having a problem with grouping and getting the results to order correctly.

SELECT t1.Ach_ID, t1.Ach_UserName, t1.Ach_TypeID, t2.AT_Type, t2.AT_Detail, t2.AT_Image FROM achievement AS t1, award_types AS t2 WHERE t1.Ach_TypeID = t2.AT_ID GROUP BY t1.Ach_UserName ORDER BY t1.Ach_Time ASC LIMIT 0 , 30

This query works except the order by gives me the first entry and does not give me the last insert.

I have read ways to sub query but have no idea how to do a sub when you have joined tables.

Does anyone know how to use sub queries with existing joined tables?

Thanks in advance for any help.

djjjozsi

do you really have a good database model here? Sub query is unnecessary here.

you have group by, but this relation is one - to - one. Group by is for to apply mysql functions like: SUM , COUNT , ect...

Be a little more specific, and post the real table
structure with example data.

One user can have more then one type? Then you may change the table model.

Select * From award_types t2 Right Join achievement t1 On t1.Ach_TypeID = t2.AT_ID Order By t1.Ach_Time Desc

this selects all achievement and all connected award_types where t1.Ach_TypeID has a value.

Weedpacket

SirEvenhand wrote:
This query works except the order by gives me the first entry and does not give me the last insert.

By that do you mean that you want the results sorted in descending order instead of ascending order?

SirEvenhand

Weedpacket;10938160 wrote:
By that do you mean that you want the results sorted in descending order instead of ascending order?

The results will give me the group by correctly but the order by only gives me the first entry for that user. I need to be able to have the last entry returned for each user

SirEvenhand

djjjozsi;10938159 wrote:
do you really have a good database model here? Sub query is unnecessary here.

you have group by, but this relation is one - to - one. Group by is for to apply mysql functions like: SUM , COUNT , ect...

Be a little more specific, and post the real table
structure with example data.

One user can have more then one type? Then you may change the table model.
Select * From award_types t2 Right Join achievement t1 On t1.Ach_TypeID = t2.AT_ID Order By t1.Ach_Time Desc
this selects all achievement and all connected award_types where t1.Ach_TypeID has a value.

dj thanks for the info.

below is the scheme for the tables

CREATE TABLE `achievement` (
  `Ach_ID` bigint(20) unsigned NOT NULL auto_increment,
  `Ach_UserID` bigint(20) unsigned NOT NULL default '0',
  `Ach_UserName` varchar(25) NOT NULL default '',
  `Ach_TypeID` tinyint(2) unsigned NOT NULL default '0',
  `Ach_Bonus` smallint(5) unsigned NOT NULL default '0',
  `Ach_Available` tinyint(2) unsigned NOT NULL default '0',
  `Ach_Collected` tinyint(2) unsigned NOT NULL default '0',
  `Ach_Time` datetime NOT NULL default '0000-00-00 00:00:00',
  `Ach_Expire` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Ach_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

-- 
-- Dumping data for table `achievement`
-- 

INSERT INTO `achievement` (`Ach_ID`, `Ach_UserID`, `Ach_UserName`, `Ach_TypeID`, `Ach_Bonus`, `Ach_Available`, `Ach_Collected`, `Ach_Time`, `Ach_Expire`) VALUES (1, 1, 'Tonythetiger', 1, 5, 5, 0, '2009-12-27 09:43:45', '2009-12-29'),
(2, 7, 'captainjack', 2, 15, 15, 0, '2009-12-27 07:53:24', '2009-12-28'),
(3, 2, 'FreddieTheFreeLoader', 1, 5, 5, 0, '2009-12-26 21:22:55', '2009-12-27'),
(4, 8, 'MsLisaH', 3, 25, 25, 0, '2009-12-27 09:34:38', '2009-12-29'),
(5, 4, 'shortandsweet', 4, 10, 5, 0, '2009-12-27 09:34:38', '2009-12-29'),
(6, 5, 'momof4', 6, 0, 5, 0, '2009-12-27 09:34:38', '2009-12-29'),
(7, 2, 'FreddieTheFreeLoader', 4, 10, 5, 0, '2010-01-01 20:00:26', '2010-01-03');

-- --------------------------------------------------------

-- 
-- Table structure for table `award_types`
-- 

CREATE TABLE `award_types` (
  `AT_ID` smallint(4) unsigned NOT NULL auto_increment,
  `AT_Type` varchar(20) NOT NULL default '',
  `AT_Detail` text NOT NULL,
  `AT_Image` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`AT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `award_types`
-- 

INSERT INTO `award_types` (`AT_ID`, `AT_Type`, `AT_Detail`, `AT_Image`) VALUES (1, 'Achievement', 'has received a bonus.', 'bonus.gif'),
(2, 'Award', 'has been given a trophy.', 'trophy1.gif'),
(3, 'Achievement', 'has earned a level up bonus.', 'star1.gif'),
(4, 'Downline', 'has added a new member to their downline.', 'netcir1.gif'),
(5, 'Bot', 'has uncovered an auto bot.', 'bot1.gif'),
(6, 'Bot', 'has found a bonus multiplier.', 'multiply1.gif');

-- --------------------------------------------------------

What I need to do is collect the records for the last achievement for each user

Any help is appreciated.

johanafm

SELECT * FROM achievement WHERE (Ach_ID, Ach_UserID) IN (
	SELECT max(AchID), Ach_UserID FROM achievement
	GROUP BY Ach_UserID
)

SirEvenhand

thanks johanafm that worked.