OK, bear with me. This is a big one, and probably one for the gurus,
but it seemed like a nice day for it.

I have a weak population (a 'frail pool' to use my industry's jargon)
made up of several 'population types' - pushers, pullers, suckers and
blowers.

For each population type, I'd like to determine the daily birth rate
for a given date -
but only during that population type's active 'birthing period' (for
that date) -
i.e. the time difference between the birth date of the first- and
last- born (of that type) on a given day.

With me so far?

The thing is, I'm responsible for assigning 'breaks' to the new-born
individuals of each population. There are two types of break - 'odd'
and 'even', and the rules for assigning them are straightforward,
corresponding to whether an individual's id is an 'odd' or 'even'
number.

The complication arises from the fact that I must be sure that none of
the individuals amongst my most prolific birthers are bequeathed even
breaks.
In other words, they must all have odd ids.
For this reason, I want to see the results for each population,
together with confirmation that the most prolific birthers never get
an 'even' break.
I'm happy to use subqueries, but unsure how to proceed.

Anyway, here's what I have so far:

SELECT a.*,IF(SUM(IF(MOD(fp3.id,2)=1,0,1))=0,'never',SUM( IF(MOD(fp3.id,
2)=1,0,1))) `even breaks`
FROM
(
SELECT
fp1.type,
DATE_FORMAT(fp1.date_created,'%M %D') `date`,
ROUND((COUNT(*)-1)/
TIME_TO_SEC(TIMEDIFF(MAX(fp1.date_created),MIN(fp1 .date_created)))*60,1)`rate/
minute`
FROM frail_pool fp1
WHERE DATE(fp1.date_created) = '2008-04-01'
GROUP BY type,date(fp1.date_created)
ORDER BY DATE(fp1.date_created),type
)a
LEFT JOIN frail_pool fp3
ON a.type = fp3.type
GROUP BY a.type;

This works really well. It gives me the result below...

+--------+-----------+-------------+-------------+
| type | date | rate/minute | even breaks |
+--------+-----------+-------------+-------------+
| pusher | April 1st | 0.3 | 22 |
| puller | April 1st | 0.2 | 22 |
| sucker | April 1st | 1.0 | never |
| blower | April 1st | 0.1 | 22 |
+--------+-----------+-------------+-------------+

but (although it's obvious in this example), just looking around, you
can see that it could be hard to tell which of the populations is most
prolific,
and hence whether or not the breaks were appropriately assigned.

Instead, I'd ideally like to see a result limited to just one row,
that of the population with the highest birth rate, together with
confirmation that the even breaks had NEVER been assigned to any
individuals within that population - of the corresponding birth date.

I thought maybe I could order the results of the query by something
and then limit its output in some way but I'm not sure how to go about
that.

I know it's rude but I'm in a bit of a hurry, so any response you
could provide, confirming the expected result, before noon (your time,
say) would be hugely pleasing.

I'm also including some CREATE and INSERT statements because it's
obviously ful if you can demonstrate how the query would apply to
my specific dataset.

CREATE TABLE `frail_pool` (
`id` int(11) NOT NULL auto_increment,
`type` enum('pusher','puller','sucker','blower') collate
latin1_general_ci default NULL,
`date_created` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=87 ;


INSERT INTO `frail_pool` VALUES
(1, 'pusher', '2008-04-01 00:00:01'),
(2, 'pusher', '2008-04-01 00:00:00'),
(3, 'sucker', '2008-04-01 00:03:00'),
(4, 'pusher', '2008-04-01 00:03:00'),
(5, 'sucker', '2008-04-01 00:04:00'),
(6, 'sucker', '2008-04-01 00:05:00'),
(7, 'puller', '2008-04-01 00:05:30'),
(8, 'sucker', '2008-04-01 00:06:00'),
(9, 'pusher', '2008-04-01 00:06:00'),
(10, 'sucker', '2008-04-01 00:07:00'),
(11, 'sucker', '2008-04-01 00:08:00'),
(12, 'sucker', '2008-04-01 00:09:00'),
(13, 'pusher', '2008-04-01 00:09:00'),
(14, 'sucker', '2008-04-01 00:10:00'),
(15, 'puller', '2008-04-01 00:10:30'),
(16, 'sucker', '2008-04-01 00:11:00'),
(17, 'sucker', '2008-04-01 00:12:00'),
(18, 'pusher', '2008-04-01 00:12:00'),
(19, 'blower', '2008-04-01 00:12:00'),
(20, 'sucker', '2008-04-01 00:13:00'),
(21, 'sucker', '2008-04-01 00:14:00'),
(22, 'sucker', '2008-04-01 00:15:00'),
(23, 'pusher', '2008-04-01 00:15:00'),
(24, 'puller', '2008-04-01 00:15:30'),
(25, 'sucker', '2008-04-01 00:16:00'),
(26, 'sucker', '2008-04-01 00:17:00'),
(27, 'sucker', '2008-04-01 00:18:00'),
(28, 'pusher', '2008-04-01 00:18:00'),
(29, 'sucker', '2008-04-01 00:19:00'),
(30, 'sucker', '2008-04-01 00:20:00'),
(31, 'puller', '2008-04-01 00:20:30'),
(32, 'sucker', '2008-04-01 00:21:00'),
(33, 'pusher', '2008-04-01 00:21:00'),
(34, 'pusher', '2008-04-01 00:24:00'),
(35, 'blower', '2008-04-01 00:24:00'),
(36, 'puller', '2008-04-01 00:25:30'),
(37, 'pusher', '2008-04-01 00:27:00'),
(38, 'pusher', '2008-04-01 00:30:00'),
(39, 'puller', '2008-04-01 00:30:30'),
(40, 'pusher', '2008-04-01 00:33:00'),
(41, 'puller', '2008-04-01 00:35:30'),
(42, 'pusher', '2008-04-01 00:36:00'),
(43, 'blower', '2008-04-01 00:36:00'),
(44, 'pusher', '2008-04-01 00:39:00'),
(45, 'puller', '2008-04-01 00:40:30'),
(46, 'pusher', '2008-04-01 00:42:00'),
(47, 'pusher', '2008-04-01 00:45:00'),
(48, 'puller', '2008-04-01 00:45:30'),
(49, 'pusher', '2008-04-01 00:48:00'),
(50, 'blower', '2008-04-01 00:48:00'),
(51, 'puller', '2008-04-01 00:50:30'),
(52, 'pusher', '2008-04-01 00:51:00'),
(53, 'pusher', '2008-04-01 00:54:00'),
(54, 'puller', '2008-04-01 00:55:30'),
(55, 'pusher', '2008-04-01 00:57:00'),
(56, 'pusher', '2008-04-01 01:00:00'),
(57, 'blower', '2008-04-01 01:00:00'),
(58, 'puller', '2008-04-01 01:00:30'),
(59, 'puller', '2008-04-01 01:05:30'),
(60, 'puller', '2008-04-01 01:10:30'),
(61, 'blower', '2008-04-01 01:12:00'),
(62, 'puller', '2008-04-01 01:15:30'),
(63, 'puller', '2008-04-01 01:20:30'),
(64, 'blower', '2008-04-01 01:24:00'),
(65, 'puller', '2008-04-01 01:25:30'),
(66, 'puller', '2008-04-01 01:30:30'),
(67, 'puller', '2008-04-01 01:35:30'),
(68, 'blower', '2008-04-01 01:36:00'),
(69, 'puller', '2008-04-01 01:40:30'),
(70, 'puller', '2008-04-01 01:45:30'),
(71, 'blower', '2008-04-01 01:48:00'),
(72, 'blower', '2008-04-01 02:00:00'),
(73, 'blower', '2008-04-01 02:12:00'),
(74, 'blower', '2008-04-01 02:24:00'),
(75, 'blower', '2008-04-01 02:36:00'),
(76, 'blower', '2008-04-01 02:48:00'),
(77, 'blower', '2008-04-01 03:00:00'),
(78, 'blower', '2008-04-01 03:12:00'),
(79, 'blower', '2008-04-01 03:24:00'),
(80, 'blower', '2008-04-01 03:36:00'),
(81, 'blower', '2008-04-01 03:48:00'),
(82, 'blower', '2008-04-01 04:00:00'),
(83, 'blower', '2008-04-01 04:12:00'),
(84, 'sucker', '2008-04-02 00:21:00'),
(85, 'puller', '2008-04-02 01:45:30'),
(86, 'blower', '2008-04-02 04:12:00');

On Mar 31, 11:58 pm, strawberry <zac.ca...@gmail.com> wrote:

>
> With me so far?
>

I lost you at "OK, bear with me."


No, sorry --



Seriously, though -- how do you know which types are prolific before
the ID's are assigned?

Assuming you know which types are prolific ahead of time, just re-
arrange the rows such that the types that are supposed to be "even"
have even ID's, and "odds" are odd. End of problem.



If you *don't* know which ones are prolific ahead of time, then you're
using the wrong method of assigning breaks. An auto-increment column
is not a good choice to use as an "attribute" of the "real world
object." It's better to leave it as an internal-row-identifier, such
as for purposes of joining to other tables, or for uniquely
identifying a row when all other attributes are identical.

Instead add a new column where you can change its value later after
you've determined which row should be assigned what.



You might want to use a separate table to assign breaks. E.g., one
type is prolific this week, but a different one is prolific next
week. Just update the "break" table, joined by type name to the
"frail_pool" table.


Hope that's ful.

On Apr 1, 8:42 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
> On Mar 31, 11:58 pm, strawberry <zac.ca...@gmail.com> wrote:
>
>
>
> > With me so far?
>
> I lost you at "OK, bear with me."
>
> No, sorry --
>
> Seriously, though -- how do you know which types are prolific before
> the ID's are assigned?
>
> Assuming you know which types are prolific ahead of time, just re-
> arrange the rows such that the types that are supposed to be "even"
> have even ID's, and "odds" are odd. End of problem.
>
> If you *don't* know which ones are prolific ahead of time, then you're
> using the wrong method of assigning breaks. An auto-increment column
> is not a good choice to use as an "attribute" of the "real world
> object." It's better to leave it as an internal-row-identifier, such
> as for purposes of joining to other tables, or for uniquely
> identifying a row when all other attributes are identical.
>
> Instead add a new column where you can change its value later after
> you've determined which row should be assigned what.
>
> You might want to use a separate table to assign breaks. E.g., one
> type is prolific this week, but a different one is prolific next
> week. Just update the "break" table, joined by type name to the
> "frail_pool" table.
>
> Hope that's ful.

Actually suckers are always the most prolific because, as my results
demonstrate, there's one born every minute and we'll never give you an
even break. ;-)

frail pool - april fool

Anyway, enjoy what's left of it.

On Apr 1, 3:43 pm, strawberry <zac.ca...@gmail.com> wrote:
> On Apr 1, 8:42 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
>
> [...]
>
> Actually suckers are always the most prolific because, as my results
> demonstrate, there's one born every minute and we'll never give you an
> even break. ;-)
>
> frail pool - april fool
>
> Anyway, enjoy what's left of it.

Well, that was rather elaborate.

Seeing just how much work you put into that gag, I have to wonder
which of us is the bigger fool!

Hope it was worth it!

;-)

ThanksButNo wrote:
> On Apr 1, 3:43 pm, strawberry <zac.ca...@gmail.com> wrote:
>> On Apr 1, 8:42 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
>>
>> [...]
>>
>> Actually suckers are always the most prolific because, as my results
>> demonstrate, there's one born every minute and we'll never give you an
>> even break. ;-)
>>
>> frail pool - april fool
>>
>> Anyway, enjoy what's left of it.
>
> Well, that was rather elaborate.
>
> Seeing just how much work you put into that gag, I have to wonder
> which of us is the bigger fool!
>
> Hope it was worth it!
>
> ;-)
>

Actually, I thought it was quite good.

The best ones take a while for you to realize they are jokes. Just as
this one did :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Apr 1, 9:04 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> ThanksButNo wrote:
> > On Apr 1, 3:43 pm, strawberry <zac.ca...@gmail.com> wrote:
> >> On Apr 1, 8:42 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
>
> >> [...]
>
> >> Actually suckers are always the most prolific because, as my results
> >> demonstrate, there's one born every minute and we'll never give you an
> >> even break. ;-)
>
> >> frail pool - april fool
>
> >> Anyway, enjoy what's left of it.
>
> > Well, that was rather elaborate.
>
> > Seeing just how much work you put into that gag, I have to wonder
> > which of us is the bigger fool!
>
> > Hope it was worth it!
>
> > ;-)
>
> Actually, I thought it was quite good.
>
> The best ones take a while for you to realize they are jokes. Just as
> this one did :-)
>

Make no mistake, it was thorough -- and I was thoroughly taken in!

(Note to self; never respond to a posting made on or about April 1)

I just hope everybody got enough laughs to make the effort worthwhile
-- because it looked like a LOT of effort went into it!

Me, if it takes more than 5 minutes to concoct, it's not worth it.

(Yah, yah, just trying to soothe my burns, that's all.)

;-)

ThanksButNo wrote:
> On Apr 1, 9:04 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> ThanksButNo wrote:
>>> On Apr 1, 3:43 pm, strawberry <zac.ca...@gmail.com> wrote:
>>>> On Apr 1, 8:42 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
>>>> [...]
>>>> Actually suckers are always the most prolific because, as my results
>>>> demonstrate, there's one born every minute and we'll never give you an
>>>> even break. ;-)
>>>> frail pool - april fool
>>>> Anyway, enjoy what's left of it.
>>> Well, that was rather elaborate.
>>> Seeing just how much work you put into that gag, I have to wonder
>>> which of us is the bigger fool!
>>> Hope it was worth it!
>>> ;-)
>> Actually, I thought it was quite good.
>>
>> The best ones take a while for you to realize they are jokes. Just as
>> this one did :-)
>>
>
> Make no mistake, it was thorough -- and I was thoroughly taken in!
>
> (Note to self; never respond to a posting made on or about April 1)
>
> I just hope everybody got enough laughs to make the effort worthwhile
> -- because it looked like a LOT of effort went into it!
>
> Me, if it takes more than 5 minutes to concoct, it's not worth it.
>
> (Yah, yah, just trying to soothe my burns, that's all.)
>
> ;-)
>

Well, the first clue I had was that strawberry was ASKING a question :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Apr 2, 9:40 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> ThanksButNo wrote:
> > On Apr 1, 9:04 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> ThanksButNo wrote:
> >>> On Apr 1, 3:43 pm, strawberry <zac.ca...@gmail.com> wrote:
> >>>> On Apr 1, 8:42 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
> >>>> [...]
> >>>> Actually suckers are always the most prolific because, as my results
> >>>> demonstrate, there's one born every minute and we'll never give you an
> >>>> even break. ;-)
> >>>> frail pool - april fool
> >>>> Anyway, enjoy what's left of it.
> >>> Well, that was rather elaborate.
> >>> Seeing just how much work you put into that gag, I have to wonder
> >>> which of us is the bigger fool!
> >>> Hope it was worth it!
> >>> ;-)
> >> Actually, I thought it was quite good.
>
> >> The best ones take a while for you to realize they are jokes. Just as
> >> this one did :-)
>
> > Make no mistake, it was thorough -- and I was thoroughly taken in!
>
> > (Note to self; never respond to a posting made on or about April 1)
>
> > I just hope everybody got enough laughs to make the effort worthwhile
> > -- because it looked like a LOT of effort went into it!
>
> > Me, if it takes more than 5 minutes to concoct, it's not worth it.
>
> > (Yah, yah, just trying to soothe my burns, that's all.)
>
> > ;-)
>
> Well, the first clue I had was that strawberry was ASKING a question :-)
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

;-)

That's not quite fair. I think I've asked LOTS of questions - and
anyway I've learnt tons from you guys. Actually I screwed up the
dataset - not that I really thought anyone would go that far to
notice. I meant to double all the ids and then subtract 1 from the
suckers! Oops, I'll try to get it right next time!

I thought the giveaway might be that it 'seemed a nice day for it',
that it was a 'frail pool', or that they 'never get an even break'. To
be honest, I really thought requesting before noon 'your time'
was going a bit far but you know, you've got to push these things -
and the resultset must have set some alarm bells ringing!!!!

Yeah it was elaborate and I went too far and I'm a fool too, and I
should get out more - but I had a chuckle.

strawberry wrote:
> On Apr 2, 9:40 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> ThanksButNo wrote:
>>> On Apr 1, 9:04 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> ThanksButNo wrote:
>>>>> On Apr 1, 3:43 pm, strawberry <zac.ca...@gmail.com> wrote:
>>>>>> On Apr 1, 8:42 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
>>>>>> [...]
>>>>>> Actually suckers are always the most prolific because, as my results
>>>>>> demonstrate, there's one born every minute and we'll never give you an
>>>>>> even break. ;-)
>>>>>> frail pool - april fool
>>>>>> Anyway, enjoy what's left of it.
>>>>> Well, that was rather elaborate.
>>>>> Seeing just how much work you put into that gag, I have to wonder
>>>>> which of us is the bigger fool!
>>>>> Hope it was worth it!
>>>>> ;-)
>>>> Actually, I thought it was quite good.
>>>> The best ones take a while for you to realize they are jokes. Just as
>>>> this one did :-)
>>> Make no mistake, it was thorough -- and I was thoroughly taken in!
>>> (Note to self; never respond to a posting made on or about April 1)
>>> I just hope everybody got enough laughs to make the effort worthwhile
>>> -- because it looked like a LOT of effort went into it!
>>> Me, if it takes more than 5 minutes to concoct, it's not worth it.
>>> (Yah, yah, just trying to soothe my burns, that's all.)
>>> ;-)
>> Well, the first clue I had was that strawberry was ASKING a question :-)
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> ;-)
>
> That's not quite fair. I think I've asked LOTS of questions - and
> anyway I've learnt tons from you guys. Actually I screwed up the
> dataset - not that I really thought anyone would go that far to
> notice. I meant to double all the ids and then subtract 1 from the
> suckers! Oops, I'll try to get it right next time!
>
> I thought the giveaway might be that it 'seemed a nice day for it',
> that it was a 'frail pool', or that they 'never get an even break'. To
> be honest, I really thought requesting before noon 'your time'
> was going a bit far but you know, you've got to push these things -
> and the resultset must have set some alarm bells ringing!!!!
>
> Yeah it was elaborate and I went too far and I'm a fool too, and I
> should get out more - but I had a chuckle.
>

That was a compliment to you. You provide a LOT more good information
than you ask :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================