get another field from a select MIN ( ... statemen

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 2008 Forums

Transact-SQL (2008)

get another field from a select MIN ( ... statemen

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-09-20 : 04:05:06

I want the field "id" from the row wich gets return by a MIN statement
how do i do that ?

declare @idx as bigint
SET NOCOUNT ON;

SELECT @idx = H.id ,MIN(totaal/aantal)
FROM tblartikelhistoriek H
WHERE H.klantid = '090001' AND H.artikelid = '1450 204250' AND (H.eenheidprijs is not null OR H.eenheidprijs > 0)
GROUP BY H.id

this doesnt work ... the min(total / amount) DOES work

Sachin.Nand

2937 Posts

Posted - 2010-09-20 : 04:20:20

You cannot assign a value to a variable in a select statement which return column values.

PBUH

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-09-20 : 07:01:31

so this isnt possible, well fuck then :s

Sachin.Nand

2937 Posts

Posted - 2010-09-20 : 07:14:16

quote:
Originally posted by doubleotwo

so this isnt possible, well fuck then :s

Please be a bit civilised in selection of words.

PBUH

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-20 : 07:56:56

Join the result again to tblartikelhistoriek on MIN(totaal/aantal)=totaal/aantal to get id.

parody
Posting Yak Master

111 Posts

Posted - 2010-09-20 : 08:04:40

You are trying to set a result set to the variable value. A variable only contains one value. I think what you want is to set the value to the ID with the min aggregate in the table is that right?

e.g something like

declare @test int

set @test =
(select top 1 H.id
from kelhistoriek H
WHERE H.klantid = '090001' AND H.artikelid = '1450 204250' AND (H.eenheidprijs is not null OR H.eenheidprijs > 0)
GROUP BY H.id
ORDER BY MIN(totaal/aantal))

select @test

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-09-23 : 08:34:02

SWEET !

why am i not from india , those guys always see the matrix ...

:) thx alot guys .. AGAIN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources