Selecting Top 1 (again) - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting Top 1 (again)

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-08 : 12:51:05

SELECT Name, Address, Value1, Value2 FROM MyTable

Each name and address can have 1 or more rows. I want the recordset to have only the Top row for each name and address when ordered by Name, Address, Value1, Value2

Is there a way to do this without temporary tables?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 12:59:42

Got a unique identifier on each row?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-08 : 13:00:48

10-4

Just call it ID

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-08 : 13:10:01

I am thinking about Jeffs "Runs and Streaks" article.
Does this work for you?


select name,address,value1,value2
from
(
	select *, (select count(name) from test b where b.name = a.name and b.value1 <= a.value1 and b.value2 <= a.value2) n
	from test a 
) a
where n = 1
order by name,address,value1,value2

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-08 : 13:11:22

OK...how about...


USE Northwind
GO

CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), CustName varchar(25), Address varchar(25), Col2 char(1), Col3 int)
GO

INSERT INTO myTable99(CustName,Address,Col2,Col3)
SELECT 'Brett', 'NJ', 'a', 1 UNION ALL
SELECT 'Brett', 'NJ', 'b', 2 UNION ALL
SELECT 'Sam', 'MD', 'c', 3 UNION ALL
SELECT 'Sam', 'MD', 'd', 4

SELECT * FROM myTable99 a 
WHERE EXISTS(SELECT CustName, Address FROM myTable99 b 
	   GROUP BY CustName, Address
	 HAVING MIN(b.Col1) = a.Col1)
GO

DROP TABLE myTable99
GO

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-08 : 22:14:42

Thanks for the feedback.

Brett, I can't count on the ID field being in order. Your suggestion is an efficent solution if the IDs were sequential.

Jay's query looks like it would work, but an "expensive" way to get the job done. If I read it right, there's a table scan on every row in the result recordset. But it get's the job done.

I found another solution, a derivative of Brett's where I as able to come up with a single calculated field that results in an order by quantity.

The inner-most part of the query uses GROUP BY to find the MAX of a calculatable field

SELECT name, address, MAX(TopDog)
FROM MyTable
GROUP BY name, address

From here, it's all downhill. I guess I was lucky to come up with a forumla for the calculated column TopDog.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources