I have trouble selecting record that contain one biggest value for a group of other values. I mean, this is my table:
I want to SELECT one line per user. The one with the bigest value (in red).
I tried:
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
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:
And this is a sample of what it contains:
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
I want to save in table files the latest record per file. I first INSERT new record:
and then UPDATE records with the latest statistics. That's where my query comes:
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?
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).
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:
Does it consequently indexes column f1 and column f2 or do I have to explicitly do:
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)
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)
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)
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)
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)
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)
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)
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)
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)