MySQL Forums
Forum List  »  Newbie

Re: GROUP BY, MIN() row ordering problems!
Posted by: Gavin Lovegrove
Date: January 28, 2008 12:44AM

Thanks Max,

Here is the structure and some data. Look forward to see how you get on.

First col in results table id auto_increment / primary key.

-- phpMyAdmin SQL Dump
-- version 2.8.0.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 28, 2008 at 07:32 PM
-- Server version: 4.1.22
-- PHP Version: 5.0.4
--
-- Database: `athleticsNZ`
--

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

--
-- Table structure for table `athletes`
--

CREATE TABLE `athletes` (
`athleteID` int(10) unsigned NOT NULL auto_increment,
`nameFirst` varchar(30) NOT NULL default '',
`nameLast` varchar(30) NOT NULL default '',
`gender` varchar(6) NOT NULL default '',
`DOB` date NOT NULL default '0000-00-00',
`clubID` int(11) default '151',
PRIMARY KEY (`athleteID`)
) ENGINE=MyISAM AUTO_INCREMENT=3454 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3454 ;

--
-- Dumping data for table `athletes`
--

INSERT INTO `athletes` (`athleteID`, `nameFirst`, `nameLast`, `gender`, `DOB`, `clubID`) VALUES (199, 'Craig', 'BEARDA', 'M', '1982-06-17', 151),
(822, 'James', 'DOLPHIN', 'M', '1983-06-17', 151),
(825, 'Chris', 'DONALDSON', 'M', '1975-05-26', 151),
(3450, 'Gavin', 'LOVEGROVE', 'M', '1967-10-21', 151),

-- --------------------------------------------------------
--
-- Table structure for table `results`
--

CREATE TABLE `results` (
`resultID` int(10) unsigned NOT NULL auto_increment,
`event` varchar(20) NOT NULL default '',
`ageGroup` varchar(10) NOT NULL default '',
`time` varchar(10) default NULL,
`distHeight` varchar(10) default NULL,
`record` varchar(5) default NULL,
`wind` varchar(10) NOT NULL default '''nwr''',
`athleteID` int(11) NOT NULL default '0',
`placing` varchar(4) default NULL,
`competition` varchar(24) default NULL,
`in_out` char(3) NOT NULL default 'Out',
`venue` varchar(30) default NULL,
`date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`resultID`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

--
-- Dumping data for table `results`
--

INSERT INTO `results` (`resultID`, `event`, `ageGroup`, `time`, `distHeight`, `record`, `wind`, `athleteID`, `placing`, `competition`, `in_out`, `venue`, `date`) VALUES
('', '100m', 'SM', '00010.46', '000000', '', '1.7', 822, '3', 'RESI', 'Out', 'Sydney', '2008-01-02'),
('', '100m', 'SM', '00010.57', '000000', '', '0.9', 822, '1', 'Battle', 'Out', 'North Shore', '2008-01-02'),
('', '100m', 'SM', '00010.58', '000000', '', '1.6', 825, '1', 'Centre', 'Out', 'Dunedin', '2008-01-02'),
('', '100m', 'SM', '00010.59', '000000', '', '0.9', 199, '2', 'Battle', 'Out', 'North Shore', '2008-01-02'),
('', '100m', 'SM', '00010.79', '000000', '', '0.3', 825, '1', 'Centre', 'Out', 'Christchurch', '2008-04-03'),
('', '100m', 'SM', '00010.30', '000000', '', '1.2', 822, '1', 'JW Night', 'Out', 'Auckland', '2008-01-02'),
('', '100m', 'SM', '00010.20', '000000', '', '1.1', 822, '1', 'Battle', 'Out', 'Hamilton', '2008-02-01'),
('', '100m', 'SM', '00010.56', '000000', '', '1.1', 825, '1', 'New Meet', 'Out', 'Auckland', '2008-01-01'),
('', '100m', 'SM', '00010.20', '000000', '', '1.3', 3450, '1', 'Test Meet', 'Out', 'Inglewood', '2008-02-02'),
('', '100m', 'SM', '00010.19', '000000', '', '1.6', 3450, '1', 'Test Meet', 'Out', 'Christchurch', '2008-04-03'),
('', '100m', 'SM', '00010.15', '000000', '', '1.6', 3450, '1', 'Test Meet', 'Out', 'Auckland', '2008-01-01');

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.