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

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

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!!