From: Rick James
Date: September 18 2012 8:01pm
Subject: RE: getting certain rows from a group by
List-Archive: http://lists.mysql.com/mysql/228188
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148B889D62@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64

U0VMRUNUIG1uLioNCiAgICAgICBteC4qDQo+ID4gICAgRlJPTSAoIFNFTEVDVCBAbWluID0gTUlO
KGJvdHRvbSksDQo+ID4gICAgICAgICAgICAgICAgICBAbWF4ID0gTUFYKGJvdHRvbSksIC4uLiAp
DQo+ID4gICAgSk9JTiBkYXRhX2NzdCBtbiAgT04gYm90dG9tID0gQG1pbg0KPiA+ICAgIEpPSU4g
ZGF0YV9jc3QgbXggIE9OIGJvdHRvbSA9IEBtYXgNCg0KVGhhdCBpcywgdGhlIEZST00gZmluZHMg
dGhlIHZhbHVlIGZvciB0aGUgZGVzaXJlZCByb3cuDQpUaGUgSk9JTiB0aGVuIGdldHMgdG8gdGhl
IHJlc3Qgb2YgdGhlIGZpZWxkcy4NCkNhdmVhdDogIElmIHR3byByb3dzIGhhdmUgYm90dG9tID0g
QG1pbiwgaXQgcmV0dXJucyBib3RoLg0KDQoNCj4gLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0N
Cj4gRnJvbTogTGFycnkgTWFydGVsbCBbbWFpbHRvOmxhcnJ5Lm1hcnRlbGxAZ21haWwuY29tXQ0K
PiBTZW50OiBUdWVzZGF5LCBTZXB0ZW1iZXIgMTgsIDIwMTIgMTI6NTQgUE0NCj4gVG86IFJpY2sg
SmFtZXMNCj4gQ2M6IHBldGVyLmJyYXdsZXlAZWFydGhsaW5rLm5ldDsgbXlzcWxAbGlzdHMubXlz
cWwuY29tDQo+IFN1YmplY3Q6IFJlOiBnZXR0aW5nIGNlcnRhaW4gcm93cyBmcm9tIGEgZ3JvdXAg
YnkNCj4gDQo+IE9uIFR1ZSwgU2VwIDE4LCAyMDEyIGF0IDI6MDUgUE0sIFJpY2sgSmFtZXMgPHJq
YW1lc0B5YWhvby1pbmMuY29tPg0KPiB3cm90ZToNCj4gPiBGb3Igc2luZ2xlIHF1ZXJ5Li4uDQo+
ID4NCj4gPiBQbGFuIEE6DQo+ID4gUmVwZWF0IHRoZSBTRUxFQ1QgYXMgb25jZSBmb3IgZWFjaCAo
TUlOLCBBVkcsIGV0YykgYXMgYSBzdWJxdWVyeSBpbg0KPiA+ICAgIFdIRVJFIGJvdHRvbSA9ICgg
U0VMRUNUIE1JTihib3R0b20pIC4uLiApDQo+ID4NCj4gPiBQbGFuIEI6DQo+ID4gICAgRlJPTSAo
IFNFTEVDVCBAbWluID0gTUlOKGJvdHRvbSksDQo+ID4gICAgICAgICAgICAgICAgICBAbWF4ID0g
TUFYKGJvdHRvbSksIC4uLiApDQo+ID4gICAgSk9JTiBkYXRhX2NzdCBtbiAgT04gYm90dG9tID0g
QG1pbg0KPiA+ICAgIEpPSU4gZGF0YV9jc3QgbXggIE9OIGJvdHRvbSA9IEBtYXgNCj4gDQo+IA0K
PiBDYW4geW91IGVsYWJvcmF0ZSBvbiB0aGlzPyBJIGRvbid0IHNlZSBob3cgdGhpcyB3aWxsIGdp
dmUgbWUgd2hhdCBJDQo+IG5lZWQuIEknbSBub3QgbG9va2luZyBmb3IgdGhlIG1pbiBvciBtYXgg
Ym90dG9tIC0gSSBhbHJlYWR5IGhhdmUgdGhhdA0KPiAtIEknbSBsb29raW5nIGZvciB0aGUgcm93
IGZyb20gZWFjaCBncm91cCB0aGF0IGhhcyB0aGUgbWluIGFuZCBtYXgNCj4gYm90dG9tLg0KPiAN
Cj4gPiBQbGFuIEM6DQo+ID4gR2V0IHJpZCBvZiAzcmQgcGFydHkgcGFja2FnZXMgdGhhdCBldmVu
dHVhbGx5IGdldCBpbiB5b3VyIHdheSBpbnN0ZWFkDQo+IG9mICdoZWxwaW5nJy4NCj4gDQo+IEl0
J3Mgbm90IGRqYW5nbyB0aGF0IGlzIGdldHRpbmcgaW4gdGhlIHdheS4gZGphbmdvIGlzIGEgZmFu
dGFzdGljDQo+IGZyYW1ld29yayBmb3Igd2ViIGRldmVsb3BtZW50LiBJdCdzIGp1c3QgY2hhbmdp
bmcgcmVxdWlyZW1lbnRzLiBUaGUNCj4gb3JpZ2luYWwgZGV2ZWxvcGVyICh3aG8gbm8gbG9uZ2Vy
IHdvcmtzIGhlcmUpIGNyZWF0ZWQgYSBmZXcgZGlmZmVyZW50DQo+IGNsYXNzZXMsIGFuZCB0aGVu
IGJhc2VkIG9uIHRoZSBvcmlnaW5hbCByZXF1aXJlbWVudHMsIGNob3NlIHRvIHVzZSB0aGUNCj4g
b25lIHRoYXQgc3VwcG9ydHMgYSBzaW5nbGUgcXVlcnkuIFRoYXQgd29ya2VkIHRoZW4sIGJ1dCBt
YXkgbm90IHdvcmsNCj4gbm93LiBUaGVyZSBhcmUgb3RoZXIgY2xhc3NlcyB0aGF0IGFsbG93IG11
bHRpcGxlIHF1ZXJpZXMsIGJ1dCB0aGVuIHlvdQ0KPiBoYXZlIHRvIHBhcnNlIHRoZSBkYXRhIGFu
ZCBzdHVmZiBpdCBpbnRvIHRoZSBjb250ZXh0IHRoYXQgd2lsbCBiZQ0KPiBwaWNrZWQgdXAgYnkg
dGhlIGJyb3dzZXIgeW91cnNlbGYuIFRoZSBzaW5nbGUgcXVlcnkgY2xhc3MgZG9lcyB0aGF0IGZv
cg0KPiB5b3UuIEkgbWF5IHlldCBnbyB0aGF0IHdheSwgYnV0IEknbSB0cnlpbmcgdG8gYXZvaWQg
YSBiaWcgcmV3cml0ZSBpZiBJDQo+IGNhbi4NCj4gDQo+ID4+IC0tLS0tT3JpZ2luYWwgTWVzc2Fn
ZS0tLS0tDQo+ID4+IEZyb206IExhcnJ5IE1hcnRlbGwgW21haWx0bzpsYXJyeS5tYXJ0ZWxsQGdt
YWlsLmNvbV0NCj4gPj4gU2VudDogVHVlc2RheSwgU2VwdGVtYmVyIDE4LCAyMDEyIDQ6NTcgQU0N
Cj4gPj4gVG86IHBldGVyLmJyYXdsZXlAZWFydGhsaW5rLm5ldA0KPiA+PiBDYzogbXlzcWxAbGlz
dHMubXlzcWwuY29tDQo+ID4+IFN1YmplY3Q6IFJlOiBnZXR0aW5nIGNlcnRhaW4gcm93cyBmcm9t
IGEgZ3JvdXAgYnkNCj4gPj4NCj4gPj4gT24gVHVlLCBTZXAgMTgsIDIwMTIgYXQgNzo1MiBBTSwg
UGV0ZXIgQnJhd2xleQ0KPiA+PiA8cGV0ZXIuYnJhd2xleUBlYXJ0aGxpbmsubmV0PiB3cm90ZToN
Cj4gPj4gPiBPbiAyMDEyLTA5LTE4IDU6NTMgQU0sIExhcnJ5IE1hcnRlbGwgd3JvdGU6DQo+ID4+
ID4+DQo+ID4+ID4+IEkgaGF2ZSB0aGlzIHF1ZXJ5Og0KPiA+PiA+Pg0KPiA+PiA+PiBTRUxFQ1Qg
ZGF0YV90YXJnZXQubmFtZSBhcyBUYXJnZXQsDQo+ID4+ID4+ICAgICAgICAgcTEuZXAgYXMgRVAs
DQo+ID4+ID4+ICAgICAgICAgQ09VTlQoKikgYXMgV2FmZXJzLA0KPiA+PiA+PiAgICAgICAgIExv
dHMsDQo+ID4+ID4+ICAgICAgICAgU1VNKG51bW9uZXApLyhDT1VOVCgqKStTVU0oQ0FTRSBXSEVO
IHJlcnVucyA+IDAgVEhFTg0KPiA+PiA+PiByZXJ1bnMgRUxTRSAwIEVORCkpIGFzICdTaXRlcy9X
YWZlcicsDQo+ID4+ID4+ICAgICAgICAgTUFYKExhc3RSdW4pIGFzICJMYXN0IFJ1biIsDQo+ID4+
ID4+ICAgICAgICAgU1VNKENBU0UgV0hFTiByZXJ1bnMgPiAwIFRIRU4gcmVydW5zIEVMU0UgMCBF
TkQpIGFzDQo+IFJlcnVuLA0KPiA+PiA+PiAgICAgICAgIENPVU5UKCopK1NVTShDQVNFIFdIRU4g
cmVydW5zID4gMCBUSEVOIHJlcnVucyBFTFNFIDAgRU5EKQ0KPiA+PiA+PiBhcw0KPiA+PiBSdW5z
LA0KPiA+PiA+PiAgICAgICAgIGF2Z2JvdHRvbSBhcyAiQXZnIEJvdHRvbSIsDQo+ID4+ID4+ICAg
ICAgICAgMypzdGRib3R0b20gYXMgIjMgU2lnIiwNCj4gPj4gPj4gICAgICAgICBtYXhib3R0b20g
YXMgTWF4LA0KPiA+PiA+PiAgICAgICAgIG1pbmJvdHRvbSBhcyBNaW4sDQo+ID4+ID4+ICAgICAg
ICAgU1VNKG51bW9uZXApIGFzIENvdW50LA0KPiA+PiA+PiAgICAgICAgIFNVTShudW1vbmVwKSAt
IFNVTShudW1ib3R0b21zKSBhcyBOQXMsDQo+ID4+ID4+ICAgICAgICAgMTAwLSgoU1VNKG51bW9u
ZXApIC0gU1VNKG51bWJvdHRvbXMpKS9TVU0obnVtb25lcCkqMTAwKQ0KPiBhcw0KPiA+PiAiJQ0K
PiA+PiA+PiBTdWNjZXNzIiwNCj4gPj4gPj4gICAgICAgICAzKnN0ZGJvdHRvbS9hdmdib3R0b20g
YXMgIjNTaWcvQXZnIiwNCj4gPj4gPj4gICAgICAgICBBVkcoYXZnYm90dG9tKSBhcyAnV2FmZXIg
QXZnJywNCj4gPj4gPj4gICAgICAgICBBVkcoV2FmZXIzU2lnbWEpIGFzICdXYWZlciAzIFNpZ21h
JywNCj4gPj4gPj4gICAgICAgICBBVkcoUmFuZ2VzKSBhcyAnQXZnIFJhbmdlJywNCj4gPj4gPj4g
ICAgICAgICAzKlNURChSYW5nZXMpIGFzICczU2lnIG9mIFJhbmdlcycsDQo+ID4+ID4+ICAgICAg
ICAgTUFYKFJhbmdlcykgYXMgJ01heCBSYW5nZScsDQo+ID4+ID4+ICAgICAgICAgTUlOKFJhbmdl
cykgYXMgJ01pbiBSYW5nZScsDQo+ID4+ID4+ICAgICAgICAgKFNVTShudW1vbmVwKSAtIFNVTShu
dW1ib3R0b21zKSkvQ09VTlQoKikgYXMgJ05Bcy9XYWZlcicNCj4gPj4gPj4gRlJPTSAoU0VMRUNU
IHRhcmdldF9uYW1lX2lkLA0KPiA+PiA+PiAgICAgICAgICAgICAgIGVwLA0KPiA+PiA+PiAgICAg
ICAgICAgICAgIHdhZmVyX2lkLA0KPiA+PiA+PiAgICAgICAgICAgICAgIENPVU5UKGJvdHRvbSkg
YXMgbnVtYm90dG9tcywNCj4gPj4gPj4gICAgICAgICAgICAgICBBVkcoYm90dG9tKSBhcyBhdmdi
b3R0b20sDQo+ID4+ID4+ICAgICAgICAgICAgICAgU1REKGJvdHRvbSkgYXMgc3RkYm90dG9tLA0K
PiA+PiA+PiAgICAgICAgICAgICAgIE1BWChib3R0b20pIGFzIG1heGJvdHRvbSwNCj4gPj4gPj4g
ICAgICAgICAgICAgICBNSU4oYm90dG9tKSBhcyBtaW5ib3R0b20sDQo+ID4+ID4+ICAgICAgICAg
ICAgICAgTUFYKGRhdGVfdGltZSkgYXMgIkxhc3RSdW4iLA0KPiA+PiA+PiAgICAgICAgICAgICAg
IENPVU5UKCopIGFzIG51bW9uZXAsDQo+ID4+ID4+ICAgICAgICAgICAgICAgQ09VTlQoRElTVElO
Q1QgdGFyZ2V0X25hbWVfaWQsIGVwLCBsb3RfaWQsDQo+ID4+ID4+IGRhdGFfZmlsZV9pZCktMSBh
cyByZXJ1bnMsDQo+ID4+ID4+ICAgICAgICAgICAgICAgQ09VTlQoRElTVElOQ1QobG90X2lkKSkg
YXMgTG90cywNCj4gPj4gPj4gICAgICAgICAgICAgICAzKlNURChib3R0b20pIGFzIFdhZmVyM1Np
Z21hLA0KPiA+PiA+PiAgICAgICAgICAgICAgIE1BWChib3R0b20pIC0gTUlOKGJvdHRvbSkgYXMg
UmFuZ2VzDQo+ID4+ID4+ICAgICAgICBGUk9NIGRhdGFfY3N0DQo+ID4+ID4+ICAgICAgICBXSEVS
RSB0YXJnZXRfbmFtZV9pZCBJTiAoNzc1LCA3NzYsIDc3NywgNzc4LCA3NzksIDc4MCwgNDUsDQo+
ID4+IDQ0LA0KPiA+PiA+PiAxMTYsIDExNywgMTE4LCAxMTksIDEyMCwgMTIxKQ0KPiA+PiA+PiAg
ICAgICAgQU5EIGRhdGFfY3N0LmRhdGVfdGltZSBCRVRXRUVOICcyMDEwLTAzLTA0IDAwOjAwOjAw
JyBBTkQNCj4gPj4gPj4gJzIwMTAtMDMtMDQgMjM6NTk6NTknDQo+ID4+ID4+ICAgICAgICBHUk9V
UCBCWSB0YXJnZXRfbmFtZV9pZCwgZXAsIHdhZmVyX2lkDQo+ID4+ID4+ICAgICAgICBIQVZJTkcg
Y291bnQoKikgPCA5OTkpIHExLA0KPiA+PiA+PiAgICAgICBkYXRhX3RhcmdldA0KPiA+PiA+PiBX
SEVSRSBkYXRhX3RhcmdldC5pZCA9IHRhcmdldF9uYW1lX2lkIEdST1VQIEJZIHExLnRhcmdldF9u
YW1lX2lkLA0KPiA+PiA+PiBxMS5lcDsNCj4gPj4gPj4NCj4gPj4gPj4NCj4gPj4gPj4gVGhpcyB3
b3JrcyBmaW5lLiBCdXQgbm93IEkgbmVlZCB0byBnZXQgYSBjZXJ0YWluIGNvbHVtbg0KPiA+PiA+
PiAoaW1hZ2VfbWVhc3VyZXJfaWQpIHdpdGggZWFjaCByb3cgcmV0dXJuZWQgdGhhdCBjb3JyZXNw
b25kcyB0bw0KPiB0aGUNCj4gPj4gPj4gcm93IGZyb20gdGhlIGdyb3VwIHRoYXQgaGFzIGJvdHRv
bSA9IE1pbihib3R0b20pLCBib3R0b20gPQ0KPiA+PiA+PiBNYXgoYm90dG9tKSwgYm90dG9tIGNs
b3Nlc3QgdG8gQXZnKGJvdHRvbSksIGFuZCBib3R0b20gZnJvbSB0aGUNCj4gPj4gPj4gcm93IHdo
ZXJlIGRhdGVfdGltZSA9IE1heChkYXRlX3RpbWUpLg0KPiA+PiA+Pg0KPiA+PiA+PiBJcyB0aGlz
IGV2ZW4gcG9zc2libGUgZnJvbSBvbmUgcXVlcnk/DQo+ID4+ID4NCj4gPj4gPg0KPiA+PiA+IE1p
Z2h0IGJlLCBidXQgd2hhdCdzIHRoZSBpbXBvcnRhbmNlIG9mIGRvaW5nIGl0IGFzIG9uZSBxdWVy
eT8NCj4gPj4NCj4gPj4gQmVjYXVzZSBpdCdzIHBhcnQgb2YgYSBkamFuZ28gYmFzZWQgd2ViIGFw
cCwgYW5kIHRoZSBjbGFzcyB0aGF0IHRoaXMNCj4gPj4gaXMgcGFydCBvZiBvbmx5IHN1cHBvcnRz
IGhhdmluZyBhIHNpbmdsZSBxdWVyeS4gVG8gdXNlIG11bHRpcGxlDQo+ID4+IHF1ZXJpZXMgd2ls
bCByZXF1aXJlIGEgZmFpcmx5IG1ham9yIHJld3JpdGUgb2YgdGhlIHNlcnZlciBzaWRlIG9mDQo+
IHRoYXQgYXBwLg0KPiA+Pg0KPiA+PiA+IEknZCBzdGFydCBieQ0KPiA+PiA+IHNhdmluZyB0aGlz
IHJlc3VsdCB0byBhIHRlbXAgdGFibGUgYW5kIGRldmVsb3BpbmcgdGhlIG5ldyBxdWVyeS4NCj4g
Pj4gPiBXaGVuIHRoYXQncyBydW5uaW5nLCBzZWUgaWYgeW91IGNhbiB0byBvcHRpbWlzZSBhIHF1
ZXJ5IGJ1aWx0IGJ5DQo+ID4+ID4gcmVwbGFjaW5nIHRoZSByZWZlcmVuY2UgdG8gdGhlIHRlbXAg
dGFibGUgd2l0aCB0aGUgb3JpZ2luYWwgcXVlcnkuDQo+ID4+DQo+ID4+IFRoYW5rcywgSSdsbCBs
b29rIGludG8gdGhpcy4NCg==