Get Record with Greates Value and Latest Date

[djneel]

Hi Everyone,

I'd like to select from the table below the unique memberId with the highest (powerReps * powerWeight) value and the latest date! It's part of a competition system. Every record is a test, connected to a user...

So I'd like to select the latest test of a unique member with the highest (powerReps * powerWeight) value. If that user had better results in the past, I'd like to disregard that, because only his latest test results are valid.

Can anyone help me? Here's the table... I've made an image out of it, I don't know how else to present it.
[url=http://imageshack.us][img]http://img487.imageshack.us/img487/7583/tabletg6.gif[/img][/url]

I've tried a number of queries, including this one, but the results are not what I aimed for... I'd like to order by MAX(powerReps * powerWeight) but I can't seem to get this done... I've tried a number of other queries, but no dice...
[code]
SELECT DISTINCT memberId, MAX(powerReps * powerWeight) as maxPower, date
FROM test
GROUP BY memberId DESC
[/code]

Thanks in advance!

[djneel]

I've consulted a few books, but I just don't know what to look for. Please, anyone, give me a hint!

[djneel]

Please help, anyone!

I have to get the memberId of the person with the highest, latest testresult.
So memberId 4 would not be it now, since memberId 4 has not performed as well on his latest test.

Please, please, please!

Maybe this will be more useful:

[code]-- phpMyAdmin SQL Dump
-- version 2.9.0-rc1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generatie Tijd: 20 Jan 2007 om 13:32
-- Server versie: 5.0.24
-- PHP Versie: 5.1.6
--
-- Database: `wecompete`
--

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

--
-- Tabel structuur voor tabel `test`
--

CREATE TABLE `test` (
  `id` int(255) NOT NULL auto_increment,
  `date` datetime NOT NULL,
  `gymId` int(255) NOT NULL,
  `memberId` int(255) NOT NULL,
  `trainerId` int(255) NOT NULL,
  `isCardio` tinyint(1) NOT NULL,
  `cardioDistance` int(255) default NULL,
  `cardioDuration` int(255) default NULL,
  `cardioInclination` int(255) default NULL,
  `isPower` tinyint(1) NOT NULL,
  `powerReps` int(255) default NULL,
  `powerWeight` int(255) default NULL,
  `isConstitution` tinyint(1) NOT NULL,
  `constitutionFat` int(255) default NULL,
  `constitutionHeight` int(255) default NULL,
  `constitutionWeight` int(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

--
-- Gegevens worden uitgevoerd voor tabel `test`
--

INSERT INTO `test` (`id`, `date`, `gymId`, `memberId`, `trainerId`, `isCardio`, `cardioDistance`, `cardioDuration`, `cardioInclination`, `isPower`, `powerReps`, `powerWeight`, `isConstitution`, `constitutionFat`, `constitutionHeight`, `constitutionWeight`) VALUES
(1, '2006-12-03 16:05:13', 1, 1, 1, 1, 3500, 15, 6, 1, 12, 100, 1, 7, 184, 74),
(2, '2006-12-19 21:11:15', 2, 2, 10, 1, 2500, 15, 8, 1, 6, 80, 1, 20, 160, 43),
(3, '2006-12-17 21:12:29', 3, 3, 8, 1, 2000, 15, 2, 0, 0, 0, 1, 36, 170, 84),
(4, '2006-12-13 21:13:51', 4, 4, 3, 0, 0, 0, 0, 1, 50000, 50000, 1, 16, 170, 74),
(5, '2006-12-29 11:12:56', 1, 3, 1, 1, 2500, 15, 3, 1, 16, 75, 1, 8, 184, 76),
(8, '2006-12-29 11:12:19', 1, 1, 1, 1, 1500, 15, 15, 1, 13, 75, 1, 9, 184, 76),
(9, '2006-12-31 03:12:31', 3, 2, 4, 1, 5000, 15, 3, 0, 0, 0, 0, 0, 0, 0),
(10, '2006-12-31 04:12:01', 2, 2, 9, 1, 3000, 15, 2, 1, 12, 60, 1, 14, 158, 43),
(11, '2007-01-06 04:01:01', 4, 2, 3, 1, 2500, 15, 3, 1, 12, 50, 0, 0, 0, 0),
(21, '2007-01-19 07:01:03', 2, 4, 2, 0, 0, 15, 0, 1, 4, 4, 0, 0, 0, 0),
(20, '2007-01-19 01:01:17', 1, 4, 5, 0, 0, 15, 0, 1, 1, 1, 0, 0, 0, 0),
(19, '2007-01-15 18:15:37', 1, 3, 1, 1, 5555, 15, 5, 0, 0, 0, 1, 13, 190, 85),
(18, '2007-01-15 18:15:37', 1, 3, 1, 1, 5555, 15, 5, 0, 0, 0, 1, 13, 190, 85),
(17, '2007-01-15 18:15:37', 1, 3, 1, 1, 5555, 15, 5, 0, 0, 0, 1, 13, 190, 85),
(22, '2007-01-20 09:01:44', 1, 1, 1, 0, 0, 15, 0, 1, 10, 100, 0, 0, 0, 0);
[/code]

[fenway]

Well, you need to find the highest member first, and only then find the most recent.