Returning unique records

mailing.database.mysql - MySQL database issues discussed in this best MySQL group in USENET 

Subject Author Date Returning unique records Jeff North 10-27-2005  Re: Returning unique records Aggro 10-27-2005  Re: Returning unique records Markus Popp 10-27-2005  Re: Returning unique records Jeff North 10-28-2005  Re: Returning unique records Jeff North 10-27-2005  Re: Returning unique records Aggro 10-27-2005  Re: Returning unique records Phil Nospam 10-29-2005  Re: Returning unique records Jeff North 10-29-2005  Re: Returning unique records Markus Popp 10-27-2005  Re: Returning unique records Jeff North 10-28-2005  Re: Returning unique records Markus Popp 10-28-2005  Re: Returning unique records Markus Popp 10-28-2005  Re: Returning unique records Jeff North 10-28-2005

Posted by Jeff North on October 27, 2005, 12:35 pm Please log in for more thread options I'm stumped and my brains are fried!!!! I have the following data +-------+------------------------------+------------+ | resID | FLEName | VersionNbr | +-------+------------------------------+------------+ | 1 | 35_laserdatandria2104.zip | 1.0 | | 2 | 35_laserdatandria2104[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? Many thanks --------------------------------------------------------------- jnorthau@yourpantsyahoo.com.au : Remove your pants to reply --------------------------------------------------------------- Posted by Aggro on October 27, 2005, 10:06 am Please log in for more thread options Jeff North wrote: show/hide quoted text > I need to be able to return only resID 2 and 6. select resID from yourtablename where resID in(2,6); Posted by Markus Popp on October 27, 2005, 11:08 am Please log in for more thread options 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 Posted by Jeff North on October 28, 2005, 12:21 am Please log in for more thread options On Thu, 27 Oct 2005 17:08:30 +0200, in mailing.database.mysql "Markus show/hide quoted text >| I think that's not what the problem is. I guess, Jeff wants to get the >| filename with the latest version. Yep, sorry I didn't make myself clearer in my original post. show/hide quoted text >| 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. True, as not all entries will be suffixed with a number. show/hide quoted text >| 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. I was hoping to avoid this type of setup :-( --------------------------------------------------------------- jnorthau@yourpantsyahoo.com.au : Remove your pants to reply --------------------------------------------------------------- Posted by Jeff North on October 27, 2005, 1:38 pm Please log in for more thread options On Thu, 27 Oct 2005 14:06:51 GMT, in mailing.database.mysql Aggro show/hide quoted text >| Jeff North wrote: >| >| > I need to be able to return only resID 2 and 6. >| >| select resID from yourtablename where resID in(2,6); 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@yourpantsyahoo.com.au : Remove your pants to reply --------------------------------------------------------------- Similar Threads Posted Returning most recent records? January 15, 2006, 4:18 pm getting unique records November 23, 2006, 9:58 am All Records From Table A - All Records From Table B - Join Alike Records October 3, 2006, 11:24 am Full text not returning any results January 11, 2006, 5:45 pm Query for returning "nearby" rows? August 30, 2006, 4:54 pm full-text score returning 0 March 29, 2005, 7:35 pm MySQL 5 stored procedures: returning prematurely June 4, 2005, 7:19 am Syntax for select statement returning output? June 6, 2006, 7:50 am Returning Clock Hours in Time Range July 19, 2006, 8:05 pm returning relevane of match queries as a percentage August 4, 2006, 10:48 am