Select Max function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Max function

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2010-03-31 : 15:32:18

Hello All,
I have a fuel table
Fuel_table
Fuel_date datetime Not null
Fuel_Amount money Not null

I want to return the latest date "Fuel_amount" and this sql gave me an error:

Select max(fuel_date), fuel_amount
from fuel_table

Can anyone help?

thank you

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-03-31 : 15:45:03

Try this:

Select max(fuel_date), fuel_amount
from fuel_table
Order By fuel_amount

nizguy
Starting Member

37 Posts

Posted - 2010-03-31 : 15:53:13

same error

Server: Msg 8118, Level 16, State 1, Line 1
Column 'fuel_table.Fuel_amount' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

if i use group by fuel_amount, it returned all value from the fuel_table. I just want to return one max fuel amount

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-03-31 : 15:54:53

quote:
Originally posted by nizguy

same error

Server: Msg 8118, Level 16, State 1, Line 1
Column 'fuel_table.Fuel_amount' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

if i use group by fuel_amount, it returned all value from the fuel_table. I just want to return one max fuel amount

Ooops,

My mistake:

Select max(fuel_date), fuel_amount
from fuel_table
Group By fuel_amount

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-03-31 : 15:58:44

"if i use group by fuel_amount, it returned all value from the fuel_table. I just want to return one max fuel amount"

Didn't read this part:

Try

select max(maxxy.themax)
From (

Select max(fuel_date) themax, fuel_amount
from fuel_table
Group By fuel_amount
) as maxxy

nizguy
Starting Member

37 Posts

Posted - 2010-03-31 : 16:26:26

it return the max date. I want to return the "Fuel_amount" of that date

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-03-31 : 16:44:13

Select fuel_amount
from fuel_table where fuel_date = (select max(fuel_date) from fuel_table)

nizguy
Starting Member

37 Posts

Posted - 2010-03-31 : 16:46:23

Yes. I got it

thank you very much.