America
Suppose I have a table with 100 records.. and I wantto get the 3rd record from that table. How can I do it
Re: Get certain raw row from table
hunchback
There is no such concept in SS, a table is an unordered set of rows and columns. If there is a group of columns that we can use to uniquely identify each row, then you can use new ranking functions.
;with cte
as
(
select *, row_number() over(order by pk_col) as rn
from dbo.t1
)
select *
from cte
where rn = 3;
AMB
Re: Get certain raw row from table
America
hmm... mb there is somewhere much simpler solusion in my situation... I have a table with one column and I would like to pass records to procedure as parameters
hmm... mb there is somewhere much simpler solusion in my situation... I have a table with one column and I would like to pass records to procedure as parameters
Code Bloc
CREATE PROCEDURE MyProcedure @h
AS
BEGIN
....
WHILE (@i < @RawCount)
BEGIN
....
END
CREATE PROCEDURE MyProcedure @h
AS
BEGIN
....
WHILE (@i < @RawCount)
BEGIN
SET @i = @i +1
EXECUTE myProcedure -- put here value from i-record
ENDEXECUTE myProcedure -- put here value from i-record
....
END
Re: Get certain raw row from table
John V. McCarthy
From your response I'm not sure you understood what AMB was suggesting. Let's say that I have a single column table and that this is a simple list of the state names for the USA. There is not default order for these values. This is why in a normal database design you'll have a primary key column in addition to whatever other information you're trying to represent.
Assuming that you don't want to change the way that you have your table set up for whatever reason then you will need to pick some arbitary way to enforce an order on your data. The way that you do this is to order the column. If I wanted to get the fourth state name from my imaginary table what I would do is this.
i = the position you want
select top 1 stateName from tblStates
where stateName in (selec top i stateName from tblStates order by stateNames desc)
order by stateNames asc
This is how you would need to do it in SQL 2000.
Please keep in mind though that like AMB said this ordering is somewhat fake and is imposed by you. The only reason there is a logical order is because you've ordered the result by the value. The Nth result you get back from this may very well not by the Nth result you created in the table but if you're just trying to cycle through the records or if the simple ordering of the values you have is what you need then this will work.
John V. McCarthy
Re: Get certain raw row from table
America
Thanks for help. But tried this:
Thanks for help. But tried this:
select top 1 stateName from tblStates
where stateName in (selec top i stateName from tblStates order by stateNames desc)
order by stateNames asc
and it has error at i, I tried @i the same error.
Re: Get certain raw row from table
DaleJ
You need to declare and set @i before you can use it.
Then you need to enclose it in parands within the SELECT statement.
As was pointed out, SS doesn't guarantee the order of a result set unless you specify an Order By clause.
If you need to work on a row level operation then you could use a CURSOR.
But most times, what people do with cursors is easily done with set-based operations.
You need to declare and set @i before you can use it.
Then you need to enclose it in parands within the SELECT statement.
Code Block
declare @tblStates table (stateName varchar(50))
insert into @tblStates
select 'Florida'
union all select 'Georgia'
union all select 'Alabama'
union all select 'South Carolina'
declare @i int
set @i = 3
select top 1 stateName from @tblStates
where stateName in (select top (@i) stateName from @tblStates order by stateName desc)
order by stateName asc
insert into @tblStates
select 'Florida'
union all select 'Georgia'
union all select 'Alabama'
union all select 'South Carolina'
declare @i int
set @i = 3
select top 1 stateName from @tblStates
where stateName in (select top (@i) stateName from @tblStates order by stateName desc)
order by stateName asc
As was pointed out, SS doesn't guarantee the order of a result set unless you specify an Order By clause.
If you need to work on a row level operation then you could use a CURSOR.
But most times, what people do with cursors is easily done with set-based operations.
Re: Get certain raw row from table
America
I don't know why but I got errors:
-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'SubordinationInfo'
AND type = 'P')
DROP PROCEDURE SubordinationInfo
GO
CREATE PROCEDURE SubordinationInfo @EmployeeID int = NULL, @level int = 0
AS
BEGIN
--printing Employee:
DECLARE @Employee varchar(40)
SET @Employee= (SELECT FirstName +' '+ LastName
FROM Northwind.dbo.Employees AS a
WHERE a.EmployeeID = @EmployeeID)
PRINT replicate(' ',@level) + @Employee
--======================================================
--printing childs:
DECLARE @ChildsAmount int
SET @ChildsAmount = (SELECT COUNT(EmployeeID)
FROM Northwind.dbo.Employees
WHERE ReportsTo = @EmployeeID)
DECLARE @Child int
SET @Child = 1
DECLARE @Subordinator int
WHILE(@Child <= @ChildsAmount)
BEGIN
SET @Subordinator =
(
SELECT TOP 1 EmployeeID
FROM Northwind.dbo.Employees
WHERE EmployeeID IN (SELECT TOP (@Child) EmployeeID
FROM Northwind.dbo.Employees
ORDER BY EmployeeID DESC)
ORDER BY EmployeeID ASC
)
SET @Child = @Child + 1
PRINT @Child+ ' ' @Subordinator
END
END
GO
-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE SubordinationInfo 2
GO
ERORS
if ....TOP @Child....
Server: Msg 170, Level 15, State 1, Procedure SubordinationInfo, Line 57
Line 57: Incorrect syntax near '@i'.
if ....TOP (@Child) ....
Server: Msg 170, Level 15, State 1, Procedure SubordinationInfo, Line 57
Line 57: Incorrect syntax near '('.
I don't know why but I got errors:
-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'SubordinationInfo'
AND type = 'P')
DROP PROCEDURE SubordinationInfo
GO
CREATE PROCEDURE SubordinationInfo @EmployeeID int = NULL, @level int = 0
AS
BEGIN
--printing Employee:
DECLARE @Employee varchar(40)
SET @Employee= (SELECT FirstName +' '+ LastName
FROM Northwind.dbo.Employees AS a
WHERE a.EmployeeID = @EmployeeID)
PRINT replicate(' ',@level) + @Employee
--======================================================
--printing childs:
DECLARE @ChildsAmount int
SET @ChildsAmount = (SELECT COUNT(EmployeeID)
FROM Northwind.dbo.Employees
WHERE ReportsTo = @EmployeeID)
DECLARE @Child int
SET @Child = 1
DECLARE @Subordinator int
WHILE(@Child <= @ChildsAmount)
BEGIN
SET @Subordinator =
(
SELECT TOP 1 EmployeeID
FROM Northwind.dbo.Employees
WHERE EmployeeID IN (SELECT TOP (@Child) EmployeeID
FROM Northwind.dbo.Employees
ORDER BY EmployeeID DESC)
ORDER BY EmployeeID ASC
)
SET @Child = @Child + 1
PRINT @Child+ ' ' @Subordinator
END
END
GO
-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE SubordinationInfo 2
GO
ERORS
if ....TOP @Child....
Server: Msg 170, Level 15, State 1, Procedure SubordinationInfo, Line 57
Line 57: Incorrect syntax near '@i'.
if ....TOP (@Child) ....
Server: Msg 170, Level 15, State 1, Procedure SubordinationInfo, Line 57
Line 57: Incorrect syntax near '('.
Re: Get certain raw row from table
DaleJ
The error seems to be related to a missing + in the print statement
The error seems to be related to a missing + in the print statement
PRINT @Child+ ' ' + @Subordinator
Re: Get certain raw row from table
America
Thank you for responce. I tried dynamic query... but
-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'SubordinationInfo'
AND type = 'P')
DROP PROCEDURE SubordinationInfo
GO
CREATE PROCEDURE SubordinationInfo @EmployeeID int = NULL, @level int = 0
AS
BEGIN
--printing Employee:
DECLARE @Employee varchar(40)
SET @Employee= (SELECT FirstName +' '+ LastName
FROM Northwind.dbo.Employees AS a
WHERE a.EmployeeID = @EmployeeID)
PRINT replicate(' ',@level) + @Employee
--======================================================
--printing childs:
DECLARE @ChildsAmount int
SET @ChildsAmount = (SELECT COUNT(EmployeeID)
FROM Northwind.dbo.Employees
WHERE ReportsTo = @EmployeeID)
DECLARE @Child int
SET @Child = 1
DECLARE @Subordinator int
WHILE(@Child <= @ChildsAmount)
BEGIN
--dynamic query
DECLARE @sql varchar(255)
SET @sql = 'SELECT TOP ' +CONVERT(VARCHAR,@Child)
+' EmployeeID '
+' FROM Northwind.dbo.Employees '
+' ORDER BY EmployeeID DESC '
SET @Subordinator =
(
SELECT TOP 1 EmployeeID
FROM Northwind.dbo.Employees
WHERE EmployeeID IN (EXEC(@sql))
ORDER BY EmployeeID ASC
)
SET @Child = @Child + 1
PRINT @Child+ ' '+ @Subordinator
END
END
GO
-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE SubordinationInfo 2
GO
Thank you for responce. I tried dynamic query... but
-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'SubordinationInfo'
AND type = 'P')
DROP PROCEDURE SubordinationInfo
GO
CREATE PROCEDURE SubordinationInfo @EmployeeID int = NULL, @level int = 0
AS
BEGIN
--printing Employee:
DECLARE @Employee varchar(40)
SET @Employee= (SELECT FirstName +' '+ LastName
FROM Northwind.dbo.Employees AS a
WHERE a.EmployeeID = @EmployeeID)
PRINT replicate(' ',@level) + @Employee
--======================================================
--printing childs:
DECLARE @ChildsAmount int
SET @ChildsAmount = (SELECT COUNT(EmployeeID)
FROM Northwind.dbo.Employees
WHERE ReportsTo = @EmployeeID)
DECLARE @Child int
SET @Child = 1
DECLARE @Subordinator int
WHILE(@Child <= @ChildsAmount)
BEGIN
--dynamic query
DECLARE @sql varchar(255)
SET @sql = 'SELECT TOP ' +CONVERT(VARCHAR,@Child)
+' EmployeeID '
+' FROM Northwind.dbo.Employees '
+' ORDER BY EmployeeID DESC '
SET @Subordinator =
(
SELECT TOP 1 EmployeeID
FROM Northwind.dbo.Employees
WHERE EmployeeID IN (EXEC(@sql))
ORDER BY EmployeeID ASC
)
SET @Child = @Child + 1
PRINT @Child+ ' '+ @Subordinator
END
END
GO
-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE SubordinationInfo 2
GO