SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Simple one I hope		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chrispy
Posting Yak Master

107 Posts
Posted - 04/29/2008 :  19:48:13  Show Profile  Reply with Quote
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

USA
35007 Posts
Posted - 04/29/2008 :  19:57:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

chandan_joshi80
Starting Member

India
30 Posts
Posted - 04/30/2008 :  06:16:16  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 04/30/2008 :  06:27:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Which is different from what Tara posted 10 hours earlier?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

chrispy
Posting Yak Master

107 Posts
Posted - 04/30/2008 :  10:06:51  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000