MOD
-
I am trying to select the even, or odd for that matter, rows that are in a table. I thought I could use the MOD function. I believe that it was not available in SQL 2000. How can I select these rows?
-
Without knowing your table structure, this is pretty much guessing. Can you provide your DDL?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Even ?
With tables, "order" has no rule, unless you have modeled it (e.g. using identity,..)
with identity, you can have gaps (which should be no problem for your datamodel !)
select colum-list
from ...
where (myidentitycol % 2) = 0
should workJohan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Great, That worked
Where ...
and (ID % 2) = 0
THANKS!
-
That works great if you have no gaps or if you want even or odd pks.. but if you really want row #1,#3,#5, or #2,#4,#6... try this :
CREATE TABLE [Databases] (
[PkDB] [int] IDENTITY (1, 1) NOT NULL ,
[DbName] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
CONSTRAINT [PK_Databases] PRIMARY KEY CLUSTERED
(
[PkDB]
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT DATABASES ON
Insert into dbo.Databases (PkDB, DbName) VALUES (135,'Reception')
Insert into dbo.Databases (PkDB, DbName) VALUES (136,'master')
Insert into dbo.Databases (PkDB, DbName) VALUES (138,'Documentation')
Insert into dbo.Databases (PkDB, DbName) VALUES (139,'Ideal')
SET IDENTITY_INSERT DATABASES OFF
--odds
Select PkDB, DbName from dbo.Databases DMain where (Select count(*) from dbo.Databases D2 where D2.PkDB <= DMain.PkDB) % 2 = 1 order By PkDB
--evens
Select PkDB, DbName from dbo.Databases DMain where (Select count(*) from dbo.Databases D2 where D2.PkDB <= DMain.PkDB) % 2 = 0 order By PkDB
drop table Databases
-
Okay, should have guessed the IDENTITY is around.
Are you using this to extract a sample for statistic purposes?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I had an application where I wanted to be able to query on address that had an odd or even house number (were on the same side of the street).
bldg_num is an integer field and I got even addresses with:
select * from address where bldg_num = (bldg_num / 2) * 2
odd addresses:
select * from address where bldg_num <> (bldg_num / 2) * 2
"where (myidentitycol % 2) = 0" looks good too
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply