TOP Key word - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TOP Key word

Author

Topic

sqldoubt
Starting Member

17 Posts

Posted - 2010-07-06 : 14:40:10

I have the below query. I want to restrict the query to get top 10 records only. So how can i achiene it. I thought of TOP keyword. But I dont know how to use it.Any help plz.

select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from ( select 11 as StepID, cast(max(OrderClientHistoryid) as nvarchar(23)) as CounterValue from vw_OrderClientHistory_Work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from (
select 14 as StepID, cast(max(RowID) as nvarchar(23)) as CounterValue from vw_OrderItem_work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from ( select 15 as StepID, cast(max(OrderItemHistoryid) as nvarchar(23)) as CounterValue from vw_OrderItemHistory_Work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from (
select 18 as StepID, cast(max(RowID) as nvarchar(23)) as CounterValue from vw_OrderItemRevenue_work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from ( select 19 as StepID, cast(max(OrderItemRevenueHistoryid) as nvarchar(23)) as CounterValue from vw_OrderItemRevenueHistory_Work
) t
'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 16:20:04

TOP 10 according to what measure?

SELECT	@BatchID AS BatchID,
	u.theCol AS StepID,
	@RunID AS RunID,
	'ControlValue' AS CounterName,
	u.theValue AS CounterValue,
	GETDATE() AS CreatedDTim,
	GETDATE() AS ModifiedDTim
FROM	(
		SELECT	MAX(OrderClientHistoryid) AS [11],
			MAX(RowID) AS [14],
			MAX(OrderItemHistoryid) AS [15],
			MAX(RowID) AS [18],
			MAX(OrderItemRevenueHistoryid) AS [19]
		FROM	vw_OrderClientHistory_Work
	) AS s
UNPIVOT	(
		theValue
		FOR theCol IN (s.[11], s.[14], s.[15], s.[18], s.[19])
	) AS u

N 56°04'39.26"
E 12°55'05.63"

sqldoubt
Starting Member

17 Posts

Posted - 2010-07-06 : 22:02:19

This is the actual code. It still has continution.

For that i need to get top n records with rowid as measure.
also where ever there is max(rowId) i need to have the nth rowid.

Plz help.....

delete dbo.ImportStep where BatchID = @BatchID
set identity_insert dbo.ImportStep on

insert dbo.ImportStep
(StepID,BatchID,StepName,StepDesc,StepProcID
,OnSuccessID,OnFailureID,IgnoreErr,StepOrder
)
select 1 ,@BatchID,'ST01','Push Order',7,null,null,null,'1'
union all select 2 ,@BatchID,'ST02','Push Order Delete',7,3,null,null,'2'
union all select 101 ,@BatchID,'ST102','delete Order Destination',8,null,null,null,'3'
union all select 102 ,@BatchID,'ST103','delete Order Work',8,null,null,null,'4'
union all
select 3 ,@BatchID,'ST03','Push OrderHistory',7,3,null,null,'1'
union all select 202 ,@BatchID,'ST203','delete OrderHistory Work',8,null,null,null,'2'
union all
select 5 ,@BatchID,'ST05','Push OrderClient',7,null,null,null,'1'
union all select 6 ,@BatchID,'ST06','Push OrderClient Delete',7,3,null,null,'2'
union all select 301 ,@BatchID,'ST302','delete OrderClient Destination',8,null,null,null,'3'
union all select 302 ,@BatchID,'ST303','delete OrderClient Work',8,null,null,null,'4'
union all
select 7 ,@BatchID,'ST07','Push OrderClientAddress',7,null,null,null,'1'
union all select 8 ,@BatchID,'ST08','Push OrderClientAddress Delete',7,3,null,null,'2'
union all select 401 ,@BatchID,'ST401','delete OrderClientAddress Destination',8,null,null,null,'3'
union all select 402 ,@BatchID,'ST402','delete OrderClientAddress Work',8,null,null,null,'4'
union all
select 9 ,@BatchID,'ST09','Push OrderClientContact',7,null,null,null,'1'
union all select 10 ,@BatchID,'ST10','Push OrderClientContact Delete',7,3,null,null,'2'
union all select 501 ,@BatchID,'ST501','delete OrderClientContact Destination',8,null,null,null,'3'
union all select 502 ,@BatchID,'ST502','delete OrderClientContact Work',8,null,null,null,'4'
union all
select 11 ,@BatchID,'ST11','Push OrderClientHistory',7,3,null,null,'1'
union all select 602 ,@BatchID,'ST602','delete OrderClientHistory Work',8,null,null,null,'2'
union all
select 13 ,@BatchID,'ST13','Push OrderItem',7,null,null,null,'1'
union all select 14 ,@BatchID,'ST14','Push OrderItem Delete',7,3,null,null,'2'
union all select 701 ,@BatchID,'ST701','delete OrderItem Destination',8,null,null,null,'3'
union all select 702 ,@BatchID,'ST702','delete OrderItem Work',8,null,null,null,'4'
union all
select 15 ,@BatchID,'ST15','Push OrderItemHistory',7,3,null,null,'1'
union all select 802 ,@BatchID,'ST802','delete OrderItemHistory Work',8,null,null,null,'2'
union all
select 17 ,@BatchID,'ST17','Push OrderItemRevenue',7,null,null,null,'1'
union all select 18 ,@BatchID,'ST18','Push OrderItemRevenue Delete',7,3,null,null,'2'
union all select 901 ,@BatchID,'ST901','delete OrderItemRevenue Destination',8,null,null,null,'3'
union all select 902 ,@BatchID,'ST902','delete OrderItemRevenue Work',8,null,null,null,'4'
union all
select 19 ,@BatchID,'ST19','Push OrderItemHistory',7,3,null,null,'1'
union all select 1002 ,@BatchID,'ST1002','delete OrderItemHistory Work',8,null,null,null,'2'

union all select 99 ,@BatchID,'ST99','Control Counter Set -1 ',7,3,null,null,'0'
union all select 100 ,@BatchID,'ST100','Control Counter Set -2 ',7,3,null,null,'1'

set identity_insert dbo.ImportStep off
delete dbo.ImportStepAttribute where BatchID = @BatchID

--this query will be used to insert control value record

insert dbo.ImportStepAttribute
(StepID,BatchID,AttributeName,AttributeValue)
select 99,@BatchID,'Source.Query'
,'select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from (select 2 as StepID, cast(max(RowID) as nvarchar(23)) as CounterValue from vw_Order_work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from (select 3 as StepID, cast(max(OrderHistoryID) as nvarchar(23)) as CounterValue from vw_OrderHistory_Work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from ( select 6 as StepID, cast(max(RowID) as nvarchar(23)) as CounterValue from vw_OrderClient_work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from (select 8 as StepID, cast(max(RowID) as nvarchar(23)) as CounterValue from vw_OrderClientAddress_Work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from (select 10 as StepID, cast(max(RowID) as nvarchar(23)) as CounterValue from vw_OrderClientContact_Work
) t
'
union all select 99,@BatchID,'Source.Component','OLEDB'
union all select 99,@BatchID,'Source.Server','<AdETL_WorkServer*>'
union all select 99,@BatchID,'Source.Database','<AdETL_WorkDatabase*>'
union all select 99,@BatchID,'Action','MoveData'
union all select 99,@BatchID,'Destination.Component','OLEDB'
union all select 99,@BatchID,'DestinationTableName','dbo.ImportStepRunCounter'
union all select 99,@BatchID,'Destination.TableName','<LocalDB*>.<DestinationTableName>'
union all select 99,@BatchID,'Destination.Server','<LocalServer*>'
union all select 99,@BatchID,'Destination.Database','<LocalDB*>'
union all select 99,@BatchID,'Destination.Staging','0'
union all select 99,@BatchID,'Destination.UserOptions','append'
union all select 99,@BatchID,'Destination.OLEDB.AccessMode','OpenRowset'
union all select 99,@BatchID,'PRIGROUP','1'
union all select 99,@BatchID,'DISABLED','0'
union all select 99,@BatchID,'RETRY','1'
union all select 99,@BatchID,'DELAY','30'
union all select 99,@BatchID,'RESTART','1'
union all select 99,@BatchID,'SEQGROUP','1'
union all select 99,@BatchID,'SQL_01','<Counter_Set>'
union all select 99,@BatchID,'Counter_Set','
declare @s nvarchar(23)
set @s = isnull(dbo.fn_cdrCounterGet(<@BatchID>,2,<@RunID>,''ControlValue''),''<ControlValue>'')
exec prc_cdrAttributeSet <@BatchID>,1,null,''MaxControlValue'',@s
exec prc_cdrAttributeSet <@BatchID>,2,null,''MaxControlValue'',@s

set @s = isnull(dbo.fn_cdrCounterGet(<@BatchID>,3,<@RunID>,''ControlValue''),''<ControlValue>'')
exec prc_cdrAttributeSet <@BatchID>,3,null,''MaxControlValue'',@s

set @s = isnull(dbo.fn_cdrCounterGet(<@BatchID>,6,<@RunID>,''ControlValue''),''<ControlValue>'')
exec prc_cdrAttributeSet <@BatchID>,5,null,''MaxControlValue'',@s
exec prc_cdrAttributeSet <@BatchID>,6,null,''MaxControlValue'',@s

set @s = isnull(dbo.fn_cdrCounterGet(<@BatchID>,8,<@RunID>,''ControlValue''),''<ControlValue>'')
exec prc_cdrAttributeSet <@BatchID>,7,null,''MaxControlValue'',@s
exec prc_cdrAttributeSet <@BatchID>,8,null,''MaxControlValue'',@s

set @s = isnull(dbo.fn_cdrCounterGet(<@BatchID>,10,<@RunID>,''ControlValue''),''<ControlValue>'')
exec prc_cdrAttributeSet <@BatchID>,9,null,''MaxControlValue'',@s
exec prc_cdrAttributeSet <@BatchID>,10,null,''MaxControlValue'',@s'

insert dbo.ImportStepAttribute
(StepID,BatchID,AttributeName,AttributeValue)
select 100,@BatchID,'Source.Query'
,'
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from ( select 11 as StepID, cast(max(OrderClientHistoryid) as nvarchar(23)) as CounterValue from vw_OrderClientHistory_Work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from (
select 14 as StepID, cast(max(RowID) as nvarchar(23)) as CounterValue from vw_OrderItem_work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from ( select 15 as StepID, cast(max(OrderItemHistoryid) as nvarchar(23)) as CounterValue from vw_OrderItemHistory_Work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from (
select 18 as StepID, cast(max(RowID) as nvarchar(23)) as CounterValue from vw_OrderItemRevenue_work
) t
union
select <@BatchID> as BatchID,t.StepID,<@RunID> as RunID,''ControlValue'' as CounterName,t.CounterValue,getdate() as CreatedDTim,getdate() as ModifiedDTim
from ( select 19 as StepID, cast(max(OrderItemRevenueHistoryid) as nvarchar(23)) as CounterValue from vw_OrderItemRevenueHistory_Work
) t
'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources