List:General Discussion« Previous MessageNext Message »
From:william drescher Date:March 31 2013 11:32am
Subject:Query help -
View as plain text  
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

Thread
Query help -william drescher31 Mar
  • Re: Query help -william drescher31 Mar
    • Re: Query help - Solvedwilliam drescher31 Mar