Show only highest value without grouping?

[suttercain]

Hi everyone,

 

I have a table set up as such:

 

name | extension | id

de01  | NULL        | 1

de01  | NULL        | 2

de01  | 01            | 3

de01  | NULL        | 4

de01  | 01            | 5

de01  | 02            | 6

de01  | 02            | 7

 

What I am trying to do is output only the records with the highest value in the "extension" column. So based on the table above I would like to output the following:

 

de01 | 02 | 6

de01 | 02 | 7

 

Additionally, this would have to be a query that can handle higher extensions that may be entered in the future. Example: if a new record was added that contained de01 | 03, that would now need to be shown and the previous two de01 | 02 records would no longer show up. I tried MAX() but I can't seem to get it to function that way I need it.

 

Thanks in advance for your time.

[premiso]

Given that your value is "02" I take it that the extension field is a Varchar or a Char, correct? Max will not work on that as it is a textual field, they will need to be int / double / float for MAX to work on them.

[suttercain]

Hi Premiso,

 

I changed the extension column over to integer and am still having an issue, I think because of the group clause.

 

Here is the SQL statement:

 

"SELECT *, MAX(eo_engine_family.executive_order_extension) AS ext
FROM executive_order
INNER JOIN eo_engine_family ON ( executive_order.executive_order = eo_engine_family.executive_order_new )
INNER JOIN engine_family_offroad_cal ON ( eo_engine_family.engine_family_name = engine_family_offroad_cal.engine_family_name )
WHERE executive_order.date_eo_rescinded IS NULL
AND executive_order.date_eo_rescinded IS NULL
AND eo_engine_family.engine_family_name = '".$engine_family_name."'
eo_engine_family.id
ORDER BY engine_year

 

This is outputting ALL results and not just the "highest" value in the extension column.

 

Thanks in advance.

 

-SC

[suttercain]

Hel-ro?