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
>
>.
>