Getting Rows from a MySQL Table with max values?


 
Thread Tools Search this Thread
Top Forums Programming Getting Rows from a MySQL Table with max values?
# 1  
Old 05-02-2012
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:
Code:
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) every time I insert a line or lines into a table. I say like a primary key because if I insert multiple rows into the table then those rows all have the same DR_RefKey (There is a separate Primary key field)

Here is a sample table of what I am talking about:

PrimKey DR_RefKey name data
1 1 george 123
2 1 nick 445
3 2 fred 345
4 3 eric 234
5 4 susie 234
6 5 bill 1234
7 6 bob 654
8 6 rich 2344
9 7 stan 765
What I want (expect) is for the above sql code to return the bottom line ie

PrimKey DR_RefKey name data 9 7 stan 765 instead it gives the first line


How do get it to show the max value of the DR_RefKey, w multiple rows?

Here is a convenient table def with inserted values which will duplicate the above sample table:

Code:
#drop table if exists `Test_DC_Trailer`;
CREATE TABLE `Test_DC_Trailer` (
  `PrimKey` mediumint(9) NOT NULL auto_increment,
  `DR_RefKey` double default NULL,
  ` name` varchar(255) default NULL,
  `data` int(11) default NULL,
  PRIMARY KEY  (`PrimKey`)
);


insert into `Test_DC_Trailer` (`PrimKey`, `DR_RefKey`, ` name`, `data`)
Values (NULL, '1', ' george', '123')
, (NULL, '1', ' nick', '445')
, (NULL, '2', ' fred', '345')
, (NULL, '3', ' eric', '234')
, (NULL, '4', ' susie', '234')
, (NULL, '5', ' bill', '1234')
, (NULL, '6', ' bob', '654')
, (NULL, '6', ' rich', '2344')
, (NULL, '7', ' stan', '765');

Thanks in advance

---------- Post updated at 12:29 PM ---------- Previous update was at 11:57 AM ----------

I can do a subquery but I find that this code is ugly and/or less intuitive/readable

Code:
select * from `Test_DC_Trailer` where DR_RefKey = (Select Max(DR_RefKey) from `Test_DC_Trailer`)


Last edited by Astrocloud; 05-02-2012 at 01:04 PM.. Reason: Tables came out wrong, trying cut and paste from mysql directly
# 2  
Old 05-02-2012
Quote:
Originally Posted by Astrocloud
How do get it to show the max value of the DR_RefKey, w multiple rows?
It has to be something that, theoretically, can be done in a single pass.

It can find the highest value easily enough it needn't print anything until it's over. But to find all rows that match the highest value in one pass, it would need to be psychic -- it has to scan the entire table to figure out what the highest is. Once it's done that it's already printed all the results and can't start over.

Indexes and the like make searching and min/max a lot faster than brute-force table scans now, but they aren't used to make the database 'psychic' this way, because that'd mean inconsistent behavior.
This User Gave Thanks to Corona688 For This Post:
# 3  
Old 05-02-2012
I agree, full table scan will happen, and index on DR_RefKey desc is required
Tested this in Oracle
Code:
select * from Test_DC_Trailer where rownum=1 order by DR_RefKey desc

Equivalent in MySQL would be(not tested)
Code:
select * from Test_DC_Trailer where limit 1 order by DR_RefKey desc

# 4  
Old 05-02-2012
I'm not saying a full table-scan will happen as much as, the result of an indexed query can't be different than what a table scan could do. (Except in order, which isn't guaranteed anyway, ORDER BY sorts after the fact.) Therefore, it won't use information from the index to know the maximum in advance. Your second query, where you just 'select max(..) from ...' would be able to use the index.

I'm not sure index is required either, as much as the performance could be quite poor if it isn't.
# 5  
Old 05-02-2012
If we do "order by" than index is required.

my bad on the earlier query. Following will work, but it uses inline query
Code:
select * from ( select * Test_DC_Trailer order by DR_RefKey desc)  where rownum=1

# 6  
Old 05-02-2012
Quote:
Originally Posted by 47shailesh
If we do "order by" than index is required.
I've fixed extremely slow order-by queries by adding appropriate indexes before; without them, it was forced to do a table scan, save to a temporary table, and sort after-the-fact, each step of which I could watch individually with the appropriate commands(something like 'show query status').

"Very poor performance" is not the same thing as "doesn't work at all".
# 7  
Old 05-02-2012
Quote:
Originally Posted by Corona688
"Very poor performance" is not the same thing as "doesn't work at all".
I totally agree with you. I meant the performance will be better with index if table is quite big.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

Php number array from max, min, step size mysql data

I want to create a form with data values in a dropdown list. The values in the dropdown list need to be generated on the fly from max, min and increment values contained in a mysql database. Hopefully this makes sense, I really have no idea where to start :confused: Thanks (6 Replies)
Discussion started by: barrydocks
6 Replies

2. Shell Programming and Scripting

awk file to read values from Db2 table replacing hard coded values

Hi, I want to replace a chain of if-else statement in an old AWK file with values from Db2 table or CSV file. The part of code is below... if (start_new_rec=="true"){ exclude_user="false"; user=toupper($6); match(user, "XXXXX."); if (RSTART ==2 ) { ... (9 Replies)
Discussion started by: asandy1234
9 Replies

3. Shell Programming and Scripting

Median and max of duplicate rows

Hi all, plz help me with this, I want to to extract the duplicate rows (column 1) in a file which at least repeat 4 times. then I want to summarize them by getting the max , mean, median and min. The file is sorted by column 1, all the repeated rows appear together. If number of elements is... (5 Replies)
Discussion started by: ritakadm
5 Replies

4. Shell Programming and Scripting

Retrieve multiple rows from mysql and automatically create a table

Hi, i want to create a table automatically based on another table (sms_key). For example; If user create a new row with sms_keyword field: IRC then a table created automatically (with some field on it, like: name, ph_number, messages). select * from sms_key; +-------------+ |... (1 Reply)
Discussion started by: jazzyzha
1 Replies

5. Shell Programming and Scripting

Query the table and return values to shell script and search result values from another files.

Hi, I need a shell script, which would search the result values from another files. 1)execute " select column1 from table_name" query on the table. 2)Based on the result, need to be grep from .wft files. could please explain about this.Below is the way i am using. #!/bin/sh... (4 Replies)
Discussion started by: Rami Reddy
4 Replies

6. UNIX for Dummies Questions & Answers

get max value every 4 rows between 2 column

Hi all I have a file that has two columns and I need the maximum value in column 2 of 4 positions o rows. for example at position {1..3} there are 4 characters (A, C, G and T) each of these characters with a value with a value in column 2. I need the maximum value in column 2 and the corresponding... (2 Replies)
Discussion started by: xinox
2 Replies

7. Shell Programming and Scripting

Compare values in two files. For matching rows print corresponding values from File 1 in File2.

- I have two files (File 1 and File 2) and the contents of the files are mentioned below. - I am trying to compare the values of Column1 of File1 with Column1 of File2. If a match is found, print the corresponding value from Column2 of File1 in Column5 of File2. - I tried to modify and use... (10 Replies)
Discussion started by: Santoshbn
10 Replies

8. Shell Programming and Scripting

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: mysql> SELECT * FROM b; +----+------+-------+ | id | user | value | +----+------+-------+ | 1 | 1 | 100 | | 3 | 1 | 150 | | 5 | 1 | 300 | | 6... (20 Replies)
Discussion started by: chebarbudo
20 Replies

9. Shell Programming and Scripting

select values from db1 table and insert into table of DB2

Hi I am having three oracle databases running in three different machine. their ip address is different. from one of the DB am able to access both the databases.(means am able to select values and insert values in to tables individually.) I need to fetch some data from DB1 table(say DB1 ip is... (2 Replies)
Discussion started by: aemunathan
2 Replies

10. Shell Programming and Scripting

max values amd min values

Hello every one, I have following data ***CAMPAIGN 1998 CONTRIBUTIONS*** --------------------------------------------------------------------------- NAME PHONE Jan | Feb | Mar | Total Donated ... (12 Replies)
Discussion started by: devmiral
12 Replies
Login or Register to Ask a Question