Integer to Float Comparison

[JRB-Bldr]

Hello,

While I have many years experience using SQL into a variety of backend databases, I am new to the SQL Server environment and its syntax. Your assistance would be greatly appreciated.

I want to create a SQL query which will use, as one of its selection parameters, a comparison of an Integer to its Float value.

I have an Integer value within the table DialID

I now want to SELECT only every 3rd record instead of all records - where the Integer portion of the Divide by 3 is equal to the Divide by 3 value itself.

To do so I want something like
DialID INT(DialID/3) (DialID/3)
1 0 0.33
2 0 0.66
3 1 1.0 <--- Selected
4 1 1.33
5 1 1.66
6 2 2.0 <--- Selected
7 2 2.33
etc.

I need some assistance with what syntax to use to get my desired results.

Any assistance you can offer would be greatly appreciated.

Thanks,
JRB-Bldr

 

[ESquared]

select * from mytable where floor(floatvalue) = intvalue

or possibly slightly more efficient

select * from mytable where floor(floatvalue) = floatvalue

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[gmmastros]

You could also use the MOD operator (implemented in T-SQL as % ), like this...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](DialId [COLOR=blue]int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](5)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](6)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7)

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Temp [COLOR=blue]Where[/color] DialId % 3 = 0

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

To give you a better idea of what I am talking about, copy/paste the following code in to a query analyzer window and run it.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](DialId [COLOR=blue]int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](5)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](6)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7)

[COLOR=blue]Select[/color]  DialId, 
        DialId/3 [COLOR=blue]As[/color] [DialId/3], 
        [COLOR=#FF00FF]Convert[/color]([COLOR=blue]Float[/color], DialId)/3 [COLOR=blue]As[/color] [[COLOR=#FF00FF]Convert[/color]([COLOR=blue]Float[/color], DialId)/3],
        DialId % 3 [COLOR=blue]As[/color] [Remainder]
[COLOR=blue]From[/color]    @Temp

The output will be:

[tt][blue]DialId DialId/3 Convert(Float, DialId)/3 Remainder
----------- ----------- ------------------------ -----------
1 0 0.333333333333333 1
2 0 0.666666666666667 2
3 1 1 0
4 1 1.33333333333333 1
5 1 1.66666666666667 2
6 2 2 0
7 2 2.33333333333333 1[/blue][/tt]

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

Thanks george, you're right about the modulus (I suspect it's faster than working with floats). I wasn't paying attention.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[JRB-Bldr]

Thanks for the replies.

It seems as though the % operator is a quick means of accomplishing what I am looking for.

It appears to be functionally comparable to the Modulo operators that I am familiar with except that it, as shown, does not allow for varying the Modulo Base (base 3, base 100, whatever...)

For a more long term understanding, I would still like to know how to get:
Integer part only of: DialID/3
Complete Floating point value of: DialID/3
And know the syntax to use so as to be able to compare the two for a numeric match (or not).

Thanks,
JRB-Bldr

 

[gmmastros]

Maybe I'm missing something here, but I thought I showed all of that with my most recent post.

[green]-- Setup test data[/green]
Declare @Temp Table(DialId int)

Insert Into @Temp Values(1)
Insert Into @Temp Values(2)
Insert Into @Temp Values(3)
Insert Into @Temp Values(4)
Insert Into @Temp Values(5)
Insert Into @Temp Values(6)
Insert Into @Temp Values(7)

Select  DialId,
        DialId/3 As [DialId/3], [green]-- Integer part[/green]
        Convert(Float, DialId)/3 As [Convert(Float, DialId)/3], [green]-- Floating point value[/green]
        DialId % 3 As [Remainder]
From    @Temp 
Where	Convert(Float, DialId) / 3 = DialId/3 [green]-- Compare values[/green]

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

It appears to be functionally comparable to the Modulo operators that I am familiar with except that it, as shown, does not allow for varying the Modulo Base (base 3, base 100, whatever...)

What do you mean? The next number is the "base" that it's divided by.

10 % 3 = 1
9 % 3 = 0
8 % 3 = 2

10 % 4 = 2
9 % 4 = 1
8 % 4 = 0

10 % 5 = 0
9 % 5 = 4
8 % 5 = 3


and so on.





[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[SQLSister]

And do remmeber that float is a very bad data type to use as it is not exact.

Questions about posting. See faq183-874

 

[JRB-Bldr]

Well once again I find another example of a non-intuitive command syntax.

One would usually think that a calculation such as:

   [B]DialID/3[/B]

would yield a Floating Pt, real value and not ignore the fractional component by being an Integer only value.

Oh well that's why it takes some time learning a new development language.

Thanks for your replies.
JRB-Bldr

 

[ESquared]

Integer part only of: DialID/3
probably dialid/3 as you've discovered
but perhaps floor(convert(float, DialID) / 3)


Complete Floating point value of: DialID/3
convert(float, DialID) / 3

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]