Simple one I hope - 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)

Simple one I hope

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2008-04-29 : 19:48:13

I have a SP with a temp table that contains the following.


ACCT           Miles
A1              9
A2              44
A2              4
A2              12
A2              2
A6              8
A9              3
A9              22

What I would like to return as a result set is such

ACCT           Miles
A1              9
A2              2
A6              8
A9              3

All the accounts still but the ones that appear on multiple rows are returned with the shortest distance (miles).

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 19:57:23

SELECT ACCT, MIN(Miles) AS Miles
FROM #YourTempTable
GROUP BY ACCT

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-04-30 : 06:16:16

declare @t table (ACC VARCHAR(5), MILES int)
insert @t
select 'A1',9
union all select 'A2',44
union all select 'A2',4
union all select 'A2',12
union all select 'A2',2
union all select 'A6',8
union all select 'A9',3
union all select 'A9',22
SELECT * FROM @t

SELECT ACC,MIN(MILES) FROM @t
GROUP BY ACC

chandan Joshi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 06:27:22

Which is different from what Tara posted 10 hours earlier?

E 12°55'05.25"
N 56°04'39.16"

chrispy
Posting Yak Master

107 Posts

Posted - 2008-04-30 : 10:06:51

Thanks all. I had the min(miles) as Tara posted figured out ,but was not working.
Seems the issue was that I had the Miles in the Group by statement. Removing that solved the issue.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources