I need a function that aggregates a bit field.

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)

I need a function that aggregates a bit field.

Author

Topic

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-09-06 : 10:40:07

The function would be very simple, it would see if just one of the fields is true it would return true or 1. Per example a field called “answerMe” with following values:

answerMe
0
0
1
0

Select maxBit(answerMe) from myTable

It should return true.

Ideas are bullet proof.

Vendetta.

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 10:47:04

"Select maxBit(answerMe) from myTable"

What's wrong with that then?

Its a bit inefficient because

1) it will have to convert BIT to INT in order to MAX() it (I think)
2) It will examine all records hoping to find something bigger than 1

I would prefer

SELECT CASE WHEN EXISTS (SELECT * FROM myTable WHERE answerMe = 1) THEN 1 ELSE 0 END

because it can "stop" as soon as it finds a TRUE value

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 10:59:30

1 Yes you are correct
2 All non-zero values will be assined as 1 to bit column

Madhivanan

Failing to plan is Planning to fail

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-09-06 : 13:15:39

first of all thank you both very much.

maxBit was just the name that I first thought to give to a aggregate function like that. I know that name isn't a good choice, but any way is it possible to build an aggregate function as I had mentioned?

PS: That function could be called "haveTrue".

Ideas are bullet proof.

Vendetta.

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 14:54:35

If [answerMe] is a BIT datatype column then the convert-to-INT implicit conversion applies.

And now that I understand your question!

You can't create a function, maxBit(), that is applied to all values in a specific column.

You can do

SELECT dbo.MyFunction(SomeValueOrColumn)

which applies a scalar function to the value supplied by [SomeValueOrColumn], which is NOT the same as applying that function to ALL the values for [SomeValueOrColumn] in the result-set

Alternatively you can do

SELECT *
FROM dbo.MyFunction(@SomeValue)

which allows passing of scalar parameter to a function, but allowing the function to return multiple rows of data - i.e. it can be used in the place of a Table.

Either way, I suggest you just use EXISTS as I already explained, as it will be more efficient.

Kristen

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-09-07 : 10:56:20

You can create a user-defined aggregate in the CLR that could do this; however, I believe the CASE supplied by Kristen is probably the way to go.

Jay
to here knows when

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-07 : 11:05:38

All things considered, just a simple:

select max(cast (bitvalue as tinyint))
from yourtable

will do the trick just fine. Kristen's solution with EXISTS will be the most efficient, but if you want to do it generically, just use MIN and MAX and cast your BIT values to a TinyInt -- that should be a very efficient cast.

- Jeff
http://weblogs.sqlteam.com/JeffS

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-07 : 13:28:49

I'm not even sure we are talking about rows here

How did you define your "fields"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-09-08 : 16:58:08

jsmith8858 that's exactly what I was looking for. =D
Kristen, thank you for the explanation of how I could archive it creating a new function.
X002548 I was talking about a function that when aggregate rows from a “bit column” it would return true if any of the rows is equal to true.

Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources