Tricky MySQL query involving multiple primary keys...

TreyAU21 · #1 13-Apr-2006, 13:48

I've had my head wrapped around it for awhile and I don't have any answers. It's a tricky one... here's the schema:

Services: service_id, service_revision, service_name, service_description, service_price

Example rows:
0001; 1; Plumbing; Plumbing Service (per hour); $75
0001; 2; Plumbing; Plumbing Service (per hour); $90
0002; 1; Carpeting; Carpeting Service (per hour); $50
0002; 2; Carpeting; Carpeting Service (per hour); $60
0002; 3; Carpeting; Carpeting Service (per hour); $65
0003; 1; Transportation; Transportation Service (per hour); $30
0004; 1; Delivery; Delivery Service (per hour); $35

and so on and so forth. What I want to do is select a row for EACH service ID that has the MAX revision number. So, for the data sample above... this is what I want returned (the most recent revision):

Returned:
0001; 2; Plumbing; Plumbing Service (per hour); $90
0002; 3; Carpeting; Carpeting Service (per hour); $65
0003; 1; Transportation; Transportation Service (per hour); $30
0004; 1; Delivery; Delivery Service (per hour); $35

Any help would be greatly appreciated as I've had my head wrapped around this one for quite some time, and I can't find the query that gets me what I need. Thanks in advance!

Trey

TreyAU21 · #2 14-Apr-2006, 14:12

Ok... so I gave up and just used 2 queries to solve the problem...

Query #1:

Code:

SELECT DISTINCT service_id 
       FROM services 
       ORDER BY service_id

And then for each row that that query returned, I used this query (hint: $row carried the object that was returned from the mysql_fetch_object() call... if not already apparent, I am using PHP):

Query #2:

Code:

SELECT * FROM services 
         WHERE service_id = '$row->service_id' 
         ORDER BY service_revision DESC 
         LIMIT 0,1

The row returned by that query gave me what I was looking for. It's a pain to do it that way... but this isn't a time critical system, so more queries aren't going to hurt me.

JdS · #3 17-Apr-2006, 04:24

I am sorry I was not able to help sooner -- I was travelling quite a bit over the weekend.

What you need is the GROUP BY clause with the MAX() function.

Something like

Code:

SELECT
	`service_id`,
	MAX( `service_revision` ) AS `last_revision`,
	`service_name`,
	`service_description`,
	`service_price`
FROM
	`the_table_name`
GROUP BY
	`service_id`

TreyAU21 · #4 17-Apr-2006, 11:36

Quote:

Originally Posted by JdS

I am sorry I was not able to help sooner -- I was travelling quite a bit over the weekend.

What you need is the GROUP BY clause with the MAX() function.

Something like

Code:

SELECT
	`service_id`,
	MAX( `service_revision` ) AS `last_revision`,
	`service_name`,
	`service_description`,
	`service_price`
FROM
	`the_table_name`
GROUP BY
	`service_id`

Yeah... I know I tried that one. It produces some odd results. It will get the MAX revision number (and there will only be one row per service id), but the price that it gets ALWAYS corresponds to the row for revision 1 (as opposed to the most recent), so for the example that I posted in the original post... I would have gotten this result:

0001; 2; Plumbing; Plumbing Service (per hour); $75 (should be $90)
0002; 3; Carpeting; Carpeting Service (per hour); $50 (should be $65)
0003; 1; Transportation; Transportation Service (per hour); $30
0004; 1; Delivery; Delivery Service (per hour); $35

Any ideas on why that might be happening?

JdS · #5 18-Apr-2006, 10:56

There is probably a way to get the information you need but I can't think of any right now.

If I were you, I would reconsider the design of my tables and normalize them. That way I would have a `service_revision` table and would left join the information to a `service_detail` table or something like that. That way, it's easier to use the MAX() data on one table to get the details off the other.

Maybe I will use your data to on a test table on my PC and see what I can come up with.

Preydator · #6 18-Feb-2009, 20:07

This will do the trick in just one mysql statement, the sub query sorts first the service_revision, then from the result, another query groups it by service_id and using the max function on service_revision.

select service_id, max(service_revision), service_name, service_description, service_price from (SELECT service_id, service_revision,service_name, service_description, service_price
FROM yourtable
order by service_revision desc) as subtable group by service_id

MisterChucker · #7 29-Mar-2009, 21:45

Quote:

Originally Posted by TreyAU21

...this isn't a time critical system, so more queries aren't going to hurt me.

Using a single query is often, but not always, more efficient than using multiple queries.

I have a database where I keep track of television shows I have recorded to DVD. The database holds all prime-time television shows, even those that I have not recorded. In my episode table, air_date, air_time, and channel_number form the primary key.

I wanted a script that would compile a list of episodes for shows that had been recorded at least once. If even only one episode had ever been recorded for a particular show, I wanted to see every episode for that show.

At first, I used a single query that included a sub-query. That worked for a few months. When my database grew to around 3000 records, my query started to time out.

Original: large, single query

PHP Code:


<?php
// Database connection initialization not shown

$query = "SELECT `show_name`      AS `show` "
       . "     , `air_date`       AS `date` "
       . "     , `episode_title`  AS `episode` "
       . "     , `channel_number` AS `ch` "
       . "     , `new_episode`    AS `n` "
       . "     , `was_recorded`   AS `r` "
       . "FROM `episode` "
       . "WHERE `show_name` "
       . "IN ("
       . "     SELECT DISTINCT `show_name` "
       . "     FROM `episode` "
       . "     WHERE `was_recorded` = 1 "
       . "   ) "
       . "ORDER BY `show_name`"
       . "       , `air_date`"
       . "       , `episode_title`";

if ($result = mysql_query($query))
{
    while ($row = mysql_fetch_assoc($result))
    {
        // Do something with each row here
    }
}
?>

The solution was to split the query into two queries. The first query retrieves a list of shows where at least one episode has been recorded. The second query retrieves the episodes for each show.

Revised: small, iterative queries

PHP Code:


<?php
// Database connection initialization not shown

$query_1 = "SELECT DISTINCT `show_name` "
         . "FROM `episode` "
         . "WHERE `was_recorded` = 1 "
         . "ORDER BY `show_name`";

if ($result_1 = mysql_query($query_1))
{
    while ($row_1 = mysql_fetch_assoc($result_1))
    {
        // Builds smaller queries, one for each show_name
        $query_2 = "SELECT `show_name`      AS `show` "
                 . "     , `air_date`       AS `date` "
                 . "     , `episode_title`  AS `episode` "
                 . "     , `channel_number` AS `ch` "
                 . "     , `new_episode`    AS `n` "
                 . "     , `was_recorded`   AS `r` "
                 . "FROM `episode` "
                   // Some show_names contain quotes
                 . "WHERE `show_name` = '".mysql_escape_string($row_1['show_name'])."'"
                 . "ORDER BY `show_name`"
                 . "       , `air_date`"
                 . "       , `episode_title`";
        
        if ($result_2 = mysql_query($query_2, $DB))
        {
            while ($row_2 = mysql_fetch_assoc($result_2))
            {
                // Do something with each row here
            }
        }
    }
}
?>

EXPLAIN is sometimes helpful for determining the efficiency of queries.

Code:

EXPLAIN SELECT first_name, last_name FROM my_friends WHERE still_calls_me = 1;

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
MySQL Query Problem	eRIC	MySQL / PHP Forum	1	18-May-2004 05:49
SQL multiple languages query	samtediou	MySQL / PHP Forum	6	23-Jul-2003 14:09
Integrate a mysql query into another	norok	MySQL / PHP Forum	2	22-Jul-2003 07:25

Tricky MySQL query involving multiple primary keys...

Re: Tricky MySQL query involving multiple primary keys...

Re: Tricky MySQL query involving multiple primary keys...

Re: Tricky MySQL query involving multiple primary keys...

Re: Tricky MySQL query involving multiple primary keys...

Re: Tricky MySQL query involving multiple primary keys...

Re: Tricky MySQL query involving multiple primary keys...