Hi!
I want to do a query against a SQL DB and by sorting a datetime field, I want to get the second newest record in the table, not the newest.
Can I do that?
/Johan Ch
SELECT TOP 1
*
FROM
(SELECT TOP 2 * FROM myTable Order by myDateTime DESC) AS A
ORDER BY
myDateTime ASC
-S>> I want to setup one of the fields [sic] in a table so it increments
sequentially(INTEGER data type). i.e the first record [sic] should be
record 1 [sic] and the second one should be 2 and so on. This field
[sic]will also be the key field [sic]. I am new to SQL and don't know
how to do this. <<
Very new. Rows are not records; columns are not fields; keys are not
physical record numbers. You missed the most basic concept of the
RDBMS model. A key is a subset of the attributes (columns) which are
unique and not null for each entity (row) in the table. It is never a
"magic number for everything" generated by the computer which has no
meaning in the reality you are trying to model.
You actually have to think and work hard to design a database. Get a
book on data modeling and read it.|||Take a look at IDENTITY (Property) in "SQL Server Books Online".
"Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
news:a3007893.0311071034.54617b6b@.posting.google.c om...
> Hi,
> I want to setup one of the fields in a table so it increments
> sequentially(int data type). i.e the first record should be record 1
> and the second one should be 2 and so on. This field will also be the
> key field. I am new to SQL and don't know how to do this.
> I am using SQL server 2000.
> Thanks for the help in advance.
> -S|||thanks for the 40. I hear SQL FOR SMARTIES is pretty good. Any thoughts??
joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0311071358.4db9e6b8@.posting.google.com>...
> >> I want to setup one of the fields [sic] in a table so it increments
> sequentially(INTEGER data type). i.e the first record [sic] should be
> record 1 [sic] and the second one should be 2 and so on. This field
> [sic]will also be the key field [sic]. I am new to SQL and don't know
> how to do this. <<
> Very new. Rows are not records; columns are not fields; keys are not
> physical record numbers. You missed the most basic concept of the
> RDBMS model. A key is a subset of the attributes (columns) which are
> unique and not null for each entity (row) in the table. It is never a
> "magic number for everything" generated by the computer which has no
> meaning in the reality you are trying to model.
> You actually have to think and work hard to design a database. Get a
> book on data modeling and read it.|||>> thanks for the 40. I hear SQL FOR SMARTIES is pretty good. Any
thoughts?? <<
I like it a lot -- especially when the royalty check arrives :)
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dont listen to Celko - his is too pr0 :p
go with the identity - but beware some of its behaviour, and protect
it carefully, it is not terribly robust and can totally shaft your
data relationships if you use it and let it get damaged.
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vqofb5e8rl3k97@.corp.supernews.com>...
> Take a look at IDENTITY (Property) in "SQL Server Books Online".
> "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > Hi,
> > I want to setup one of the fields in a table so it increments
> > sequentially(int data type). i.e the first record should be record 1
> > and the second one should be 2 and so on. This field will also be the
> > key field. I am new to SQL and don't know how to do this.
> > I am using SQL server 2000.
> > Thanks for the help in advance.
> > -S|||Wang,
I do agree with you. These are all features that help programmers to develop
more efficient applications. It all depends on how good you are, if you are
a terrible programmer then it doesn't matter whether you use IDENTITY or
not, you will end up with a piece of crap anyway. A professionals is someone
who can take advantage of these features and use them wisely to help
developing their creative ideas without making a mess.
Shervin
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0311110220.539cf94d@.posting.google.c om...
> Dont listen to Celko - his is too pr0 :p
> go with the identity - but beware some of its behaviour, and protect
> it carefully, it is not terribly robust and can totally shaft your
> data relationships if you use it and let it get damaged.
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:<vqofb5e8rl3k97@.corp.supernews.com>...
> > Take a look at IDENTITY (Property) in "SQL Server Books Online".
> > "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> > news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > > Hi,
> > > I want to setup one of the fields in a table so it increments
> > > sequentially(int data type). i.e the first record should be record 1
> > > and the second one should be 2 and so on. This field will also be the
> > > key field. I am new to SQL and don't know how to do this.
> > > I am using SQL server 2000.
> > > Thanks for the help in advance.
> > > > -S|||how would you protect the behavior of IDENTITY?
Shervin and wangkhar thanks for the help.
Celko, Don't expect any royalty from this beginner!!
Wangkhar@.yahoo.com (WangKhar) wrote in message news:<bb269444.0311110220.539cf94d@.posting.google.com>...
> Dont listen to Celko - his is too pr0 :p
> go with the identity - but beware some of its behaviour, and protect
> it carefully, it is not terribly robust and can totally shaft your
> data relationships if you use it and let it get damaged.
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vqofb5e8rl3k97@.corp.supernews.com>...
> > Take a look at IDENTITY (Property) in "SQL Server Books Online".
> > "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> > news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > > Hi,
> > > I want to setup one of the fields in a table so it increments
> > > sequentially(int data type). i.e the first record should be record 1
> > > and the second one should be 2 and so on. This field will also be the
> > > key field. I am new to SQL and don't know how to do this.
> > > I am using SQL server 2000.
> > > Thanks for the help in advance.
> > > > -S|||>> Celko, Don't expect any royalty from this beginner! <<
What I used to get from beginners was consulting gigs, to clean up the
mess they made when their company has problems. Those jobs pay MUCH
better than royalties :)
Using IDENTITY as a primary key is a sign that there is no data model,
only an imitation of a sequential file system. Since this "magic,
all-purpose, one-size-fits-all" pseudo-identifier exists only as a
result of the physical state of a particular piece of hardware at a
particular time as read by the current release of a particular database
product, how do you verify that an entity has such a number in the
reality you are modeling?
You will see newbies who design tables like this:
CREATE Drivers
(driver_id IDENTITY (1,1) NOT NULL PRIMARY KEY,
ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin));
Now input data and submit the same row a thousand times, a million
times. Your data integrity is trashed. The natural key was this:
CREATE Drivers
(ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin),
PRIMARY KEY (ssn, vin));
If you want to enforce a rule that a car can have one driver:
CREATE Drivers
(ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL PRIMARY KEY
REFERENCES Motorpool(vin));
Now you are REALLY thinking about relations and keys instead of 1950's
sequential record numbering. Go further and add DRI:
CREATE Drivers
(ssn CHAR(9) NOT NULL
REFERENCES Personnel(ssn)
ON DELETE CASACADE
ON UPDATE CASACADE,
vin CHAR(17) NOT NULL PRIMARY KEY
REFERENCES Motorpool(vin)
ON DELETE CASACADE
ON UPDATE CASACADE,
PRIMARY KEY (ssn, vin));
Adding an IDENTITY column to either of these tables as a candidate key
would be dangerously redundant; one query uses the IDENTITY and another
uses the real key, and like a man with two watches, you are never sure
what time it is.
Researching the CHECK constraints you need for a VIN or SSN will take a
few days -- but newbies only want "quick and magic answers that solve
all the problems" and do not bother doing the real work.
Finally, an appeal to authority, with a quote from Dr. Codd: "..Database
users may cause the system to generate or delete a surrogate, but they
have no control over its value, nor is its value ever displayed to them
..."(Dr. Codd in ACM TODS, pp 409-410) and Codd, E. (1979), Extending
the database relational model to capture more meaning. ACM Transactions
on Database Systems, 4(4). pp. 397-434.
This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means never
used in queries, DRI or anything else that a user does.
Codd also wrote the following:
"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.
(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two companies
merge, the two employee databases might be combined with the result that
some or all of the serial numbers might be changed.).
(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.
(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it has
ceased to have one (e.g. and applicant for a job and a retiree).
These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] - is
to introduce entity domains which contain system-assigned surrogates.
Database users may cause the system to generate or delete a surrogate,
but they have no control over its value, nor is its value ever displayed
to them...." (Codd in ACM TODS, pp 409-410).
References
Codd, E. (1979), Extending the database relational model to capture more
meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||It's not always consecutive. So if it's important to you just forget
IDENTITY.
Identity is a means just like many other things in SQL, if you chose the
right tool for your problem you won't regret it. It's not a silver bullet
but it will help you if you use it in the right place.
Good luck,
Shervin
"Sumanth Suri" <dangerousminds_17@.yahoo.com> wrote in message
news:2e121621.0311111136.5c401fd@.posting.google.co m...
> how would you protect the behavior of IDENTITY?
> Shervin and wangkhar thanks for the help.
> Celko, Don't expect any royalty from this beginner!!
> Wangkhar@.yahoo.com (WangKhar) wrote in message
news:<bb269444.0311110220.539cf94d@.posting.google.com>...
> > Dont listen to Celko - his is too pr0 :p
> > go with the identity - but beware some of its behaviour, and protect
> > it carefully, it is not terribly robust and can totally shaft your
> > data relationships if you use it and let it get damaged.
> > "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:<vqofb5e8rl3k97@.corp.supernews.com>...
> > > Take a look at IDENTITY (Property) in "SQL Server Books Online".
> > > > "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> > > news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > > > Hi,
> > > > I want to setup one of the fields in a table so it increments
> > > > sequentially(int data type). i.e the first record should be record 1
> > > > and the second one should be 2 and so on. This field will also be
the
> > > > key field. I am new to SQL and don't know how to do this.
> > > > I am using SQL server 2000.
> > > > Thanks for the help in advance.
> > > > > > -S
Hi
I am trying to implement a sliding window on a table in SQL Server 2005 but i am having some problem.
I have two tables, "Letture" and "LettureStorico". The first one receives data on a few seconds basis, some thousands of rows each day. The second is the historical record and should store all the records till midnigh of two days before, that is, if today is November 21st, LettureStorico stores rows till November 19th 23.59:59.997.
At some time during morning of each day i want to run a stored procedures that takes the records older than midnight of two days before in "Letture" and switch them as a partition in "LettureStorico"
Here's what i do:
/*-*/
CREATE PARTITION FUNCTION [partizioneLive](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')
CREATE PARTITION FUNCTION [partizioneStorico](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')
CREATE PARTITION SCHEME [schemapartizioneLive] AS PARTITION [partizioneLive] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneLive] NEXT USED [PRIMARY];/*(1)*/
CREATE PARTITION SCHEME [schemapartizioneStorico] AS PARTITION [partizioneStorico] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneStorico] NEXT USED [PRIMARY]; /*(1)*/
CREATE TABLE [dbo].[Letture](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_Letture] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneLive(data)
) ON schemaPartizioneLive(data)
ALTER TABLE [dbo].[Letture] WITH CHECK ADD CONSTRAINT [CK_Letture] CHECK (([Data]>='20061115 00:00'))
CREATE TABLE [dbo].[LettureStorico](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_LettureStorico] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneStorico(data)
) ON schemaPartizioneStorico(data)
ALTER TABLE [dbo].[LettureStorico] WITH CHECK ADD CONSTRAINT [CK_LettureStorico] CHECK (([Data]<'20061115 00:00'))
/*-*/
Every morning i run a stored procedure that, after dropping the check constraints (i'll recreate the at the end), does the following:
/*--*/
SET @.NewBoundary = dateadd(dd,-1, @.dateOfToday)
--this new partition contains the rows i want to switch
ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)
--this new partition is empty
ALTER PARTITION FUNCTION PartizioneStorico() SPLIT RANGE (@.NewBoundary)
--this works fine, rows are moved
ALTER TABLE Letture SWITCH PARTITION 2 TO LettureStorico PARTITION 2
--these two merges lead to two tables partitioned in two partitions each
ALTER PARTITION FUNCTION PartizioneLive() MERGE RANGE (@.OldBoundaryLive)
ALTER PARTITION FUNCTION PartizioneStorico() MERGE RANGE (@.OldBoundaryStorico)
/**/
Till now, everything is working as expected.
Now, when i try to run the same Stored Procedure " a day later" (NewBoundary moved on 1 day) i receive, when i do the "ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)" i receive a 7707 error message:
"Msg 7707, Level 16, State 1, Line 1
The associated partition function 'PartizioneLive' generates more partitions than there are file groups mentioned in the scheme 'schemapartizioneLive'."
How is this possible if i used the "ALL TO [PRIMARY]" and specified which file to use next as in (1) ? Why all this succeeds the first time (when i have 3 partitions) but not the second (again i have just three partitions, i checked) ?
Someone can help me on this, please ?
Many thankx
Wentu
Hello,
I've experienced the same problem. Even if you map all your partitions to the PRIMARY filegroup when you create your scheme, each time before you split your range, you have to call
ALTER PARTITION SCHEME schemapartizioneLive NEXT USED [PRIMARY].
However, why it works the first time is still a mystery to me. Probably when you mal all your partitions, the "next used" is probably also set. As you haven't done it when you split the first time, you got the error the second time.
Greetings,
Adriano
