looking for a better query - 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

looking for a better query

Author

Topic

b11091019
Starting Member

15 Posts

Posted - 2011-08-10 : 21:07:00

I've got: (typed from memory, could have syntax errors!)

create table foo(a int, b int, c int)
insert into foo values
(1,2,3),
(1,3,4),
(2,5,6),
(2,7,8)

select * from foo x
where x.b = max(
select a, b from foo y
where x.a = y.a) z

result should look something like:

a b c
-- -- --
1 3 4
2 7 8

That is, only output the rows of data where 'b' is max for each 'a'.
But I want a simpler query without a subquery if possible. If not, what other ways would give the same results?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-10 : 21:14:47

Are you using Microsoft SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-10 : 21:16:59

if you are using SQL 2005 / 2008, this is the way


select	a, b, c
from	(
		select	*, rn = row_number() over (partition by a order by b desc)
		from	foo
	) m
where	m.rn	= 1

KH
[spoiler]Time is always against us[/spoiler]

b11091019
Starting Member

15 Posts

Posted - 2011-08-10 : 21:19:54

hmmm...my original (working!) version is shorter. Have to check the performance of both though.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-10 : 21:24:59

if your original query is working, then you are not using SQL Server. SQLTeam is on SQL Server. For other databases, try dbforums.com

KH
[spoiler]Time is always against us[/spoiler]

b11091019
Starting Member

15 Posts

Posted - 2011-08-11 : 10:50:13

I am using sql server 2008. Corrected query below (told you I typed it from memory!)

select * from foo x
where x.b = (select min(b)
from foo y
where x.a = y.a)

Anyway, your suggestion improves the original query on a large dataset by 2 orders of magnitude! I'm using your query instead.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources