Tricky MySQL query involving multiple primary keys...

TreyAU21 · #1 13-Apr-2006, 12: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, 13: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, 03: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, 10: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, 09: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.

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 04:49
SQL multiple languages query	samtediou	MySQL / PHP Forum	6	23-Jul-2003 13:09
Integrate a mysql query into another	norok	MySQL / PHP Forum	2	22-Jul-2003 06: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...