difficult SQL query question

[DrDik]

Hi

I have a table like so:

id | file_id | file_repository_id | version_number | filename | package_id
7 | 5 | 7 | 1 | file1 | 11
8 | 6 | 8 | 1 | file2 | 11
9 | 5 | 9 | 2 | file1.1 | 11
10 | 5 | 10 | 3 | file1.2 | 11


I would like to get out the filename or each highest versioned file, ie id's 10 and 8 with the correct filename ie:

7,5,7,file1
and
10,5,10,3 file1.2

but my SQL:

SELECT filename AS filename,
file_id AS file_id,
file_repository_id AS file_repository_id,
MAX(version_number) AS version_number
FROM version
WHERE package_id = 11
GROUP by file_id

works well but fails to give the correct filename on the highest version_number, it gives the first filename of that file.

Anybody even understand what I wrote!

Please help.....

Thanks

Richard

 

[petersJazz]

Dont understand why row "7,5,7,file1" and "10,5,10,3,file1.2" should be selected? On what grounds?

 

[DrDik]

Hi
Sorry it's hard to explain!

All the rows for package_id=11 are shown (there are many other rows with different package_id's)

What I want isthe max version number for each file_id.

So based on file_id (file_id represents a unique file) and also on version I need the max version for each file_id so "file2" with file_id=5 is a unique file with only 1 version while the other record I need is 10,5,10,3 becuse it the max version of file_id=6

In essence: file_id is the file number, version is the version of that file_id and file_repository is a foreign key to a table of blobs. id is just a key field in this table. It's a bit confusing!

Any help greatly appreciated!

 

[petersJazz]

So in the example you would like to get out the max of file_id 5 (10,5,10...) and max of file_id 6 (8,6,8...)?

 

[DrDik]

Yes indeed

When I run my query I can get the correct max version number but it also returns the wrong filename (of that version number). It returns the version 1 filename of the correct file_id and not (in this example) the version 3 of the correct file_id.
so I would like this:

8 | 6 | 8 | 1 | file2 | 11
10 | 5 | 10 | 3 | file1.2 | 11

but seem to get this: (note the incorrect filename)

8 | 6 | 8 | 1 | file2 | 11
10 | 5 | 10 | 3 | file1 | 11


I've looked at this but cannot see the answer!

Cheers

Richard

 

[petersJazz]

Try

SELECT a.filename as filename,
a.file_repository_id as file_repository_id,
b.file_id AS file_id,
MAX(b.version_number) AS version_number
FROM version a,
version b
WHERE a.id = b.id
AND b.package_id = 11
GROUP BY filename,
file_repository_id,
file_id;

 

[DrDik]

very close..... I've tried it and it certainly works except... (you were waiting for that!)

it returns for every file_id the version number rather than just a result set of file_id and max version number.

In other words:

where file_id=5 and there are 3 versions of file_id=5 it returns a row file_id=5 and version_number=1, then file_id=5 and version_number=2 and file_id=5 and version_number=3. What I really need is just file_id=5 and version_number=3 and (for example) file_id=6 and version=1


Does that make sense? If there are 10 rows on package_id=11 but only 2 distinct file_id's say 1 and 2 but each file has 5 (in total) versions, then I need just 2 rows returned but as I test it the query will return all 10.

Cheers

Richard

 

[petersJazz]

Try

SELECT a.filename as filename,
a.file_repository_id as file_repository_id,
b.file_id AS file_id,
MAX(b.version_number) AS version_number
FROM version a,
version b
WHERE a.id = b.id
AND a.file_id = b.file_id
AND b.package_id = 11
GROUP BY filename,
file_repository_id,
file_id;

 

[piti]

hi

this is not tested, but could be working

SELECT filename AS filename,
file_id AS file_id,
file_repository_id AS file_repository_id,
version_number
FROM version
WHERE package_id = 11
GROUP by file_id
HAVING version_number = MAX(version_number)

 

[petersJazz]

Yes, having is maybe the way:

SELECT a.filename as filename,
a.file_repository_id as file_repository_id,
b.file_id AS file_id,
MAX(b.version_number) AS version_number
FROM version a,
version b
WHERE a.id = b.id
AND b.package_id = 11
GROUP BY filename,
file_repository_id,
file_id
HAVING a.version_number = version_number;

 

[DrDik]

SQL error on peter's SQL, MySQL doesn't like a.version_number in having clause, I've tried all the aliases I can think of:

Here is a dump of the table with real data, it might help:

DROP TABLE IF EXISTS `version`;

CREATE TABLE `version` (
  `id` bigint(20) NOT NULL auto_increment,
  `file_id` bigint(20) NOT NULL default '0',
  `file_repository_id` bigint(20) unsigned NOT NULL default '0',
  `version_number` int(11) NOT NULL default '0',
  `filename` varchar(255) NOT NULL default '',
  `package_id` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM COMMENT='Holds the actual document information.';

#
# Dumping data for table `version`
#

INSERT INTO `version` (`id`, `file_id`, `file_repository_id`, `version_number`, `filename`,  `package_id`) 
VALUES 
(1, 0, 0, 1, 'RGM cv.doc', 13),
(2, 1, 2, 1, 'RGM mileage.xls' ,13),
(3, 2, 3, 1, 'brian keating.txt', 13),
(4, 3, 4, 1, 'pgp263is.tar.gz',  13 ),
(12, 7, 12, 1, 'multi_static_ip.ini',  15 ),
(6, 3, 6, 2, 'php_manual_sample_6.zip', 13 ),
(7, 5, 7, 1, 'price structure.xls',  11 ),
(11, 6, 11, 1, 'pgp263is.tar.gz',  16 ),
(9, 5, 9, 2, 'brian keating.txt',  11 ),
(10, 5, 10, 3, 'brian keating.txt',  11 ),
(13, 6, 13, 2, 'invoice-1032.doc',  16 );

If we assume package_id = 13
I would like id's :1,2,3,6 but not 4 as it is version 1 of a 2 version file, version 2 is id=6. We can see this because file_id=3 for both id4 and 6.

Peter's SQL looked good until the mysql error!

 

[petersJazz]

This will compile

SELECT a.filename as filename,
a.file_repository_id as file_repository_id,
b.file_id AS file_id,
a.version_number,
MAX(b.version_number) AS version_number
FROM version a,
version b
WHERE a.id = b.id
AND b.package_id = 11
GROUP BY filename,
file_repository_id,
file_id
HAVING a.version_number = version_number;

but is it correct????

 

[DrDik]

sadly not, it still lists duplicate file_id records, ie

if a file_id has 2 version the query prints out both rather than only the record with the greatest version number

arghhhhhhh!

 

[petersJazz]

Isnt there a better way to store the information in two or more tables. It looks like you are trying to put to much in one table.

 

[HenryMonster]

To make things simple, let's forget about package_id
(you'll add it later).

I guess what you want (and what any commercial
database would have no problem to deliver) is

select <whatever you need>
from version
where file_id, version_number
in (select file_id, max(version_number) )
from version
group by file_id)

I am not mySql expert but seems like it has a very
limited support for subqueries (the call them
subSelects).

If my statement will be rejected in mySql,
implement it as a sequence of two statements:

1) create a table (to store temporary data)
with two columns: file_id, max_version
and populate it as:

insert into ...
select file_id, max(version_number)
from version
group by file_id

2) then join the version table and the other one
to select what you need.


HenryMonster.