![]() |
|
#1
|
||||
|
||||
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
|
||||
|
||||
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:
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:
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
|
||||
|
||||
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:
__________________
J de Silva Learning Journal | GIDForums™ | GIDNetwork™ | GIDWebhosts™ | GIDSearch™ |
|
#4
|
||||
|
||||
Re: Tricky MySQL query involving multiple primary keys...Quote:
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
|
||||
|
||||
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. __________________
J de Silva Learning Journal | GIDForums™ | GIDNetwork™ | GIDWebhosts™ | GIDSearch™ |
|
#6
|
|||
|
|||
Re: Tricky MySQL query involving multiple primary keys...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 |
|
#7
|
||||
|
||||
Re: Tricky MySQL query involving multiple primary keys...Quote:
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:
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:
EXPLAIN is sometimes helpful for determining the efficiency of queries. Code:
|
Recent GIDBlog
Once again, no time for hobbies by crystalattice
| Thread Tools | Search 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 |
Network Sites: GIDNetwork · GIDWebHosts · GIDSearch · Learning Journal by J de Silva, The