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:
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:
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
Last edited by Astrocloud; 05-02-2012 at 01:04 PM..
Reason: Tables came out wrong, trying cut and paste from mysql directly
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.
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.
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".
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)
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)
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)
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)
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)
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)
- 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)
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)
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)
Hello every one, I have following data
***CAMPAIGN 1998 CONTRIBUTIONS***
---------------------------------------------------------------------------
NAME PHONE Jan | Feb | Mar | Total Donated
... (12 Replies)