Resetting the count on a Primary Key at a certain record? Impossible?

Hello,
Several consecutive records have been deleted out of an MSDE database
and the program that uses the database doesn't like it. I'm curious
if it's possible to... say... reset the primary key count so that the
records all shift to fill the "dead space" left by the deleted
records.
I have the feeling that this may be humorous to some of you, but any
input (including "you can't do this. that's the point of a primary
key") is appreciated.
Thanks!
Matt
hi Matt,
Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
you can "reset" the identity value using a DBCC CHECKIDENT with the RESEED
option specified..
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Mon, 02 Jul 2007 10:51:23 -0700, Matt Brown - identify wrote:

>Hello,
>Several consecutive records have been deleted out of an MSDE database
>and the program that uses the database doesn't like it. I'm curious
>if it's possible to... say... reset the primary key count so that the
>records all shift to fill the "dead space" left by the deleted
>records.
>I have the feeling that this may be humorous to some of you, but any
>input (including "you can't do this. that's the point of a primary
>key") is appreciated.
Hi Matt,
You can't do this. That's the point of a primary key.
Seriously - a primary key is intended to identify an entity. Think of
what would happen if you would change your name. Or get a new SSN.
Note that I am not contradicting Andrea. DBCC CHECKIDENT WITH RESEED
will affect new IDENTITY values, not existing values. I think that you
were asking about the latter.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
ooopppsss...
Hugo is right, completely right... I did not read with enougth attention and
only saw the "identity" resetting requirement... I'm sorry..
I'm just curious why "..program that uses the database doesn't like it.." :D
Agreeing completely with Hugo about the "keys" immutability, you can perhaps
workaround that..
you could create a "temporary" table where you insert all the current valid
rows of your table, truncate the original table in order to empty it and to
reset the idientity table's value, and finally refill it with the "orphaned"
rows..
so you can write something like
INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
TRUNCATE TABLE dbo.YourTable;
INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
SELECT all_columns_but_the_identity_col
FROM tempTable
WHERE... ORDER BY...;
DROP TABLE tempTable;
but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
or then.. and this "workaround" does not work if you have declarative
referential integrity set on that table, if it's referenced by other rows in
other tables...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Jul 3, 6:34 am, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> Matt Brown - identify wrote:
>
>
> ooopppsss...
> Hugo is right, completely right... I did not read with enougth attention and
> only saw the "identity" resetting requirement... I'm sorry..
> I'm just curious why "..program that uses the database doesn't like it.." :D
> Agreeing completely with Hugo about the "keys" immutability, you can perhaps
> workaround that..
> you could create a "temporary" table where you insert all the current valid
> rows of your table, truncate the original table in order to empty it and to
> reset the idientity table's value, and finally refill it with the "orphaned"
> rows..
> so you can write something like
> INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
> TRUNCATE TABLE dbo.YourTable;
> INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
> SELECT all_columns_but_the_identity_col
> FROM tempTable
> WHERE... ORDER BY...;
> DROP TABLE tempTable;
> but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
> or then.. and this "workaround" does not work if you have declarative
> referential integrity set on that table, if it's referenced by other rows in
> other tables...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
I haven't checked this is a while. That's actually perfect. I can
remove some recordsets, then reset the identity key count with this
here DBCC CHECKIDENT WITH RESEED, then everything should progress as
normal.
Righto!
Thanks,
Matt

resetting count on INT type field (autonumber field)

Hi,
I have an INT field that auto increments by 1, now that I am approaching the
end of the testing phase, I need to delete all the records and reset the
"count" to 1.
Any help?
MitchJust truncate the table (or drop and recreate it) since you don't need the
data. Truncate will reset the identity.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an INT field that auto increments by 1, now that I am approaching
the
> end of the testing phase, I need to delete all the records and reset the
> "count" to 1.
> Any help?
> Mitch
>|||Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
and how do I do it?
SQL Server 2000
Thanks!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
> Just truncate the table (or drop and recreate it) since you don't need the
> data. Truncate will reset the identity.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have an INT field that auto increments by 1, now that I am approaching
> the
> > end of the testing phase, I need to delete all the records and reset the
> > "count" to 1.
> >
> > Any help?
> >
> > Mitch
> >
> >
>|||In Query Analyzer run the following in your database
TRUNCATE TABLE name
Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...
> Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
> and how do I do it?
> SQL Server 2000
> Thanks!
> Mitch
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
> > Just truncate the table (or drop and recreate it) since you don't need
the
> > data. Truncate will reset the identity.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "mitchel" <mitch_001@.yahoo.com> wrote in message
> > news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > I have an INT field that auto increments by 1, now that I am
approaching
> > the
> > > end of the testing phase, I need to delete all the records and reset
the
> > > "count" to 1.
> > >
> > > Any help?
> > >
> > > Mitch
> > >
> > >
> >
> >
>|||Thanks!
Worked perfectly!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23703ihV2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> In Query Analyzer run the following in your database
> TRUNCATE TABLE name
> Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...
> > Sorry, I'm kind of new to SQL server, what does "truncate the table"
mean
> > and how do I do it?
> >
> > SQL Server 2000
> >
> > Thanks!
> >
> > Mitch
> >
> >
> >
> > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
> > > Just truncate the table (or drop and recreate it) since you don't need
> the
> > > data. Truncate will reset the identity.
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "mitchel" <mitch_001@.yahoo.com> wrote in message
> > > news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> > > > Hi,
> > > >
> > > > I have an INT field that auto increments by 1, now that I am
> approaching
> > > the
> > > > end of the testing phase, I need to delete all the records and reset
> the
> > > > "count" to 1.
> > > >
> > > > Any help?
> > > >
> > > > Mitch
> > > >
> > > >
> > >
> > >
> >
> >
>

resetting count on INT type field (autonumber field)

Hi,
I have an INT field that auto increments by 1, now that I am approaching the
end of the testing phase, I need to delete all the records and reset the
"count" to 1.
Any help?
MitchJust truncate the table (or drop and recreate it) since you don't need the
data. Truncate will reset the identity.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi,
> I have an INT field that auto increments by 1, now that I am approaching

---

the

quote:

---

> end of the testing phase, I need to delete all the records and reset the
> "count" to 1.
> Any help?
> Mitch
>

---

|||Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
and how do I do it?
SQL Server 2000
Thanks!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Just truncate the table (or drop and recreate it) since you don't need the
> data. Truncate will reset the identity.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> the
>

---

|||In Query Analyzer run the following in your database
TRUNCATE TABLE name
Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...

quote:

---

> Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
> and how do I do it?
> SQL Server 2000
> Thanks!
> Mitch
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
the[QUOTE]
approaching[QUOTE]
the[QUOTE]
>

---

|||Thanks!
Worked perfectly!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23703ihV2DHA.1532@.TK2MSFTNGP10.phx.gbl...

quote:

---

> In Query Analyzer run the following in your database
> TRUNCATE TABLE name
> Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...
mean[QUOTE]
> the
> approaching
> the
>

---

Reseting Identity Column using SQL

Hi, Can anyone write me a script, how can I reset identity on column? ( I want to start records from 1 again)

Thanks, radco

Take a look at the following pointer:http://msdn2.microsoft.com/en-US/library/ms176057.aspx

Reset identity column

A number of records were written to a table with an identity column that has an identity seed.
These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then remov
ed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
Thanks for any assistance on this.
Never mind, I have answered my own question: dbcc checkident is the solution...
Thanks
"TomT" wrote:

> A number of records were written to a table with an identity column that has an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then rem
oved. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
> Thanks for any assistance on this.

Reset identity column

A number of records were written to a table with an identity column that has
an identity seed.
These records had to be deleted, and I would like to begin the numeric seque
nce at a point as if the records had never been added. E.g., before the inco
rrect records were added, the value of this column was 2500. Two hundred rec
ords were added, then remov
ed. I would like the next value to be 2501, however new records are starting
at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the val
ue, or is there another way?
Thanks for any assistance on this.Never mind, I have answered my own question: dbcc checkident is the solution
...
Thanks
"TomT" wrote:

> A number of records were written to a table with an identity column that h
as an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at
a point as if the records had never been added. E.g., before the incorrect records
were added, the value of this column was 2500. Two hundred records were added, then
rem
oved. I would like the next value to be 2501, however new records are starting at 2701, afte
r the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the v
alue, or is there another way?
> Thanks for any assistance on this.

Reset identity column

A number of records were written to a table with an identity column that has an identity seed.
These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then removed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
Thanks for any assistance on this.Never mind, I have answered my own question: dbcc checkident is the solution...
Thanks
"TomT" wrote:
> A number of records were written to a table with an identity column that has an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then removed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
> Thanks for any assistance on this.

Reset Database

I am fairly new to SQL 2000. I have a program called Websense that monitors
all internet traffic and records it to a SQL database. The database is
growing, and I want to reset it every 2 months just to keep it relatively
small, but I don't know the best way to do this. Should I manually do a
backup of the database after 2 months, and then re-create the database fresh
or is there a more efficient way? I also want to have a backup of the
database for an indefinite period of time just in case I need to restore a
previous copy.Yes you could make a full backup of the database, and then truncate the
tables.
The following article will get you started with implementing an archive
plan: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Allison" <no@.email.com> wrote in message
news:edpWWobuDHA.1060@.TK2MSFTNGP12.phx.gbl...
I am fairly new to SQL 2000. I have a program called Websense that monitors
all internet traffic and records it to a SQL database. The database is
growing, and I want to reset it every 2 months just to keep it relatively
small, but I don't know the best way to do this. Should I manually do a
backup of the database after 2 months, and then re-create the database fresh
or is there a more efficient way? I also want to have a backup of the
database for an indefinite period of time just in case I need to restore a
previous copy.|||Hi Allison,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
I agree with Vyas that you need to archive data from the database for the
purpose of keeping your database small. The suggestion in the article
provided by Vyas looks good. My only concern is that I am not quite clear
what your restore requirements are. Can you describe it in detail? What do
you mean by "indefinite period of time"?
As I understand, when the database is fully backed up from the data of the
last 2 months, it is hard to restore the data in the backup files together.
For example, last 4 months data or last 6 months data will be hard to
restore. You can only restore one backup file within the 2 months data to
the current database each time. Have I addressed your concern correctly?
For more information regarding this issue, please refer to the following
articles:
329833 Support WebCast: Microsoft SQL Server 2000: Understanding Backup and
http://support.microsoft.com/?id=329833
325257 Support WebCast: SQL Server 2000 Database Recovery: Backup and
Restore
http://support.microsoft.com/?id=325257
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Require an SQL

Hi all
From one of the table having the values like below, I need
to take only top 3 records for each EquipmentID.
(I don't prefer to use a simple stored procedure using
temp table) Expecting an SQL Statement if possible.
EquipmentID AreaID AlarmID Duration
-- -- -- --
L4-BELine 1 8241 17484
L4-BELine 1 6038 62
L4-BELine 1 2042 52
L4-BELine 1 8005 32
L4-BELine 1 1013 28
L4-BELine 1 3054 24
L4-BELine 1 5005 24
L4-BUF1 1 1 17340
L4-BUF1 1 2 2056
L4-BUF1 1 8 856
L4-DA01 1 18 6196
L4-DA01 1 1 4924
L4-DA01 1 200 4390
L4-DA01 1 33 24
L4-DA01 1 74 18
L4-DA02 1 80 3920
L4-DA02 1 73 2858
L4-DA02 1 18 2214
L4-DA02 1 203 458
L4-DA02 1 74 346
So the Result expected is as follows:
EquipmentID AreaID AlarmID Duration
-- -- -- --
L4-BELine 1 8241 17484
L4-BELine 1 6038 62
L4-BELine 1 2042 52
L4-BUF1 1 1 17340
L4-BUF1 1 2 2056
L4-BUF1 1 8 856
L4-DA01 1 18 6196
L4-DA01 1 1 4924
L4-DA01 1 200 4390
L4-DA02 1 80 3920
L4-DA02 1 73 2858
L4-DA02 1 18 2214
To try I am here with giving the Schema and data
CREATE TABLE [dbo].[tempRTM_EQM_Top10Alarms] (
[EquipmentID] [nvarchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AreaID] [int] NOT NULL ,
[AlarmID] [int] NULL ,
[Duration] [int] NULL
) ON [PRIMARY]
GO
Data:
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,8241,17484)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,6038,62)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,2042,52)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,8005,32)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,1013,28)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,3054,24)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,5005,24)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,1,17340)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,2,2056)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,8,856)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,18,6196)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,1,4924)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,200,4390)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,33,24)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,74,18)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,80,3920)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,73,2858)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,18,2214)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,203,458)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,74,346)
Thanks in advance.
AnandYou probably want something like this
SELECT EquipmentID, AreaID, AlarmID, Duration
FROM tempRTM_EQM_Top10Alarms A
WHERE AlarmID IN (SELECT TOP 3 AlarmID FROM tempRTM_EQM_Top10Alarms B WHERE
B.EquipmentID = A.EquipmentID ORDER BY AlarmID DESC)
ORDER BY EquipmentID, AlarmID
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anand" <gurusanand1@.sifymail.com> wrote in message
news:09be01c366f5$ffb8fd00$a501280a@.phx.gbl...
> Hi all
> From one of the table having the values like below, I need
> to take only top 3 records for each EquipmentID.
> (I don't prefer to use a simple stored procedure using
> temp table) Expecting an SQL Statement if possible.
> EquipmentID AreaID AlarmID Duration
> -- -- -- --
> L4-BELine 1 8241 17484
> L4-BELine 1 6038 62
> L4-BELine 1 2042 52
> L4-BELine 1 8005 32
> L4-BELine 1 1013 28
> L4-BELine 1 3054 24
> L4-BELine 1 5005 24
> L4-BUF1 1 1 17340
> L4-BUF1 1 2 2056
> L4-BUF1 1 8 856
> L4-DA01 1 18 6196
> L4-DA01 1 1 4924
> L4-DA01 1 200 4390
> L4-DA01 1 33 24
> L4-DA01 1 74 18
> L4-DA02 1 80 3920
> L4-DA02 1 73 2858
> L4-DA02 1 18 2214
> L4-DA02 1 203 458
> L4-DA02 1 74 346
> So the Result expected is as follows:
> EquipmentID AreaID AlarmID Duration
> -- -- -- --
> L4-BELine 1 8241 17484
> L4-BELine 1 6038 62
> L4-BELine 1 2042 52
> L4-BUF1 1 1 17340
> L4-BUF1 1 2 2056
> L4-BUF1 1 8 856
> L4-DA01 1 18 6196
> L4-DA01 1 1 4924
> L4-DA01 1 200 4390
> L4-DA02 1 80 3920
> L4-DA02 1 73 2858
> L4-DA02 1 18 2214
>
> To try I am here with giving the Schema and data
> CREATE TABLE [dbo].[tempRTM_EQM_Top10Alarms] (
> [EquipmentID] [nvarchar] (12) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [AreaID] [int] NOT NULL ,
> [AlarmID] [int] NULL ,
> [Duration] [int] NULL
> ) ON [PRIMARY]
> GO
> Data:
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-
> BELine',1,8241,17484)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,6038,62)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,2042,52)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,8005,32)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,1013,28)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,3054,24)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,5005,24)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,1,17340)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,2,2056)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,8,856)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,18,6196)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,1,4924)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,200,4390)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,33,24)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,74,18)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,80,3920)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,73,2858)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,18,2214)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,203,458)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,74,346)
>
> Thanks in advance.
> Anand|||Hi Allan
This is what I want. Thanks a lot.
Anand.
>--Original Message--
>You probably want something like this
>
>SELECT EquipmentID, AreaID, AlarmID, Duration
>FROM tempRTM_EQM_Top10Alarms A
>WHERE AlarmID IN (SELECT TOP 3 AlarmID FROM
tempRTM_EQM_Top10Alarms B WHERE
>B.EquipmentID = A.EquipmentID ORDER BY AlarmID DESC)
>ORDER BY EquipmentID, AlarmID
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Anand" <gurusanand1@.sifymail.com> wrote in message
>news:09be01c366f5$ffb8fd00$a501280a@.phx.gbl...
>> Hi all
>> From one of the table having the values like below, I
need
>> to take only top 3 records for each EquipmentID.
>> (I don't prefer to use a simple stored procedure using
>> temp table) Expecting an SQL Statement if possible.
>> EquipmentID AreaID AlarmID Duration
>> -- -- -- --
>> L4-BELine 1 8241 17484
>> L4-BELine 1 6038 62
>> L4-BELine 1 2042 52
>> L4-BELine 1 8005 32
>> L4-BELine 1 1013 28
>> L4-BELine 1 3054 24
>> L4-BELine 1 5005 24
>> L4-BUF1 1 1 17340
>> L4-BUF1 1 2 2056
>> L4-BUF1 1 8 856
>> L4-DA01 1 18 6196
>> L4-DA01 1 1 4924
>> L4-DA01 1 200 4390
>> L4-DA01 1 33 24
>> L4-DA01 1 74 18
>> L4-DA02 1 80 3920
>> L4-DA02 1 73 2858
>> L4-DA02 1 18 2214
>> L4-DA02 1 203 458
>> L4-DA02 1 74 346
>> So the Result expected is as follows:
>> EquipmentID AreaID AlarmID Duration
>> -- -- -- --
>> L4-BELine 1 8241 17484
>> L4-BELine 1 6038 62
>> L4-BELine 1 2042 52
>> L4-BUF1 1 1 17340
>> L4-BUF1 1 2 2056
>> L4-BUF1 1 8 856
>> L4-DA01 1 18 6196
>> L4-DA01 1 1 4924
>> L4-DA01 1 200 4390
>> L4-DA02 1 80 3920
>> L4-DA02 1 73 2858
>> L4-DA02 1 18 2214
>>
>> To try I am here with giving the Schema and data
>> CREATE TABLE [dbo].[tempRTM_EQM_Top10Alarms] (
>> [EquipmentID] [nvarchar] (12) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [AreaID] [int] NOT NULL ,
>> [AlarmID] [int] NULL ,
>> [Duration] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> Data:
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
>> BELine',1,8241,17484)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,6038,62)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,2042,52)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,8005,32)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,1013,28)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,3054,24)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,5005,24)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BUF1',1,1,17340)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,2,2056)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,8,856)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA01',1,18,6196)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,1,4924)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA01',1,200,4390)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,33,24)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,74,18)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,80,3920)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,73,2858)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,18,2214)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,203,458)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,74,346)
>>
>> Thanks in advance.
>> Anand
>
>.
>

Repost

I have a need to update data in a table so that
a column contains the generated number for sets of records
for instance I have fileName as listed and it should
generate the SheetNum for each of the set
FileName SheetNum
A001 1
A001 2
A002 1
A002 2
A002 3
A003 1
A003 2
B011 1
B011 2
B011 3
C189 1
D9090 1
Thank you in advance
-Kamy
.Kamy,
This will do it at least as you specified.
SET NOCOUNT ON
GO
IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
GO
CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
GO
INSERT test VALUES ('A001',0)
INSERT test VALUES ('A001',0)
INSERT test VALUES ('A002',0)
INSERT test VALUES ('A002',0)
INSERT test VALUES ('A002',0)
INSERT test VALUES ('A003',0)
INSERT test VALUES ('A003',0)
INSERT test VALUES ('B011',0)
INSERT test VALUES ('B011',0)
INSERT test VALUES ('B011',0)
INSERT test VALUES ('C189',0)
INSERT test VALUES ('D9090',0)
GO
ALTER TABLE dbo.test add Ctr int not null identity(1,1)
GO
SELECT t1.TestCol
, (SELECT COUNT(*) FROM test t2 WHERE t1.testcol = t2.testcol AND t1.Ctr
>= t2.Ctr)
FROM test t1
GO
ALTER TABLE dto.test DROP COLUMN Ctr
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Kamy" <anonymous@.discussions.microsoft.com> wrote in message
news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
> I have a need to update data in a table so that
> a column contains the generated number for sets of records
> for instance I have fileName as listed and it should
> generate the SheetNum for each of the set
> FileName SheetNum
> A001 1
> A001 2
> A002 1
> A002 2
> A002 3
> A003 1
> A003 2
> B011 1
> B011 2
> B011 3
> C189 1
> D9090 1
> Thank you in advance
> -Kamy
> .
>|||Thank you Ron,
I will try it out.
-Kamy
>--Original Message--
>Kamy,
>This will do it at least as you specified.
>SET NOCOUNT ON
>GO
>IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
>GO
>CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>GO
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('C189',0)
>INSERT test VALUES ('D9090',0)
>GO
>ALTER TABLE dbo.test add Ctr int not null identity(1,1)
>GO
>SELECT t1.TestCol
> , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol =t2.testcol AND t1.Ctr
>>= t2.Ctr)
>FROM test t1
>GO
>ALTER TABLE dto.test DROP COLUMN Ctr
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>"Kamy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> I have a need to update data in a table so that
>> a column contains the generated number for sets of
records
>> for instance I have fileName as listed and it should
>> generate the SheetNum for each of the set
>> FileName SheetNum
>> A001 1
>> A001 2
>> A002 1
>> A002 2
>> A002 3
>> A003 1
>> A003 2
>> B011 1
>> B011 2
>> B011 3
>> C189 1
>> D9090 1
>> Thank you in advance
>> -Kamy
>> .
>>
>
>.
>|||hi Ron,
It didn't work, as soon as I add an identity column,
it inserts running nos. into the field.
Am I missing something?
Thank you,
-Kamy
>--Original Message--
>Kamy,
>This will do it at least as you specified.
>SET NOCOUNT ON
>GO
>IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
>GO
>CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>GO
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('C189',0)
>INSERT test VALUES ('D9090',0)
>GO
>ALTER TABLE dbo.test add Ctr int not null identity(1,1)
>GO
>SELECT t1.TestCol
> , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol =t2.testcol AND t1.Ctr
>>= t2.Ctr)
>FROM test t1
>GO
>ALTER TABLE dto.test DROP COLUMN Ctr
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>"Kamy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> I have a need to update data in a table so that
>> a column contains the generated number for sets of
records
>> for instance I have fileName as listed and it should
>> generate the SheetNum for each of the set
>> FileName SheetNum
>> A001 1
>> A001 2
>> A002 1
>> A002 2
>> A002 3
>> A003 1
>> A003 2
>> B011 1
>> B011 2
>> B011 3
>> C189 1
>> D9090 1
>> Thank you in advance
>> -Kamy
>> .
>>
>
>.
>