Re: select row with greatest valued column - let me try again

[Tim McIntyre]

Thanks for the responses guys however I was running out the door  
yesterday and I oversimplified my problem.  Sorry about that, let me  
try again.

Here is my "status_histories" table with some data:
id 	order_id 	code 	type 	                                         
created_at
79 	1 	                1 	        StatusHistoryOrder 	                 
2006-12-06 16:46:36
80 	1 	                1 	        StatusHistoryOrderPayment 	 
2006-12-06 16:46:37
81 	1 	                2 	        StatusHistoryOrder 	                 
2006-12-06 16:46:55
82 	1 	                1 	        StatusHistoryOrder 	                 
2006-12-06 16:47:22
83 	1 	                2 	        StatusHistoryOrder 	                 
2006-12-06 16:47:34
84 	2 	                1 	        StatusHistoryOrder 	                 
2006-12-07 09:50:44
85 	2 	                1 	        StatusHistoryOrderPayment 	 
2006-12-07 09:50:44
86 	2 	                2 	        StatusHistoryOrder 	                 
2006-12-07 09:50:59
87 	2 	                1 	        StatusHistoryOrder 	                 
2006-12-07 09:52:41
88 	2 	                2 	        StatusHistoryOrder 	                 
2006-12-07 09:54:42
89 	3 	                1 	        StatusHistoryOrder 	                 
2006-12-07 11:57:44
90 	3 	                1 	        StatusHistoryOrderPayment 	 
2006-12-07 11:57:44
91 	4 	                1 	        StatusHistoryOrder 	                 
2006-12-07 11:57:58
92 	4 	                1 	        StatusHistoryOrderPayment 	 
2006-12-07 11:57:58

Basically what I'm trying to do is get the most recent  
"StatusHistoryOrder" for each order_id.

something to the effect of this pseudo sql:
SELECT id FROM status_histories WHERE created_at >  
all_other_created_ats_in_this_group GROUP BY order_id;

Hope that makes some sense?

Again thanks in advance!
Tim

On Dec 7, 2006, at 2:25 PM, mos wrote:

At 03:10 PM 12/7/2006, Tim McIntyre wrote:
Hey all

I would guess this would be a pretty simple question.

How do I select a row whose value for a given column is the greatest
or the least or whatever for that table?

e.g:
select * from table where creation_date > all_other_creation_dates;

Hope that makes some sense.  Thanks in advance!
Tim

Tim,
        You can also try:

select min(Creation_date) MinDate, max(Creation_Date) from tablex;

If the creation_date is indexed, it should be quite fast.

Since you're stumbling on simple queries like this one, you may  
want to go out and purchase MySQL Cookbook 2nd edition. It has  
hundreds of useful "How To's" that will help to increase your MySQL  
IQ. Get it for Christmas. It makes a great stocking stuffer  
(depending on how big your feet are!). :)

Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]



Be who you are and say what you feel, because those who mind don't  
matter and those who matter don't mind.
  - Dr. Seuss