select multiple columns with one max( )

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

SQL Server 2000 Forums

Transact-SQL (2000)

select multiple columns with one max( )

Author

Topic

Fletch
Starting Member

29 Posts

Posted - 2006-08-04 : 09:44:49

I'm sure there's a simple way to do this, without resorting to temporary tables etc.

Simple dataset:
..outlet.......value.......date
...1............14.........1/1/06
...1............99.........2/1/06
...1............15.........3/1/06
...2............10.........1/1/06
...2............12.........2/1/06
...2............97.........3/1/06

I need one row per outlet, with the value and date for when the max(value) occurred.

...1............99.........2/1/06
...2............97.........3/1/06

I can get the outlet and value no problem but not the date!

Many thanks. (edit: added dots to 'aid' clarity/formatting)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-04 : 09:47:01

[code]select *
from table t
where date in (select max(date) from table x where x.outlet = t.outlet)[/code]

Fletch
Starting Member

29 Posts

Posted - 2006-08-04 : 09:57:15

wouldn't that select the max(date) rather than the date of the max(value)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-04 : 10:05:22

sorry.. did not read it correctly.

select *
from   table t
where  value in (select max(value) from table x where x.outlet = t.outlet)

Fletch
Starting Member

29 Posts

Posted - 2006-08-04 : 10:11:40

Why does the answer always look so easy?

Many thanks KH. The query is actually on two tables but I think I can figure out the rest myself (if not.. ..I'll be back!)

Thanks again.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-04 : 10:34:48

quote:
Originally posted by khtan

sorry.. did not read it correctly.

select *
from table t
where value in (select max(value) from table x where x.outlet = t.outlet)

KH

You want =, not IN.

... where value = (select ...

- Jeff

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-04 : 10:47:24

"You want =, not IN. "
is it the same in this case ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-04 : 14:23:03

I guess tehinically you can use IN, but that is like saying:

WHERE Value IN (0)

The optimizer should evaluate it the same as saying WHERE VALUE = 0 but I think we can agree that = logically makes more sense.

- Jeff

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-04 : 19:44:04

quote:
Originally posted by jsmith8858

I guess tehinically you can use IN, but that is like saying:

WHERE Value IN (0)

The optimizer should evaluate it the same as saying WHERE VALUE = 0 but I think we can agree that = logically makes more sense.

- Jeff

Agreed

. Now that I think of it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources