On 3/31/2013 7:32 AM, william drescher wrote:
I have a table, schedule:
CREATE TABLE `schedule` (
`schedule_id` mediumint(9) NOT NULL AUTO_INCREMENT,
`provider` varchar(15) NOT NULL,
`apptTime` datetime NOT NULL,
`location` varchar(10) NOT NULL,
`duration` smallint(5) unsigned NOT NULL,
`standing_script` mediumint(9) DEFAULT NULL,
`appt_status` char(1) NOT NULL,
`patient_number` mediumint(9) NOT NULL,
`notify` smallint(6) DEFAULT NULL,
`comment` varchar(80) DEFAULT NULL,
`history` varchar(200) DEFAULT NULL,
`posted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`schedule_id`),
UNIQUE KEY `patient` (`patient_number`,`apptTime`,`schedule_id`),
UNIQUE KEY `user` (`user`,`apptTime`,`schedule_id`),
KEY `standing` (`standing_script`),
KEY `posted` (`posted`,`user`,`apptTime`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;
all of which can be ignored except for 'provider' and apptTime.
I want to query the database and have the result be only the next
appointment for 'patient_number' with each user (the doctor).
eg:
2013-04-04 JSmith
2013-04-20 WJones
where the database contains:
2013-04-04 JSmith
2013-04-10 JSmith
2013-04-17 Jsmith
2013-04-20 WJones
2013-04-24 JSmith
etc
I can get a list of future appointments for 1 patient, but can't
figure out how to just get the first for each provider (there
might be 1..5 providers)
Any suggestions will be appreciated.
--bill
This will be a seldom used query and the schedule database is
relatively small, so overhead is not a big deal.
--bill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
