[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Query help -
From:       william drescher <william () TechServSys ! com>
Date:       2013-03-31 11:36:48
Message-ID: kj9745$2j7$1 () ger ! gmane ! org
[Download RAW message or body]

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

[prev in list] [next in list] [prev in thread] [next in thread]