Is not = to query

I need some help with writing a query that would give be all the result that
is not equalIs not equal to what?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chrismkr" <Chrismkr@.discussions.microsoft.com> wrote in message
news:B015F4E9-6BE4-4A76-A0CC-89398990F301@.microsoft.com...
>I need some help with writing a query that would give be all the result tha
t
> is not equal
>
>|||To what?
I assume you mean for example
select 1 from table where column <> something OR
select 1 from table where column != something
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234VLrAWQGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Is not equal to what?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chrismkr" <Chrismkr@.discussions.microsoft.com> wrote in message
> news:B015F4E9-6BE4-4A76-A0CC-89398990F301@.microsoft.com...
>|||sorry I should give more information.
I have a table with two columns that should have the same kind of data.
What I want to do is see the once that are not the same.
example
Value Value_Index
2.3 2.3
4.5 4.0
3.5 3.5
2.1 2.0
The result in the query should be
Value Value_Index
4.5 4.0
2.1 2.0
Thanks
"Tibor Karaszi" wrote:

> Is not equal to what?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chrismkr" <Chrismkr@.discussions.microsoft.com> wrote in message
> news:B015F4E9-6BE4-4A76-A0CC-89398990F301@.microsoft.com...
>|||Try both and it did not work
Thanks
"Immy" wrote:

> To what?
> I assume you mean for example
> select 1 from table where column <> something OR
> select 1 from table where column != something
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%234VLrAWQGHA.2012@.TK2MSFTNGP14.phx.gbl...
>
>|||Hello Chris,
Both
SELECT * FROM tbValues WHERE Value != Value_Index
or
SELECT * FROM tbValues WHERE Value <> Value_Index
should work in SQL Server assuming Value and Value_Index are both of
datatype float.
In case the columns are of type varchar you should try using LIKE or NOT
LIKE operators.|||Actually, float datatypes are approximate numeric datatypes (as opposed
to exact numeric datatypes like DECIMAL and NUMERIC). So if the
datatypes of both columns are float or real it's possible to get "col1 =
col2" coming up false even when the values look the same because it's
possible for 2.3 to actually be stored as 2.3000000000162 or something
like that for example.
For integers, no problem. For strings (like varchar or char), no
problem either (<> and = work just as well with character data). But
for floating point numbers, to be sure, you should either round them to
the appropriate number of significant digits or type cast them to
DECIMAL(s,p).
For example,
select c1, c2 from MyTable
where cast(c1 as decimal(3,1)) <> cast(c2 as decimal(3,1));
or
select c1, c2 from MyTable
where round(c1,1) <> round(c2,1);
That said, I just wrote a quick batch and it works fine for me with
table variables, temp tables and permanent tables on SQL 2000 and SQL
2005. See the following script:
use tempdb;
go
declare @.MyTable table
(
c1 float,
c2 float
);
insert into @.MyTable (c1, c2) values (2.3, 2.3);
insert into @.MyTable (c1, c2) values (4.5, 4.0);
insert into @.MyTable (c1, c2) values (3.5, 3.5);
insert into @.MyTable (c1, c2) values (2.1, 2.0);
select c1, c2 from @.MyTable
where c1 <> c2;
go
create table #tmp
(
c1 float,
c2 float
);
insert into #tmp (c1, c2) values (2.3, 2.3);
insert into #tmp (c1, c2) values (4.5, 4.0);
insert into #tmp (c1, c2) values (3.5, 3.5);
insert into #tmp (c1, c2) values (2.1, 2.0);
select c1, c2 from #tmp
where c1 <> c2;
drop table #tmp;
go
create table MikeTmp
(
c1 float,
c2 float
);
insert into MikeTmp (c1, c2) values (2.3, 2.3);
insert into MikeTmp (c1, c2) values (4.5, 4.0);
insert into MikeTmp (c1, c2) values (3.5, 3.5);
insert into MikeTmp (c1, c2) values (2.1, 2.0);
select c1, c2 from MikeTmp
where c1 <> c2;
drop table MikeTmp;
go
*mike hodgson*
http://sqlnerd.blogspot.com
SQL_Learner wrote:

>Hello Chris,
>Both
>SELECT * FROM tbValues WHERE Value != Value_Index
>or
>SELECT * FROM tbValues WHERE Value <> Value_Index
>should work in SQL Server assuming Value and Value_Index are both of
>datatype float.
>In case the columns are of type varchar you should try using LIKE or NOT
>LIKE operators.
>|||Hi Chrismkr
What does 'not work' mean? Did you get an error? Did you get the wrong data?
What results did you get?
What datatype did you use to define the column? How is the data being
inserted?
Can you show us the exact query you used?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chrismkr" <Chrismkr@.discussions.microsoft.com> wrote in message
news:E045D31F-3BE9-48BC-8B82-827D8029C42D@.microsoft.com...
> Try both and it did not work
>
> Thanks
> "Immy" wrote:
>
>|||@.MyTable has no key.Do not collect $200 and go
directly to jail!
:)
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:eiuBtmXQGHA.5400@.
TK2MSFTNGP09.phx.gbl...
Actually, float datatypes are approximate numeric datatypes (as opposed to e
xact numeric datatypes like DECIMAL and NUMERIC). So if the datatypes of bo
th columns are float or real it's possible to get "col1 = col2" coming up fa
lse even when the values look the same because it's possible for 2.3 to actu
ally be stored as 2.3000000000162 or something like that for example.
For integers, no problem. For strings (like varchar or char), no problem ei
ther (<> and = work just as well with character data). But for floating poi
nt numbers, to be sure, you should either round them to the appropriate numb
er of significant digits or type cast them to DECIMAL(s,p).
For example,
select c1, c2 from MyTable
where cast(c1 as decimal(3,1)) <> cast(c2 as decimal(3,1));
or
select c1, c2 from MyTable
where round(c1,1) <> round(c2,1);
That said, I just wrote a quick batch and it works fine for me with table va
riables, temp tables and permanent tables on SQL 2000 and SQL 2005. See the
following script:
use tempdb;
go
declare @.MyTable table
(
c1 float,
c2 float
);
insert into @.MyTable (c1, c2) values (2.3, 2.3);
insert into @.MyTable (c1, c2) values (4.5, 4.0);
insert into @.MyTable (c1, c2) values (3.5, 3.5);
insert into @.MyTable (c1, c2) values (2.1, 2.0);
select c1, c2 from @.MyTable
where c1 <> c2;
go
create table #tmp
(
c1 float,
c2 float
);
insert into #tmp (c1, c2) values (2.3, 2.3);
insert into #tmp (c1, c2) values (4.5, 4.0);
insert into #tmp (c1, c2) values (3.5, 3.5);
insert into #tmp (c1, c2) values (2.1, 2.0);
select c1, c2 from #tmp
where c1 <> c2;
drop table #tmp;
go
create table MikeTmp
(
c1 float,
c2 float
);
insert into MikeTmp (c1, c2) values (2.3, 2.3);
insert into MikeTmp (c1, c2) values (4.5, 4.0);
insert into MikeTmp (c1, c2) values (3.5, 3.5);
insert into MikeTmp (c1, c2) values (2.1, 2.0);
select c1, c2 from MikeTmp
where c1 <> c2;
drop table MikeTmp;
go
mike hodgson
http://sqlnerd.blogspot.com
SQL_Learner wrote:
Hello Chris,
Both
SELECT * FROM tbValues WHERE Value != Value_Index
or
SELECT * FROM tbValues WHERE Value <> Value_Index
should work in SQL Server assuming Value and Value_Index are both of
datatype float.
In case the columns are of type varchar you should try using LIKE or NOT
LIKE operators.|||LOL
Yes, it is true. I have sinned in the eyes of Celko & co. But I didn't
think it was terribly critical given that @.MyTable, #tmp and MikeTmp all
contained only 4 rows each and the addition of DRI would probably only
make the example code more confusing (albeit only slightly) while adding
no performance benefit (and very little integrity benefit). The KISS
principle at work.
*mike hodgson*
http://sqlnerd.blogspot.com
05ponyGT wrote:
> @.MyTable has no key.Do not collect $200 and go
> directly to jail!
> :)
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:eiuBtmXQGHA.5400@.TK2MSFTNGP09.phx.gbl...
> Actually, float datatypes are approximate numeric datatypes (as
> opposed to exact numeric datatypes like DECIMAL and NUMERIC). So
> if the datatypes of both columns are float or real it's possible
> to get "col1 = col2" coming up false even when the values look the
> same because it's possible for 2.3 to actually be stored as
> 2.3000000000162 or something like that for example.
> For integers, no problem. For strings (like varchar or char), no
> problem either (<> and = work just as well with character data).
> But for floating point numbers, to be sure, you should either
> round them to the appropriate number of significant digits or type
> cast them to DECIMAL(s,p).
> For example,
> select c1, c2 from MyTable
> where cast(c1 as decimal(3,1)) <> cast(c2 as decimal(3,1));
> or
> select c1, c2 from MyTable
> where round(c1,1) <> round(c2,1);
>
> That said, I just wrote a quick batch and it works fine for me
> with table variables, temp tables and permanent tables on SQL 2000
> and SQL 2005. See the following script:
> use tempdb;
> go
> declare @.MyTable table
> (
> c1 float,
> c2 float
> );
> insert into @.MyTable (c1, c2) values (2.3, 2.3);
> insert into @.MyTable (c1, c2) values (4.5, 4.0);
> insert into @.MyTable (c1, c2) values (3.5, 3.5);
> insert into @.MyTable (c1, c2) values (2.1, 2.0);
> select c1, c2 from @.MyTable
> where c1 <> c2;
> go
>
> create table #tmp
> (
> c1 float,
> c2 float
> );
> insert into #tmp (c1, c2) values (2.3, 2.3);
> insert into #tmp (c1, c2) values (4.5, 4.0);
> insert into #tmp (c1, c2) values (3.5, 3.5);
> insert into #tmp (c1, c2) values (2.1, 2.0);
> select c1, c2 from #tmp
> where c1 <> c2;
> drop table #tmp;
> go
>
> create table MikeTmp
> (
> c1 float,
> c2 float
> );
> insert into MikeTmp (c1, c2) values (2.3, 2.3);
> insert into MikeTmp (c1, c2) values (4.5, 4.0);
> insert into MikeTmp (c1, c2) values (3.5, 3.5);
> insert into MikeTmp (c1, c2) values (2.1, 2.0);
> select c1, c2 from MikeTmp
> where c1 <> c2;
> drop table MikeTmp;
> go
>
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> SQL_Learner wrote:
>