max value of certin column - 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

Transact-SQL (2000)

max value of certin column

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-07-21 : 06:27:44

i have a query that base on some union's.
on this query i make another query like this :


DECLARE @maxCol10 TinyInt
Select * from (
select...
union
select...
union
select... ) t1 where t1.col1 is not null

the thing is that in the query i have col10 which contains 0 or 1 values.
and i want as part of the query that if there is at least one time 1 then my varaible (@maxCol10) will get 1 value else it will get 0.

how can i do this?
Thanks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 06:54:08

Is col10 also a tinyInt?

If so



DECLARE @maxCol10 TINYINT SET @maxCol10 = 0

SELECT
    @maxCol10 = MAX(t1.[col10])
FROM (
    select...
    union
    select...
    union
    select...
    ) t1
WHERE
    t1.col1 is not null

would probably do the trick

That would probably also work if it's a bit.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-07-21 : 07:18:14

the problem that i need
SELECT *,@maxCol10 = MAX(t1.[col10])
which throw an error

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-21 : 07:21:21

can u post the error? which has occured

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-07-21 : 07:28:16

error 141 : A SELECT statement that assigns a value to a variable
must not be combined with data-retrieval operations.

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 07:32:24

you can't return a result set and assign the variable at the same time (not in SQL SERVER anyway)

Do your UNIONS Take a long time / are complicated?

If not then just do 2 select statements one after the other

1 to return the results and the second to set the variable.

IF the queries do take some time / are complicated then there are more efficient ways to do this.

Edit -- probably better to set the variable first then return the result set. I don't know how you are running this sql

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources