Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
|
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 14th, 2003, 06:19 PM
eln eln is offline
Registered User
 
Join Date: Nov 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default two tables, one query, getting the latest for each

sbj: two tables, one query, getting the latest for each in tbl1 from tbl2
hi,
Could you please help me with this?
Say I have two tables

tbl1 {
id int auto_increment,
name string,
description string
}

tbl2 {
id int,
file_url int,
version int
}

These two tables are used for software project releases on a web site,
`tbl1` keeps the project `name`, `description`, and auto inc. `id` and the `tbl2` keeps files released for a project, `id` references `tbl1.id`, the file_url is the file name that would be presented for a given `version`. So as you might guessed a project once is inserted into tbl1 then each time a file is released for a given project its name along with its version is inserted into the tbl2 referencing tbl1.id, so in the end e.g. Project1 Project2 Project3 each has ~20 files versions start with 0 up to say 20, my question is; executing only __one__ query, how do I get the latest (highest version) file name for a given project _and_ optinaly for all projects, _again_ only one query. Feel free to alter the tables, in a sense all I need to know is how/is it possible/ what needs to be used.

Thanks

P.S Hope you understand my English
 
Old November 17th, 2003, 03:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi,

To list latest version of all projects.

select name, max(version) from tbl1, tbl2
where tbl1.id=tbl2.id
group by name order by name
______________________________________________

To list the Latest version of a project provided the PROJECT_NAME or PROJECT_ID

select name, max(version) from tbl1, tbl2
where tbl1.id=tbl2.id and tbl1.name=<PROJECT_NAME>
                     OR
select name, max(version) from tbl1, tbl2
where tbl1.id=tbl2.id and tbl1.id=<PROJECT_ID>
______________________________________________

Hope that helps

Cheers,


-Vijay G
 
Old November 17th, 2003, 11:15 PM
eln eln is offline
Registered User
 
Join Date: Nov 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vijay,
thanks for the help.

could you take a look at this
below is the `tbl2` altered and called `files` now and `tbl1` is called `downloads` now.

Create Table: CREATE TABLE `files` (
 `id` int(10) NOT NULL auto_increment,
 `download_id` int(10) default NULL,
 `posted` timestamp(14) NOT NULL,
 `version` int(6) default NULL,
 `url` varchar(255) default NULL,
 PRIMARY KEY (`id`),
 KEY `download_id` (`download_id`),
 KEY `version` (`version`)
 TYPE=MyISAM



then I load it up like:

+-------------+---------+------------+----------+
| download_id | version | url | pr |
+-------------+---------+------------+----------+
| 2 | 1 | shex_file1 | 03:56:38 |
| 2 | 2 | shex_file2 | 03:56:46 |
| 2 | 3 | shex_file3 | 03:56:50 |
| 2 | 4 | shex_file4 | 03:56:53 |
| 2 | 5 | shex_file5 | 03:56:57 |
| 2 | 6 | shex_file6 | 03:57:02 |
| 10 | 1 | prj10_1 | 03:59:36 |
| 11 | 5 | prj11_5 | 03:59:27 |
| 11 | 4 | prj11_4 | 03:59:23 |
| 11 | 3 | prj11_3 | 03:59:18 |
| 11 | 2 | prj11_2 | 03:59:06 |
| 11 | 1 | prj11_1 | 03:58:53 |
| 10 | 2 | prj10_2 | 03:59:39 |
| 10 | 3 | prj10_3 | 03:59:43 |
+-------------+---------+------------+----------+

SELECT url, name, max(version), DATE_FORMAT( posted , '(%M %D, %Y %h:%i:%s)' ) AS posted_humane FROM files, downloads WHERE downloads.id=files.download_id GROUP BY downloads.id ORDER BY downloads.id;

I don't understand, it returns latest `version` but not `url` corresponding to it, please see below:

+------------+---------+--------------+--------------------------------+
| url | name | max(version) | posted_humane |
+------------+---------+--------------+--------------------------------+
| shex_file1 | shex | 6 | (November 18th, 2003 03:56:38) |
| prj10_1 | zzzzzh | 3 | (November 18th, 2003 03:59:36) |
| prj11_5 | atoolsh | 5 | (November 18th, 2003 03:59:27) |
+------------+---------+--------------+--------------------------------+
could you help??
Thank you very much!!






Similar Threads
Thread Thread Starter Forum Replies Last Post
Query for getting latest date and following .. dragonball SQL Server 2000 4 February 28th, 2008 08:10 AM
SQL Query - picking latest record and group by markw SQL Language 2 April 6th, 2005 03:54 AM
Query to Select latest X entries acdsky Classic ASP Databases 3 July 6th, 2004 01:41 AM
Latest Date with Inner Join Query rstelma SQL Server 2000 3 November 5th, 2003 07:32 AM
Returning ONLY the latest records in a query kilkerr1 Access 3 November 3rd, 2003 01:03 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.