GIDForums  

Go Back   GIDForums > Computer Programming Forums > MySQL / PHP Forum
User Name
Password
Register FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
 
Thread Tools Search this Thread Rate Thread
  #1  
Old 13-Apr-2006, 12:48
TreyAU21's Avatar
TreyAU21 TreyAU21 is offline
Member
 
Join Date: Feb 2006
Location: Atlanta, GA
Posts: 116
TreyAU21 has a spectacular aura aboutTreyAU21 has a spectacular aura about

Tricky MySQL query involving multiple primary keys...


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
__________________
If practice makes perfect and nobody's perfect... why practice?

Homepage: http://www.treywhite.com
Blog: http://www.treywhite.com/blog.php
Web Design Company: http://www.ewebproductions.com
  #2  
Old 14-Apr-2006, 13:12
TreyAU21's Avatar
TreyAU21 TreyAU21 is offline
Member
 
Join Date: Feb 2006
Location: Atlanta, GA
Posts: 116
TreyAU21 has a spectacular aura aboutTreyAU21 has a spectacular aura about

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


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.
__________________
If practice makes perfect and nobody's perfect... why practice?

Homepage: http://www.treywhite.com
Blog: http://www.treywhite.com/blog.php
Web Design Company: http://www.ewebproductions.com
  #3  
Old 17-Apr-2006, 03:24
JdS's Avatar
JdS JdS is offline
Senior Member
 
Join Date: Aug 2001
Location: KUL, Malaysia
Posts: 3,371
JdS will become famous soon enough

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


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`
  #4  
Old 17-Apr-2006, 10:36
TreyAU21's Avatar
TreyAU21 TreyAU21 is offline
Member
 
Join Date: Feb 2006
Location: Atlanta, GA
Posts: 116
TreyAU21 has a spectacular aura aboutTreyAU21 has a spectacular aura about

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


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?
__________________
If practice makes perfect and nobody's perfect... why practice?

Homepage: http://www.treywhite.com
Blog: http://www.treywhite.com/blog.php
Web Design Company: http://www.ewebproductions.com
  #5  
Old 18-Apr-2006, 09:56
JdS's Avatar
JdS JdS is offline
Senior Member
 
Join Date: Aug 2001
Location: KUL, Malaysia
Posts: 3,371
JdS will become famous soon enough

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


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.
 

Recent GIDBlogLast Week of IA Training by crystalattice

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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

Network Sites: GIDNetwork · GIDWebHosts · GIDSearch · Learning Journal by J de Silva, The

All times are GMT -6. The time now is 21:45.


vBulletin, Copyright © 2000 - 2008, Jelsoft Enterprises Ltd.