Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Select record with MAX value in MySQL

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Select record with MAX value in MySQL
# 1  
Old 06-25-2010
Question Select record with MAX value in MySQL
Hi there,

I have trouble selecting record that contain one biggest value for a group of other values. I mean, this is my table:
Code:
mysql> SELECT * FROM b;
+----+------+-------+
| id | user | value |
+----+------+-------+
|  1 |    1 |   100 |
|  3 |    1 |   150 |
|  5 |    1 |   300 |
|  6 |    2 |    10 |
|  7 |    2 |    80 |
|  8 |    2 |   880 |
+----+------+-------+

I want to SELECT one line per user. The one with the bigest value (in red).
I tried:
Code:
mysql> SELECT id, user, MAX(value) FROM b GROUP BY user;
+----+------+------------+
| id | user | MAX(value) |
+----+------+------------+
|  1 |    1 |        300 |
|  6 |    2 |        880 |
+----+------+------------+

But as you can see, it is completely wrong. Indeed it returns the biggest value per user but not the corresponding record.
How can I do that?
Thanks for your help
Santiago
# 2  
Old 06-25-2010
This is an expected behavior (see this article).

You could use something like this:

Code:
select b.id, b.user, b.value 
from b, (
  select user, max(value) value 
  from b
  group by user
  ) m
where b.value = m.value
and b.user = m.user;

Last edited by radoulov; 06-25-2010 at 08:17 AM..
# 3  
Old 06-29-2010
Thanks radoulov,
It takes a while to process the nested query with 350,000 records but it looks likes this is the only way.
# 4  
Old 06-29-2010
How did you define the indexes on the table?
Could you also post the explain [plan] of the statement?
# 5  
Old 06-29-2010
thanks radoulov for your interest.
As you can imagine, my real problem is far more complex than a tables called b with just 2 field.

In the real world, I have a big table containing a huge number of uploaded files. All files were archived (tgz) and then uploaded. So I have I directory loaded with tons of tgz files. Some of those tgz actually contain the same file but uploaded at two different times. I will keep all files to be able to recover versions. But I'm first interested in the list of files (not tgz files but the files they contain) in their latest status (mod, owner, group). I hope this is clear enough.

This is my real table:
Code:
mysql> DESCRIBE uploaded;
+------------+------------------+------+-----+---------+----------------------------------------------+
| Field      | Type             | Null | Key | Default | Extra                                        |
+------------+------------------+------+-----+---------+----------------------------------------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment                               |
| tgz_path   | varchar(512)     | NO   |     | NULL    | path to a tgz file                           |
| file_mod   | varchar(9)       | YES  |     | NULL    | mod of the file inside the tgz               |
| file_owner | varchar(32)      | YES  |     | NULL    | owner of the file inside the tgz             |
| file_group | varchar(32)      | YES  |     | NULL    | group of the file inside the tgz             |
| file_time  | datetime         | YES  |     | NULL    | modification time of the file inside the tgz |
| file_name  | varchar(512)     | NO   |     | NULL    | path of the file inside the tgz              |
+------------+------------------+------+-----+---------+----------------------------------------------+

And this is a sample of what it contains:
Code:
+----+---------------------+-----------+------------+------------+---------------------+--------------------------+
| id | tgz_path            | file_mod  | file_owner | file_group | file_time           | file_name                |
+----+---------------------+-----------+------------+------------+---------------------+--------------------------+
|  1 | /home/ftp/18027.tgz | rw-r----- | root       | root       | 2010-06-28 10:11:51 | /home/santiago/doc.txt   |
|  2 | /home/ftp/8400.tgz  | rw-r--r-- | santiago   | santiago   | 2010-06-29 17:37:29 | /home/santiago/doc.txt   |
|  3 | /home/ftp/18695.tgz | rw-rw-r-- | root       | root       | 2010-06-27 11:42:56 | /usr/local/bin/script.sh |
|  4 | /home/ftp/27750.tgz | rwxr-x--- | foo        | foo        | 2010-06-29 00:53:32 | /usr/local/bin/script.sh |
|  5 | /home/ftp/4333.tgz  | rwxrwxr-x | foo        | foo        | 2010-06-28 09:17:14 | /usr/local/bin/script.sh |
+----+---------------------+-----------+------------+------------+---------------------+--------------------------+

As you can see, files have changed mod and owner over time. I highlighted in red the latest record per file.

Then I have two other tables: files and versions
Code:
mysql> DESCRIBE files;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| file_mod   | varchar(9)       | NO   |     | NULL    |                |
| file_owner | varchar(32)      | NO   |     | NULL    |                |
| file_group | varchar(32)      | NO   |     | NULL    |                |
| file_time  | datetime         | NO   |     | NULL    |                |
| file_name  | varchar(512)     | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
mysql> DESCRIBE versions;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| file_id   | int(10) unsigned | NO   |     | NULL    |                |
| file_time | datetime         | NO   |     | NULL    |                |
| path      | varchar(512)     | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

I want to save in table files the latest record per file. I first INSERT new record:
Code:
mysql> INSERT IGNORE INTO files (file_mod, file_owner, file_group, file_time, file_name)
SELECT file_mod, file_owner, file_group, file_time, file_name
FROM uploaded
GROUP BY file_name;

and then UPDATE records with the latest statistics. That's where my query comes:
Code:
mysql> UPDATE files JOIN (
    SELECT uploaded.file_mod, uploaded.file_owner, uploaded.file_group, uploaded.file_time, uploaded.file_name
    FROM uploaded
    JOIN (SELECT MAX(file_time) AS file_time, file_name
          FROM uploaded
          GROUP BY file_name) AS max_uploaded ON uploaded.file_time = max_uploaded.file_time AND uploaded.file_name = max_uploaded.file_name
    GROUP BY uploaded.file_time, uploaded.file_name) AS uploaded ON files.file_name = uploaded.file_name
SET files.is_folder  = uploaded.is_folder,
    files.file_mod   = uploaded.file_mod,
    files.file_owner = uploaded.file_owner,
    files.file_group = uploaded.file_group,
    files.file_time  = uploaded.file_time
WHERE uploaded.file_time > files.file_time;

That's it! Ask me if it's too messy.
Now you're right, to put some well choosen will probably blindingly increase the speed of the query. What do you think?
# 6  
Old 06-29-2010
From what I see, I'd put an index at least on uploaded.file_name and uploaded.file_time, and maybe files.file_name. You could run an EXPLAIN on the SELECT part, as that should show the biggest bottlenecks (probably the JOIN ON).
# 7  
Old 06-29-2010
Thanks for the advice.
You look pretty good with MySQL.
Could you answer this question?
If I create a unique index based on two columns:
Code:
ALTER TABLE t ADD UNIQUE INDEX unique_key (f1, f2)

Does it consequently indexes column f1 and column f2 or do I have to explicitly do:
Code:
ALTER TABLE t ADD UNIQUE INDEX unique_key (f1, f2), ADD INDEX (f1), ADD INDEX (f2)
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Script to record max memory usage of program

Hello, I am working on an application that uses allot of memory depending on the input. I am also working on more than one processing algorithm. The program has a long processing time (hours) so it would be nice to be able to monitor the maximum memory footprint of the application during runs... (1 Reply)
Discussion started by: LMHmedchem
1 Replies

2. Programming

MySQL select user with the same IP address

Dear community, I woul like to make a query to output all the users having the same IP address. The table is somethig like: name logged_ip ==== ========= user1 127.0.0.1 user2 127.0.0.2 user3 127.0.0.3 user4 127.0.0.1 user5 127.0.0.2 user6 127.0.0.5I used this query... (4 Replies)
Discussion started by: Lord Spectre
4 Replies

3. Shell Programming and Scripting

Select record having different value in second column

I want records which have more than one and different value in the second column on the below sample file. Ex, I have the samle file below :- XYZ 1 XYZ 3 abc 1 abc 1 qwe 2 qwe 1 qwe 3 I want to select XYZ and QWE line only. (6 Replies)
Discussion started by: Sanjeev Yadav
6 Replies

4. Programming

Getting Rows from a MySQL Table with max values?

I feel stupid for asking this because it seems that MYSQL code isn't working the way that I think it should work. Basically I wrote code like this: select * from `Test_DC_Trailer` HAVING max(DR_RefKey); Where the DR_RefKey is a unique numeric field that is auto iterated (like a primary key)... (7 Replies)
Discussion started by: Astrocloud
7 Replies

5. Shell Programming and Scripting

Max value Record from a File

I have a file with 3 columns I want to get the record with the max salary. If there are more than 1 record having the highest salary, i want any one of them. empid,ename,Sal 1,abc,100 2,def,200 3,xyz,300 4,pqr,100 5,mnq,300 Output Any record with 300 as salary Please use next... (1 Reply)
Discussion started by: traininfa
1 Replies

6. UNIX for Dummies Questions & Answers

Select max value based on filename

Hi, I would just like to know how to get the file with the max filename on a directory and get rid of all the others. For example, in directory A:/ i have the ff files: APPLE2001 APPLE2002 APPLE2003 GRAPE2004 what I want to get is the max in files whose filenames start with APPLE*,... (4 Replies)
Discussion started by: madden
4 Replies

7. UNIX for Advanced & Expert Users

mysql select query optimization..

hi.. i need to optimize my select query .. my situation is like this .. i have 15 lac recors in my table.. the following query takes nine seconds to give the required output.. SELECT max(ah.AUC_AMT), SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (1 Reply)
Discussion started by: senkerth
1 Replies

8. Shell Programming and Scripting

mysql select query optimization..

hi.. i need to optimize my select query .. my situation is like this .. i have 15 lac recors in my table.. the following query takes nine seconds to give the required output.. SELECT max(ah.AUC_AMT), SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (0 Replies)
Discussion started by: senkerth
0 Replies

9. Shell Programming and Scripting

Select Record based on First Column

Hi, I have a file with multiple records...and I have to select records based on first column....here is the sample file... I01,abc,125,1a2,LBVI02 I01,abc,126,2b5,LBVI02 I02,20070530,254,abc,LLBI01 I02,20070820,111,bvd,NGBI01 I need all records with I01 in first field in one file and... (8 Replies)
Discussion started by: mgirinath
8 Replies
Login or Register to Ask a Question