SELECT MIN question - 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 2005 Forums

Transact-SQL (2005)

SELECT MIN question

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-03-06 : 12:08:08

Got this table


VehicleRef Miles Term CH
13519      10000 2    200 
13519      20000 2    100
13519      30000 3    300

I want to return the row with the lowest CH

SELECT term,miles,min(ch) FROM vwAllMatrixWithLombardAndShortModel WHERE vehicleRef = 13519

This gives me an error - is it possible to do what I want?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-06 : 12:13:50

SELECT term,miles,min(ch) FROM vwAllMatrixWithLombardAndShortModel WHERE vehicleRef = 13519
GROUP BY term,miles

you have to use group by when using aggregates

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-03-06 : 12:15:46

Hi,

That displays all the rows (presumably as there are 3 distinct miles)

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-06 : 12:18:14

[code]select top 1
a.*
from
vwAllMatrixWithLombardAndShortModel a
where
a.VehicleRef = 13519
order by
a.CH ASC[/code]
CODO ERGO SUM

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-03-06 : 12:20:05

True, but that query will give you all three records back. Do you have multiple VehicleRefs in the table? Do you really want to identify the row with min(CH) for each VehicleRef? Or do you literally only want the data for VehicleRef = 13519?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-06 : 14:04:14

quote:
Originally posted by tprupsis
...True, but that query will give you all three records back...

Which query are you commenting on? The one I posted will return only the row the OP said they wanted: "I want to return the row with the lowest CH"

CODO ERGO SUM

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-03-06 : 14:38:31

You're right...it was in reference to spirit1's post. I thought more input from the OP was required. I'm guessing they don't want just the lowest for that one VehicleRef. But by the time I clicked save, three new posts had been written. I just type too slow I guess!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-06 : 14:52:26

@tprupsis
you're right. more input is required

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 15:53:16

quote:
Originally posted by Mondeo

Hi,

That displays all the rows (presumably as there are 3 distinct miles)

Thanks

The problem is there are 3 distinct miles, 2 distinct Term and 3 distinct CH, so the min CH for each distinct ROW is actually the CH for each row to begin with (in the sample data)

So it returns all rows, precisely as the select statement tells it to.


Select a.VehicleRef,a.Miles, a.Term,b.CH
FROM [Table] a inner join (Select VehicleRef,min(CH) as CH
                           FROM [Table]
                           Group by VehicleRef) b
on a.VehiclRef = b.VehicleRef and a.CH = b.CH

Will return the row:

VehicleRef Miles Term CH
13519 20000 2 100

I am sure there are better ways...but it seems like he wants the whole row where the CH is the minimum for each vehicleref..presumably there are more than 1 of those as well.

Poor planning on your part does not constitute an emergency on my part.

cbeganesh
Posting Yak Master

105 Posts

Posted - 2008-03-06 : 16:18:10

select * from ttt
vname,miles,term,ch

13519,10000,2,200
13519,20000,2,100
13519,30000,3,300
13520,10000,1,200
13520,20000,2,100
13520,30000,3,300
13521,10000,1,100
(7 row(s) affected)

select a.* from ttt a,
(select vname,min(ch) as minch from ttt group by vname) b
where a.vname = b.vname
and a.ch = b.minch

vname,miles,term,ch
13519,20000,2,100
13520,20000,2,100
13521,10000,1,100

cbeganesh
Posting Yak Master

105 Posts

Posted - 2008-03-06 : 16:19:36

this is same as posted by DataGuru1971

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-07 : 00:51:19

By using SQL 2005 syntax.

Declare @tbl table(VehicleRef int,Miles int,Term int,CH int)
Insert into @tbl
select 13519,10000,2,200 union all
select 13519,20000,2,100 union all
select 13519,30000,3,300 union all
select 13520,10000,1,200 union all
select 13520,20000,2,100 union all
select 13520,30000,3,300 union all
select 13521,10000,1,100

select * from
(
select VehicleRef,Miles,Term,CH,row_number()over
(
partition by VehicleRef order by VehicleRef,ch
)
as rowid
from @tbl
)t where rowid=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources