Using an "intelligent" GROUP BY...

Anon

Hi!
I have a table with a lot of records, there are one or more referencing to a primary_key in another table, and every record have a value...

My problem: I want to retrieve the top-value from the records that references different primary-keys.
Is this possible, or do i have to just select every post, and use a "nasty" PHP-loop(Very heavy, since this table, in the long run can have X-thousand records!

Please help me, I'm REALLY stuck on this one!!

Markus, Norway

By the way: I'm running MySQL 3.22 on linux

Anon

Hi Mate,

Could you explain the layout of your tables so I can get a better idea of the structure for the sql statement.

Cheers

Ray

Anon

Ok, here's some more detailes : This is part of a billing-service for a web-hotel-site...

I have a email-table , and each time a customer adds/edits an account, a record is put into the billing-table. Each mounth a PHP-script is (supposed to) run. This script must find the bill in the billing-table for each email-account with the highest prize, the prize of the email-account is stored in the billing-table since prizes may change, but the customer is supposed to pay for what the email-accoun when they orded it from us...

Do you need more info??

Hope you can help me...
Markus

Anon

Wouldn't a MAX and GROUP BY do the trick:

SELECT e.id,
e.(whatever you need from email table),
MAX(b.price)
FROM email e
,billing b
WHERE e.id = b.id
GROUP BY e.id,
e.(whatever you need from email table);

It will return the highest price for each account.
Or have I missed something ?

This is a valid query for Oracle. hope it is for MySQL too.

JBL

Anon

TNX!!!

You are actually getting a vote from me as "hero of the day!" ;o)

Markus