Returning unique records

**Aggro** · Oct 27 '05, 02:15 PM

Re: Returning unique records

Jeff North wrote:
[color=blue]
> I need to be able to return only resID 2 and 6.[/color]

select resID from yourtablename where resID in(2,6);

**Markus Popp** · Oct 27 '05, 03:15 PM

Re: Returning unique records

I think that's not what the problem is. I guess, Jeff wants to get the
filename with the latest version.

There's some information missing to accomplish this, because MySQL can't
clearly identify the program out of the filename. Maybe there are solutions
with substring and locate to extract the program name, but this wouldn't be
a 100 % proof solution.

I would recommand adding another table, where the program names are stored,
with a relation to this table. Then it's possible to join the two tables and
select for the maximum version number for each program.

Markus

**Jeff North** · Oct 27 '05, 05:45 PM

Re: Returning unique records

On Thu, 27 Oct 2005 14:06:51 GMT, in mailing.databas e.mysql Aggro
<spammerdream@y ahoo.com> wrote:
[color=blue]
>| Jeff North wrote:
>|
>| > I need to be able to return only resID 2 and 6.
>|
>| select resID from yourtablename where resID in(2,6);[/color]

Sorry, I should've been clearer in my posting (I did sa my brains were
scambled LOL ).

I need to retrieve the highest version number and/or the lates file
i.e. myfile[3].zip.
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Markus Popp** · Oct 27 '05, 06:25 PM

Re: Returning unique records

Check out this:

mysql> create table programs (progID int unsigned not null auto_increment
primary key,
programName varchar(50) not null) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into programs (programName) values ('laserdatandri a'),
('microweb');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> create table files (resID int unsigned not null auto_increment
primary
key, progID int unsigned not null, FLEName varchar(50) not null, VersionNbr
decimal
(6,3) not null, key(progID), foreign key (progID) references
programs(progID ))
engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into files (progID, FLEName, VersionNbr) values (1,
'35_laserdatan
dria2104.zip', '1.0'), (1, '35_laserdatand ria2104[1].zip', '1.23'), (2,
'35_microweb1.3 1.zip', '1.0'),
(2, '35_microweb1.3 1[1].zip', '1.234'), (2, '35_microweb1.3 1[2].zip',
'1.345'),
(2, '35_microweb1.3 1[3].zip', '1.456');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from programs;
+--------+----------------+
| progID | programName |
+--------+----------------+
| 1 | laserdatandria |
| 2 | microweb |
+--------+----------------+
2 rows in set (0.00 sec)

mysql> select * from files;
+-------+--------+------------------------------+------------+
| resID | progID | FLEName | VersionNbr |
+-------+--------+------------------------------+------------+
| 1 | 1 | 35_laserdatandr ia2104.zip | 1.000 |
| 2 | 1 | 35_laserdatandr ia2104[1].zip | 1.230 |
| 3 | 2 | 35_microweb1.31 .zip | 1.000 |
| 4 | 2 | 35_microweb1.31[1].zip | 1.234 |
| 5 | 2 | 35_microweb1.31[2].zip | 1.345 |
| 6 | 2 | 35_microweb1.31[3].zip | 1.456 |
+-------+--------+------------------------------+------------+
6 rows in set (0.00 sec)

mysql> select f.resID, f.FLEName, f.VersionNbr
-> from files f inner join programs p using (progID)
-> where f.VersionNbr =
-> (select max(VersionNbr) from files where progID = f.progID);
+-------+------------------------------+------------+
| resID | FLEName | VersionNbr |
+-------+------------------------------+------------+
| 2 | 35_laserdatandr ia2104[1].zip | 1.230 |
| 6 | 35_microweb1.31[3].zip | 1.456 |
+-------+------------------------------+------------+
2 rows in set (0.00 sec)

mysql>

**Aggro** · Oct 27 '05, 10:35 PM

Re: Returning unique records

Jeff North wrote:
[color=blue]
> I need to retrieve the highest version number and/or the lates file
> i.e. myfile[3].zip.[/color]

If it is possible to change the table structure I suggest doing that, as
other have mentioned (by adding extra column to tell program id). Other
solutions can be slow, hard to implement or cause errors in production
and are not suggested to be used.

You can use alter table -command to add columns to existing databases,
if that database is already in production. That way you won't lose any
existing data.

**Jeff North** · Oct 28 '05, 04:35 AM

Re: Returning unique records

On Thu, 27 Oct 2005 17:08:30 +0200, in mailing.databas e.mysql "Markus
Popp" <mfp@gmx.li> wrote:
[color=blue]
>| I think that's not what the problem is. I guess, Jeff wants to get the
>| filename with the latest version.[/color]

Yep, sorry I didn't make myself clearer in my original post.
[color=blue]
>| There's some information missing to accomplish this, because MySQL can't
>| clearly identify the program out of the filename. Maybe there are solutions
>| with substring and locate to extract the program name, but this wouldn't be
>| a 100 % proof solution.[/color]

True, as not all entries will be suffixed with a number.
[color=blue]
>| I would recommand adding another table, where the program names are stored,
>| with a relation to this table. Then it's possible to join the two tables and
>| select for the maximum version number for each program.[/color]

I was hoping to avoid this type of setup :-(
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Jeff North** · Oct 28 '05, 07:55 AM

Re: Returning unique records

On Thu, 27 Oct 2005 20:16:06 +0200, in mailing.databas e.mysql "Markus
Popp" <mfp@gmx.li> wrote:
[color=blue]
>| Check out this:
>|
>| mysql> create table programs (progID int unsigned not null auto_increment
>| primary key,
>| programName varchar(50) not null) engine=innodb;
>| Query OK, 0 rows affected (0.05 sec)
>|
>| mysql> insert into programs (programName) values ('laserdatandri a'),
>| ('microweb');
>| Query OK, 2 rows affected (0.02 sec)
>| Records: 2 Duplicates: 0 Warnings: 0
>|
>| mysql> create table files (resID int unsigned not null auto_increment
>| primary
>| key, progID int unsigned not null, FLEName varchar(50) not null, VersionNbr
>| decimal
>| (6,3) not null, key(progID), foreign key (progID) references
>| programs(progID ))
>| engine=innodb;
>| Query OK, 0 rows affected (0.05 sec)
>|
>| mysql> insert into files (progID, FLEName, VersionNbr) values (1,
>| '35_laserdatan
>| dria2104.zip', '1.0'), (1, '35_laserdatand ria2104[1].zip', '1.23'), (2,
>| '35_microweb1.3 1.zip', '1.0'),
>| (2, '35_microweb1.3 1[1].zip', '1.234'), (2, '35_microweb1.3 1[2].zip',
>| '1.345'),
>| (2, '35_microweb1.3 1[3].zip', '1.456');
>| Query OK, 6 rows affected (0.00 sec)
>| Records: 6 Duplicates: 0 Warnings: 0
>|
>| mysql> select * from programs;
>| +--------+----------------+
>| | progID | programName |
>| +--------+----------------+
>| | 1 | laserdatandria |
>| | 2 | microweb |
>| +--------+----------------+
>| 2 rows in set (0.00 sec)
>|
>| mysql> select * from files;
>| +-------+--------+------------------------------+------------+
>| | resID | progID | FLEName | VersionNbr |
>| +-------+--------+------------------------------+------------+
>| | 1 | 1 | 35_laserdatandr ia2104.zip | 1.000 |
>| | 2 | 1 | 35_laserdatandr ia2104[1].zip | 1.230 |
>| | 3 | 2 | 35_microweb1.31 .zip | 1.000 |
>| | 4 | 2 | 35_microweb1.31[1].zip | 1.234 |
>| | 5 | 2 | 35_microweb1.31[2].zip | 1.345 |
>| | 6 | 2 | 35_microweb1.31[3].zip | 1.456 |
>| +-------+--------+------------------------------+------------+
>| 6 rows in set (0.00 sec)
>|
>| mysql> select f.resID, f.FLEName, f.VersionNbr
>| -> from files f inner join programs p using (progID)
>| -> where f.VersionNbr =
>| -> (select max(VersionNbr) from files where progID = f.progID);
>| +-------+------------------------------+------------+
>| | resID | FLEName | VersionNbr |
>| +-------+------------------------------+------------+
>| | 2 | 35_laserdatandr ia2104[1].zip | 1.230 |
>| | 6 | 35_microweb1.31[3].zip | 1.456 |
>| +-------+------------------------------+------------+
>| 2 rows in set (0.00 sec)
>|
>| mysql>[/color]

I really appreciate the work you put into this, thank you.

Unfortunately I'm forced to use version 4.0.19 which doesn't allow the
second select statement :-(
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Markus Popp** · Oct 28 '05, 01:35 PM

Re: Returning unique records

You can leave the foreign key ... and engine=... away - then it should work.
However, you will have problems with the subquery at the end - but it can be
rewritten as a join. I'll add the correct join then ;-).

Markus

**Markus Popp** · Oct 28 '05, 02:06 PM

Re: Returning unique records

I fear I was wrong - whenever I try to bring the filename and the latest
version number together, I need a subquery.

It's possible to query the latest version for each program, though:

select p.programName, max(f.VersionNb r) as latestVersionNb r
from programs p inner join files f using (progID)
group by p.programName

Markus

**Jeff North** · Oct 28 '05, 10:05 PM

Re: Returning unique records

On Fri, 28 Oct 2005 15:54:24 +0200, in mailing.databas e.mysql "Markus
Popp" <mfp@gmx.li> wrote:
[color=blue]
>| I fear I was wrong - whenever I try to bring the filename and the latest
>| version number together, I need a subquery.
>|
>| It's possible to query the latest version for each program, though:
>|
>| select p.programName, max(f.VersionNb r) as latestVersionNb r
>| from programs p inner join files f using (progID)
>| group by p.programName[/color]

Thanks for all your help and time you've spent on this, it is greatly
appreciated.

I will try out your recommendations later on as at the moment I'm
snowed under with other items.
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

**Phil Nospam** · Oct 29 '05, 04:45 PM

Re: Returning unique records

"Jeff North" <jnorthau@yahoo .com.au> wrote in message
news:hmh1m1ppuf 5o6ttlq2288koqa je28muc3v@4ax.c om...[color=blue]
> I'm stumped and my brains are fried!!!!
>
> I have the following data
> +-------+------------------------------+------------+
> | resID | FLEName | VersionNbr |
> +-------+------------------------------+------------+
> | 1 | 35_laserdatandr ia2104.zip | 1.0 |
> | 2 | 35_laserdatandr ia2104[1].zip | 1.23 |
> | 3 | 35_microweb1.31 .zip | 1.0 |
> | 4 | 35_microweb1.31[1].zip | 1.234 |
> | 5 | 35_microweb1.31[2].zip | 1.345 |
> | 6 | 35_microweb1.31[3].zip | 1.456 |
> +-------+------------------------------+------------+
>
> I need to be able to return only resID 2 and 6.
>
> Can anybody give me any directions on how to do this?[/color]

"Aggro" <spammerdream@y ahoo.com> wrote in message
news:3qc8f.444$ z64.411@read3.i net.fi...[color=blue]
> Jeff North wrote:
>[color=green]
> > I need to retrieve the highest version number and/or the lates file
> > i.e. myfile[3].zip.[/color]
>
> If it is possible to change the table structure I suggest doing that, as
> other have mentioned (by adding extra column to tell program id). Other
> solutions can be slow, hard to implement or cause errors in production
> and are not suggested to be used.
>
> You can use alter table -command to add columns to existing databases,
> if that database is already in production. That way you won't lose any
> existing data.[/color]

Aggro has the right idea, but the table structure might not need to be
changed. Are there more fields in the table than you've listed here? I
only ask because many people only list the fields that they think are
relevant, ommitting fields that they feel would clutter the posting here on
the newsgroup. Your table looks like it lists the names of the zip files
that are updates to some programs. If you already have another field that
identifies the name of the program (not the name of the file), it MAY be
possible to achieve your desired results without changing the table
structure. An example follows:

+------+----------+------------------------------+------------+
| resID |ProgName |FLEName | VersionNbr |
+------+----------+------------------------------+------------+
| 1 | LaserData |35_laserdatand ria2104.zip | 1.0 |
| 2 | LaserData |35_laserdatand ria2104[1].zip | 1.23 |
| 3 | MicroWeb |35_microweb1.3 1.zip | 1.0 |
| 4 | MicroWeb |35_microweb1.3 1[1].zip | 1.234 |
| 5 | MicroWeb |35_microweb1.3 1[2].zip | 1.345 |
| 6 | MicroWeb |35_microweb1.3 1[3].zip | 1.456 |
+------+----------+------------------------------+------------+

Set your query to retrieve the FLEName (and other fields as needed) for the
ProgName (all of them or for whichever program you're interested in at the
time) with the highest version number.

If the field does not exist, adding it is the best solution - this may be
easy or difficult depending on the current length of your existing table.

Phil

**Jeff North** · Oct 29 '05, 10:55 PM

Re: Returning unique records

On Sat, 29 Oct 2005 16:33:51 GMT, in mailing.databas e.mysql "Phil
Nospam" <philnospam@don twantnospam.com > wrote:
[color=blue]
>| "Jeff North" <jnorthau@yahoo .com.au> wrote in message
>| news:hmh1m1ppuf 5o6ttlq2288koqa je28muc3v@4ax.c om...
>| > I'm stumped and my brains are fried!!!!
>| >
>| > I have the following data
>| > +-------+------------------------------+------------+
>| > | resID | FLEName | VersionNbr |
>| > +-------+------------------------------+------------+
>| > | 1 | 35_laserdatandr ia2104.zip | 1.0 |
>| > | 2 | 35_laserdatandr ia2104[1].zip | 1.23 |
>| > | 3 | 35_microweb1.31 .zip | 1.0 |
>| > | 4 | 35_microweb1.31[1].zip | 1.234 |
>| > | 5 | 35_microweb1.31[2].zip | 1.345 |
>| > | 6 | 35_microweb1.31[3].zip | 1.456 |
>| > +-------+------------------------------+------------+
>| >
>| > I need to be able to return only resID 2 and 6.
>| >
>| > Can anybody give me any directions on how to do this?
>|
>|
>| "Aggro" <spammerdream@y ahoo.com> wrote in message
>| news:3qc8f.444$ z64.411@read3.i net.fi...
>| > Jeff North wrote:
>| >
>| > > I need to retrieve the highest version number and/or the lates file
>| > > i.e. myfile[3].zip.
>| >
>| > If it is possible to change the table structure I suggest doing that, as
>| > other have mentioned (by adding extra column to tell program id). Other
>| > solutions can be slow, hard to implement or cause errors in production
>| > and are not suggested to be used.
>| >
>| > You can use alter table -command to add columns to existing databases,
>| > if that database is already in production. That way you won't lose any
>| > existing data.
>|
>| Aggro has the right idea, but the table structure might not need to be
>| changed. Are there more fields in the table than you've listed here? I
>| only ask because many people only list the fields that they think are
>| relevant, ommitting fields that they feel would clutter the posting here on
>| the newsgroup.[/color]

You're right. The table consists of:

# Host: localhost
# Database: elvass
# Table: 'competency_res ources'
#
CREATE TABLE `competency_res ources` (
`ResID` bigint(20) unsigned NOT NULL auto_increment,
`fkTP` int(10) unsigned NOT NULL default '0',
`cid` bigint(20) unsigned NOT NULL default '0',
`EntryType` varchar(5) NOT NULL default '',
`FLEName` varchar(255) NOT NULL default '',
`FLEDescription ` varchar(255) NOT NULL default '',
`FLEsize` bigint(20) unsigned NOT NULL default '0',
`FLEType` varchar(100) NOT NULL default '',
`FLEencodeStrin g` varchar(255) NOT NULL default '',
`VersionNbr` varchar(100) NOT NULL default '',
`AccessBy` enum('TEACHER', 'STUDENT') NOT NULL default 'TEACHER',
`ShareName` varchar(100) NOT NULL default '',
`Editable` enum('Yes','No' ) NOT NULL default 'Yes',
`EntryDate` date NOT NULL default '0000-00-00',
`EntryTime` time NOT NULL default '00:00:00',
PRIMARY KEY (`ResID`),
KEY `residx` (`cid`)
) TYPE=MyISAM COMMENT='Storag e of associated files for competency';

[color=blue]
>| Your table looks like it lists the names of the zip files
>| that are updates to some programs.[/color]

You're close :-)
The table holds information about files that have been uploaded by the
user. No file can be overwriten thus the square bracket, number,
square bracket, giving each file a unique number.

I've only included zip files but the extension could be anyone of a
multitude of allowable file extensions.
[color=blue]
>| If you already have another field that
>| identifies the name of the program (not the name of the file), it MAY be
>| possible to achieve your desired results without changing the table
>| structure. An example follows:
>|
>| +------+----------+------------------------------+------------+
>| | resID |ProgName |FLEName | VersionNbr |
>| +------+----------+------------------------------+------------+
>| | 1 | LaserData |35_laserdatand ria2104.zip | 1.0 |
>| | 2 | LaserData |35_laserdatand ria2104[1].zip | 1.23 |
>| | 3 | MicroWeb |35_microweb1.3 1.zip | 1.0 |
>| | 4 | MicroWeb |35_microweb1.3 1[1].zip | 1.234 |
>| | 5 | MicroWeb |35_microweb1.3 1[2].zip | 1.345 |
>| | 6 | MicroWeb |35_microweb1.3 1[3].zip | 1.456 |
>| +------+----------+------------------------------+------------+
>|
>| Set your query to retrieve the FLEName (and other fields as needed) for the
>| ProgName (all of them or for whichever program you're interested in at the
>| time) with the highest version number.
>|
>| If the field does not exist, adding it is the best solution - this may be
>| easy or difficult depending on the current length of your existing table.[/color]

Thanks for the info. Will try it out.
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

Returning unique records

Returning unique records

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment