Link to home
Start Free TrialLog in
Avatar of Steve Tempest
Steve TempestFlag for Australia

asked on

Help with select min and grouping.

Hi,

I'm having trouble understanding how to structure my data or construct the correct query to select the value's I want.

The Data I have is below.
table_key;order_key;copy;proof;return;release;page
547983;536692;2/6/2008 17:00:00;3/6/2008 09:00:00;3/6/2008 16:00:00;3/6/2008 20:00:00;3/6/2008 21:00:00
548003;536692;2/6/2008 17:00:00;3/6/2008 08:30:00;3/6/2008 10:00:00;3/6/2008 13:00:00;3/6/2008 15:30:00
547985;536692;2/6/2008 17:00:00;3/6/2008 09:00:00;3/6/2008 14:00:00;3/6/2008 17:00:00;3/6/2008 18:00:00
548104;536692;3/6/2008 10:00:00;3/6/2008 14:00:00;4/6/2008 09:00:00;4/6/2008 13:00:00;4/6/2008 14:00:00
548116;536691;2/6/2008 17:00:00;3/6/2008 09:00:00;3/6/2008 14:00:00;3/6/2008 14:30:00;3/6/2008 15:30:00
548023;536691;2/6/2008 17:00:00;3/6/2008 08:00:00;3/6/2008 17:00:00;3/6/2008 20:00:00;3/6/2008 21:00:00
548163;536691;3/6/2008 12:00:00;3/6/2008 15:00:00;3/6/2008 17:00:00;3/6/2008 22:00:00;3/6/2008 22:30:00
548191;536691;2/6/2008 16:00:00;3/6/2008 10:00:00;3/6/2008 12:00:00;3/6/2008 16:00:00;3/6/2008 17:00:00


When I run the following select statement I get the min(page) but but the rest of the record as the lowest table_key row. How would I best return the entire row that belongs with the min(page).

The table has about 100,000 records so it needs to be pretty quick. If I need to restructure the data then I can do that.


SELECT table_key, order_key, copy, proof, return, release, Min(page) FROM tablename GROUP BY order_key order by Min(page);


548023;536691;2/6/2008 17:00:00;3/6/2008 08:00:00;3/6/2008 17:00:00;3/6/2008 20:00:00;3/6/2008 15:30:00
547983;536692;2/6/2008 17:00:00;3/6/2008 09:00:00;3/6/2008 16:00:00;3/6/2008 20:00:00;4/6/2008 14:00:00

Somehow I would like to get this result

548003;536692;2/6/2008 17:00:00;3/6/2008 08:30:00;3/6/2008 10:00:00;3/6/2008 13:00:00;3/6/2008 15:30:00
548116;536691;2/6/2008 17:00:00;3/6/2008 09:00:00;3/6/2008 14:00:00;3/6/2008 14:30:00;3/6/2008 15:30:00

Avatar of Pratima
Pratima
Flag of India image

Try this

SELECT  table_key, order_key, copy, proof, return, release, Min(page) FROM tablename

where page = ( select min(page) from tablename)
order by page;
Avatar of Steve Tempest

ASKER

Hi,

There is no group by in this select... It is important that only 1 entry is returned for each order_key

The order_key may have the same value for page but I only want one of them.
Avatar of aija
aija

Is this what you want?


SELECT
t1.table_key,
t1.order_key,
t1.copy,
t1.proof,
t1.return,
t1.release,
t1.page
FROM
table_name t1
WHERE 
t1.order_key IN (
 SELECT MIN(t2.page) 
 FROM table_name t2 
 WHERE t2.order_key=t1.order_key 
)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forgot to add group by
SELECT
t1.table_key,
t1.order_key,
t1.copy,
t1.proof,
t1.return,
t1.release,
t1.page
FROM
table_name t1
WHERE 
t1.order_key IN (
 SELECT MIN(t2.page) 
 FROM table_name t2 
 WHERE t2.order_key=t1.order_key 
 GROUP BY t2.order_key
)

Open in new window

Thanks for your help

The query didnt run on a mysql database but the info you provided pointed me in the right direction.

Thanks
APNFFSC,
You're welcome. I didn't have mysql available, so I tested it on Oracle. Can you tell me what the final query looked like?