[SOLVED]Re: Query to Select records in the last 4 weeks
Hi, I finally found the solution "SELECT count( smsc_id ) AS total, week( insertdate ) AS tanggal FROM momtbak WHERE insertdate BETWEEN DATE_SUB( CURRENT_DATE( ) , INTERVAL 4 WEEK ) AND CURRENT_DATE( ) GROUP BY week( insertdate )" Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to Select records in the last 4 weeks
Hi, I have tried to use this query: "SELECT count(smsc_id) as total, insertdate FROM momtbak WHERE insertdate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) AND CURRENT_DATE() group by week(date_format(insertdate,'%Y-%m-%d'),3)" to group records in the last 4 weeks by week. But the result returns this list: 144 2008-11-06 07:00:24 1883 2008-11-10 07:00:06 1645 2008-11-17 11:59:46 2476 2008-11-24 21:54:11 1015 2008-12-01 20:45:43 The expected result is the date shown above is the weeknumber. What do I miss here? TIA Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to Select records in the last 4 weeks
Hi, Thanks for the prompt reply. I have tested yours and it seems to be working. What about to group the result by week? TIA. Willy Your life would be very empty if you had nothing to regret. -Original Message- From: Daevid Vincent <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Cc: sangprabv <[EMAIL PROTECTED]> Subject: Re: Query to Select records in the last 4 weeks Date: Wed, 03 Dec 2008 17:52:32 -0800 SELECT * FROM momtbak WHERE insertdate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) AND CURRENT_DATE(); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to Select records in the last 4 weeks
On Thu, 2008-12-04 at 08:27 +0700, sangprabv wrote: > Hi, > I get stuck to build a query to select records between curdate() and the > last 4 weeks and groupped by week. I tested with: > > "SELECT * > FROM momtbak > WHERE insertdate > BETWEEN curdate( ) > AND curdate( ) - INTERVAL 4 week" > > It doesn't work. Please help, TIA. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-add Untested, but something like this maybe? "SELECT * FROM momtbak WHERE insertdate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) AND CURRENT_DATE();"
Query to Select records in the last 4 weeks
Hi, I get stuck to build a query to select records between curdate() and the last 4 weeks and groupped by week. I tested with: "SELECT * FROM momtbak WHERE insertdate BETWEEN curdate( ) AND curdate( ) - INTERVAL 4 week" It doesn't work. Please help, TIA. Willy Every why hath a wherefore. -- William Shakespeare, "A Comedy of Errors" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to select...
On Tue, 24 May 2005 [EMAIL PROTECTED] wrote: >Selon Dan Bolser <[EMAIL PROTECTED]>: > >> >> Hello, >> >> I have data like this >> >> PK GRP_COL >> 1A >> 2A >> 3A >> 4B >> 5B >> 6B >> 7C >> 8C >> 9C >> >> >> And I want to write a query to select data like this... >> >> PK FK GRP_COL >> 11 A >> 21 A >> 31 A >> 44 B >> 54 B >> 64 B >> 77 C >> 87 C >> 97 C >> >> >> Where FK is a random (or otherwise) member of PK from within the >> appropriate group given by GRP_COL. FK recreates the grouping from >> GRP_COL, but in terms of PK. I want to do this because GRP_COL is >> difficult to handle and I want to re-represent the grouping in terms of PK >> (this allows me to link data into the grouping more easily). >> >> Is there a simple way to do this? Sorry about the column names above (something in my head). Here is my favorite answer... SET @i=0, @row=''; SELECT *, -- Data table IF(@row=GRP_COL, @i, @i:[EMAIL PROTECTED]) AS FK, @row:=GRP_COL AS DROP_ME_LATER FROM data ORDER BY GRP_COL-- Essential for the logic used ; http://dev.mysql.com/doc/mysql/en/variables.html (John Belamaric) Having the FK column taken from the PK column was clearly not necessary (thanks all again for pointers). Somehow in the distant memory of my brain this is the answer I was looking for (and finally found). I like this answer because I hate that half of SQL which ALTERS tables and I have a neurotic fear of UPDATES accross JOINS that infected my nightmares as a youth! Strange I know, but its late and time for me to sleep /(xOx)/ Pleasant dreams! Dan. >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to select...
>I agree, especially with the additional information the OP provided about >his REAL table structure. A separate groups table makes better sense. > >Let this be an object lesson to others looking for assistance: If you want >timely and useful assistance, provide real and complete information >whenever possible. SHOW CREATE TABLE gives much better information than >DESCRIBE or SHOW COLUMNS. If you do chose to obfuscate your real table and >data layout then be prepared to translate whatever advice you receive. Eeep! I often try to simplify my problem to the bare bones before asking a question, as it is often quite tricky to work out what you "really want to do (tm)" and put it in its simplest form - also I often find that doing this gives me the answer, and I can just delete my email before I ever send it! >Sorry it took so long to get to the bottom of this design issue. Now to work out what I "really want to do" ;) - I will look over the answers and see if I am any closer... Thanks all for the help. >Shawn Green >Database Administrator >Unimin Corporation - Spruce Pine > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to select...
Michael Stassen <[EMAIL PROTECTED]> wrote on 05/24/2005 10:26:14 AM: > [EMAIL PROTECTED] wrote: > > > Dan Bolser <[EMAIL PROTECTED]> wrote on 05/24/2005 06:08:32 AM: > > > >>Hello, > >> > >>I have data like this > >> > >>PK GRP_COL > >>1 A > >>2 A > >>3 A > >>4 B > >>5 B > >>6 B > >>7 C > >>8 C > >>9 C > >> > >>And I want to write a query to select data like this... > >> > >>PK FK GRP_COL > >>1 1 A > >>2 1 A > >>3 1 A > >>4 4 B > >>5 4 B > >>6 4 B > >>7 7 C > >>8 7 C > >>9 7 C > >> > >>Where FK is a random (or otherwise) member of PK from within the > >>appropriate group given by GRP_COL. FK recreates the grouping from > >>GRP_COL, but in terms of PK. I want to do this because GRP_COL is > >>difficult to handle and I want to re-represent the grouping in terms of > > > > PK > > > >>(this allows me to link data into the grouping more easily). > >> > >>Is there a simple way to do this? > > > > I don't understand your choice of column name for your new column. PK > > generally means PRIMARY KEY and FK generally means FOREIGN KEY. FOREIGN > > KEYs are used to enforce relational data integrity between tables. What it > > looks like you want to do is to tag every row in a group with the lowest > > (minimum) PK value for that group. To me, that is not a FK. > > > > To do what you want will either take a subquery or a separate table. I > > think the separate table will perform faster so I will show you that > > method. > > > > CREATE TEMPORARY TABLE tmpPK (KEY GRP_COL) > > SELECT GRP_COL,MIN(PK) as minpk > > FROM datatable > > GROUP BY GRP_COL; > > > > ALTER TABLE datatable ADD COLUMN FK INT; > > > > UPDATE datatable > > INNER JOIN tmpPK > > ON tmpPK.GRP_COL = datatable.GRP_COL > > SET datatable.FK = tmpPK.minpk; > > > > DROP TEMPORARY TABLE tmpPK; > > > > The slowest part of all of this will be adding the column to your table. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > I was writing a similar answer, but you beat me to it. > > This is the right idea, but I don't think you go far enough. This > solution does what Dan asks, but I don't think it's what he really > needs. Surely, the problem is that the data isn't normalized. GRP_COL > contains repeated strings and is "difficult to handle". I'd suggest a > permanent, rather than temporary, fix. Something like: > >CREATE TABLE groups >(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) >SELECT DISTINCT GRP_COL AS group_name >FROM datatable; > >ALTER TABLE datatable ADD grp_id INT UNSIGNED NOT NULL; > >UPDATE datatable >JOIN groups ON datatable.GRP_COL = groups.group_name >SET datatable.grp_id = groups.id; > > Check first, then > >ALTER TABLE datatable DROP COLUMN GRP_COL; > > Now you join to the new groups table when you need the group name. > > Michael > I agree, especially with the additional information the OP provided about his REAL table structure. A separate groups table makes better sense. Let this be an object lesson to others looking for assistance: If you want timely and useful assistance, provide real and complete information whenever possible. SHOW CREATE TABLE gives much better information than DESCRIBE or SHOW COLUMNS. If you do chose to obfuscate your real table and data layout then be prepared to translate whatever advice you receive. Sorry it took so long to get to the bottom of this design issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query to select...
Hi, I read "FK is a random (or otherwise)" But i prefer your help !! Selon Michael Stassen <[EMAIL PROTECTED]>: > > How on earth will that help? What does the ASCII (byte) code of GRP_COL > have to do with what Dan wants? > > [EMAIL PROTECTED] wrote: > > > Hi, > > try for axample, > > > > select PK, ord(GRP_COL),COL from your_table; > > > > > > Mathias > > > > Selon Dan Bolser <[EMAIL PROTECTED]>: > > > > > >>Hello, > >> > >>I have data like this > >> > >>PK GRP_COL > >>1 A > >>2 A > >>3 A > >>4 B > >>5 B > >>6 B > >>7 C > >>8 C > >>9 C > >> > >> > >>And I want to write a query to select data like this... > >> > >>PK FK GRP_COL > >>1 1 A > >>2 1 A > >>3 1 A > >>4 4 B > >>5 4 B > >>6 4 B > >>7 7 C > >>8 7 C > >>9 7 C > >> > >> > >>Where FK is a random (or otherwise) member of PK from within the > >>appropriate group given by GRP_COL. FK recreates the grouping from > >>GRP_COL, but in terms of PK. I want to do this because GRP_COL is > >>difficult to handle and I want to re-represent the grouping in terms of PK > >>(this allows me to link data into the grouping more easily). > >> > >>Is there a simple way to do this? > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to select...
[EMAIL PROTECTED] wrote: Dan Bolser <[EMAIL PROTECTED]> wrote on 05/24/2005 06:08:32 AM: Hello, I have data like this PK GRP_COL 1 A 2 A 3 A 4 B 5 B 6 B 7 C 8 C 9 C And I want to write a query to select data like this... PK FK GRP_COL 1 1 A 2 1 A 3 1 A 4 4 B 5 4 B 6 4 B 7 7 C 8 7 C 9 7 C Where FK is a random (or otherwise) member of PK from within the appropriate group given by GRP_COL. FK recreates the grouping from GRP_COL, but in terms of PK. I want to do this because GRP_COL is difficult to handle and I want to re-represent the grouping in terms of PK (this allows me to link data into the grouping more easily). Is there a simple way to do this? I don't understand your choice of column name for your new column. PK generally means PRIMARY KEY and FK generally means FOREIGN KEY. FOREIGN KEYs are used to enforce relational data integrity between tables. What it looks like you want to do is to tag every row in a group with the lowest (minimum) PK value for that group. To me, that is not a FK. To do what you want will either take a subquery or a separate table. I think the separate table will perform faster so I will show you that method. CREATE TEMPORARY TABLE tmpPK (KEY GRP_COL) SELECT GRP_COL,MIN(PK) as minpk FROM datatable GROUP BY GRP_COL; ALTER TABLE datatable ADD COLUMN FK INT; UPDATE datatable INNER JOIN tmpPK ON tmpPK.GRP_COL = datatable.GRP_COL SET datatable.FK = tmpPK.minpk; DROP TEMPORARY TABLE tmpPK; The slowest part of all of this will be adding the column to your table. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I was writing a similar answer, but you beat me to it. This is the right idea, but I don't think you go far enough. This solution does what Dan asks, but I don't think it's what he really needs. Surely, the problem is that the data isn't normalized. GRP_COL contains repeated strings and is "difficult to handle". I'd suggest a permanent, rather than temporary, fix. Something like: CREATE TABLE groups (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT DISTINCT GRP_COL AS group_name FROM datatable; ALTER TABLE datatable ADD grp_id INT UNSIGNED NOT NULL; UPDATE datatable JOIN groups ON datatable.GRP_COL = groups.group_name SET datatable.grp_id = groups.id; Check first, then ALTER TABLE datatable DROP COLUMN GRP_COL; Now you join to the new groups table when you need the group name. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to select...
On Tue, 24 May 2005 [EMAIL PROTECTED] wrote: >Hi, >try for axample, > >select PK, ord(GRP_COL),COL from your_table; When I said my data was 'like' the below I should have been more specific. Here is some of my data (with reference to the simplified data (and question details) below); INPUT: <PK-> <--GRP_COL> +--+-+-++ | PDB | ASS | LIST| COUNTS | +--+-+-++ | 104l | 1 | 104lA | 1 | | 104l | 2 | 104lA | 1 | | 104m | 1 | 104m- | 1 | | 105m | 1 | 104m- | 1 | | 117e | 1 | 117eA | 2 | | 11ba | 1 | 11baA | 2 | | 11bg | 1 | 11baA | 2 | | 12e8 | 1 | 12e8H,12e8L | 2,2| | 12e8 | 2 | 12e8H,12e8L | 2,2| ... OUTPUT (wanted): <FK> <PK-> +-+-+--+-+ | REP_PDB | REP_ASS | PDB | ASS | +-+-+--+-+ | 104l| 1 | 104l | 1 | | 104l| 1 | 104l | 2 | | 104m| 1 | 104m | 1 | | 104m| 1 | 105m | 1 | | 117e| 1 | 117e | 1 | | 11ba| 1 | 11ba | 1 | | 11ba| 1 | 11bg | 1 | | 12e8| 1 | 12e8 | 1 | | 12e8| 1 | 12e8 | 2 | ... Cheers, > > >Mathias > >Selon Dan Bolser <[EMAIL PROTECTED]>: > >> >> Hello, >> >> I have data like this >> >> PK GRP_COL >> 1A >> 2A >> 3A >> 4B >> 5B >> 6B >> 7C >> 8C >> 9C >> >> >> And I want to write a query to select data like this... >> >> PK FK GRP_COL >> 11 A >> 21 A >> 31 A >> 44 B >> 54 B >> 64 B >> 77 C >> 87 C >> 97 C >> >> >> Where FK is a random (or otherwise) member of PK from within the >> appropriate group given by GRP_COL. FK recreates the grouping from >> GRP_COL, but in terms of PK. I want to do this because GRP_COL is >> difficult to handle and I want to re-represent the grouping in terms of PK >> (this allows me to link data into the grouping more easily). >> >> Is there a simple way to do this? >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to select...
How on earth will that help? What does the ASCII (byte) code of GRP_COL have to do with what Dan wants? [EMAIL PROTECTED] wrote: Hi, try for axample, select PK, ord(GRP_COL),COL from your_table; Mathias Selon Dan Bolser <[EMAIL PROTECTED]>: Hello, I have data like this PK GRP_COL 1 A 2 A 3 A 4 B 5 B 6 B 7 C 8 C 9 C And I want to write a query to select data like this... PK FK GRP_COL 1 1 A 2 1 A 3 1 A 4 4 B 5 4 B 6 4 B 7 7 C 8 7 C 9 7 C Where FK is a random (or otherwise) member of PK from within the appropriate group given by GRP_COL. FK recreates the grouping from GRP_COL, but in terms of PK. I want to do this because GRP_COL is difficult to handle and I want to re-represent the grouping in terms of PK (this allows me to link data into the grouping more easily). Is there a simple way to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to select...
Dan Bolser <[EMAIL PROTECTED]> wrote on 05/24/2005 06:08:32 AM: > > Hello, > > I have data like this > > PK GRP_COL > 1 A > 2 A > 3 A > 4 B > 5 B > 6 B > 7 C > 8 C > 9 C > > > And I want to write a query to select data like this... > > PK FK GRP_COL > 1 1 A > 2 1 A > 3 1 A > 4 4 B > 5 4 B > 6 4 B > 7 7 C > 8 7 C > 9 7 C > > > Where FK is a random (or otherwise) member of PK from within the > appropriate group given by GRP_COL. FK recreates the grouping from > GRP_COL, but in terms of PK. I want to do this because GRP_COL is > difficult to handle and I want to re-represent the grouping in terms of PK > (this allows me to link data into the grouping more easily). > > Is there a simple way to do this? > > I don't understand your choice of column name for your new column. PK generally means PRIMARY KEY and FK generally means FOREIGN KEY. FOREIGN KEYs are used to enforce relational data integrity between tables. What it looks like you want to do is to tag every row in a group with the lowest (minimum) PK value for that group. To me, that is not a FK. To do what you want will either take a subquery or a separate table. I think the separate table will perform faster so I will show you that method. CREATE TEMPORARY TABLE tmpPK (KEY GRP_COL) SELECT GRP_COL,MIN(PK) as minpk FROM datatable GROUP BY GRP_COL; ALTER TABLE datatable ADD COLUMN FK INT; UPDATE datatable INNER JOIN tmpPK ON tmpPK.GRP_COL = datatable.GRP_COL SET datatable.FK = tmpPK.minpk; DROP TEMPORARY TABLE tmpPK; The slowest part of all of this will be adding the column to your table. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query to select...
Hi, try for axample, select PK, ord(GRP_COL),COL from your_table; Mathias Selon Dan Bolser <[EMAIL PROTECTED]>: > > Hello, > > I have data like this > > PKGRP_COL > 1 A > 2 A > 3 A > 4 B > 5 B > 6 B > 7 C > 8 C > 9 C > > > And I want to write a query to select data like this... > > PKFK GRP_COL > 1 1 A > 2 1 A > 3 1 A > 4 4 B > 5 4 B > 6 4 B > 7 7 C > 8 7 C > 9 7 C > > > Where FK is a random (or otherwise) member of PK from within the > appropriate group given by GRP_COL. FK recreates the grouping from > GRP_COL, but in terms of PK. I want to do this because GRP_COL is > difficult to handle and I want to re-represent the grouping in terms of PK > (this allows me to link data into the grouping more easily). > > Is there a simple way to do this? > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query to select...
Hello, I have data like this PK GRP_COL 1 A 2 A 3 A 4 B 5 B 6 B 7 C 8 C 9 C And I want to write a query to select data like this... PK FK GRP_COL 1 1 A 2 1 A 3 1 A 4 4 B 5 4 B 6 4 B 7 7 C 8 7 C 9 7 C Where FK is a random (or otherwise) member of PK from within the appropriate group given by GRP_COL. FK recreates the grouping from GRP_COL, but in terms of PK. I want to do this because GRP_COL is difficult to handle and I want to re-represent the grouping in terms of PK (this allows me to link data into the grouping more easily). Is there a simple way to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query to select only numeric portion of string
Using MySQL 4.0.20, I need to extract just the numeric portion of a string in a field and move it to another field in the same table, but I'd only like to do this if the value actually starts with a number. So, what I'm looking to accomplish is: UPDATE table SET field2 = VOODOO( field1 ) The fields are currently defined as: field2 INT( 10 ) field1 CHAR( 19 ) The table is relatively small, with about 55,000 records in it. Here is the type of data I find in field1 and next to it, what I'd like to wind up with in field2: 1234 -> 1234 12345 -> 12345 123456 -> 123456 1234567 ->1234567 1234NN -> 1234 12345NN ->12345 123456N -> 123456 1234567 -> 1234567 WWW -> WWW NC -> NC There is other data, but most of it follows that rule. If I can do this with MySQL, I would prefer it. If it's something I should farm out to PHP or Perl, I can do that, but I was trying to think of how to accomplish this just within MySQL and don't really know how to approach it. Any pointers would be appreciated. Thanks. Wes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write query to select the Max(version) for each unique file_name record?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sunday 20 July 2003 22:59, Tanamon wrote: > ++---+--+ > > | id | file_name | max(version) | > > ++---+--+ > > | 6 | ffour |2 | > | 1 | fone |3 | > | 5 | fthree|1 | > | 2 | ftwo |2 | > > ++---+--+ > 4 rows in set (0.02 sec) > > The query below returns the empty set. > > mysql> select id, file_name, max(version) from test group by file_name > having max(version); > Empty set (0.00 sec) Try this: Select id, file_name, max(version) from test group by file_name; The "having" clause in your query is redundant as the max() aggregate already limits it to the maximum value. - ---Michael -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQE/G8GujeziQOokQnARArVPAJ9TdchBj8tExxitns8PkRSnGbMFugCgmhTW et7HpBp07nWh+KD//xqDLbM= =p8/8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to write query to select the Max(version) for each unique file_name record?
Hello All, I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries below. Logically I want to use max(version) as a constraint in a Where Clause. However, the max() function is not allowed directly in a where clause. I have contemplated a second table to track the max version for each file name. I would like to structure the data in an efficient manner for query performance when the data set grows to many thousands of unique file_name records with many hundreds of versions each. Any guidance will be appreciated. David Oberlitner Sample data: mysql> select * from test; ++---+-+ | id | file_name | version | ++---+-+ | 1 | fone | 1 | | 2 | ftwo | 1 | | 3 | fone | 2 | | 4 | fone | 3 | | 5 | fthree| 1 | | 6 | ffour | 1 | | 7 | ftwo | 2 | | 8 | ffour | 2 | ++---+-+ The query below gets close in that it returns each file name and its max(version), however it returns the ID field associated with the first record instance of file_name and not the ID associated with the max(version) instance of the file_name record. mysql> select id, file_name, max(version) from test group by file_name; ++---+--+ | id | file_name | max(version) | ++---+--+ | 6 | ffour |2 | | 1 | fone |3 | | 5 | fthree|1 | | 2 | ftwo |2 | ++---+--+ 4 rows in set (0.02 sec) The query below returns the empty set. mysql> select id, file_name, max(version) from test group by file_name having max(version); Empty set (0.00 sec) Additionally, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
