How can i get the 2nd, 3rd or N''th row from a table?

This would be a TOP Clause question.

if we use TOP clause for example;

SELECT Top(4) Col_A, Col_B

FROM Table_A

ORDER BY Col_A

This query gets the first 4 rows according to the order of Col_A.

How can i get only the second or the third row from this table?

Thanks for your helps...

--2
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 1 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]

--3
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 2 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]|||

Hi phokaia,

you can try the sample code as below:

--Start : Create sample db,table,data --

Create Database d1
go

use d1
go

Create table t1
(c1 int ,
c2 char(10)
)
go

insert t1 values (1,'a')
insert t1 values (2,'a')
insert t1 values (3,'a')
insert t1 values (4,'a')
go

-- In SQL2K, you can try this

declare @.i int
declare @.c char(10)

declare cur_top cursor SCROLL for select * from t1 order by c1
open cur_top;

Fetch ABSOLUTE 3 from cur_top into @.i,@.c
close cur_top;
deallocate cur_top;

select @.i,@.c

-- OR --

In SQL2K05, you can try this , if you need the 3rd row.

Select *
from (select *,'RowNum'=ROW_NUMBER() OVER (ORDER BY c1)
　　　from t1) temptable
where RowNum=3

try it

hoping this can help you.

Best Regrads,

Hunt.

How can i get the 2nd, 3rd or N''th row from a table?

This would be a TOP Clause question.

if we use TOP clause for example;

SELECT Top(4) Col_A, Col_B

FROM Table_A

ORDER BY Col_A

This query gets the first 4 rows according to the order of Col_A.

How can i get only the second or the third row from this table?

Thanks for your helps...

--2
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 1 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]

--3
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 2 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]|||

Hi phokaia,

you can try the sample code as below:

--Start : Create sample db,table,data --

Create Database d1
go

use d1
go

Create table t1
(c1 int ,
c2 char(10)
)
go

insert t1 values (1,'a')
insert t1 values (2,'a')
insert t1 values (3,'a')
insert t1 values (4,'a')
go

-- In SQL2K, you can try this

declare @.i int
declare @.c char(10)

declare cur_top cursor SCROLL for select * from t1 order by c1
open cur_top;

Fetch ABSOLUTE 3 from cur_top into @.i,@.c
close cur_top;
deallocate cur_top;

select @.i,@.c

-- OR --

In SQL2K05, you can try this , if you need the 3rd row.

Select *
from (select *,'RowNum'=ROW_NUMBER() OVER (ORDER BY c1)
　　　from t1) temptable
where RowNum=3

try it

hoping this can help you.

Best Regrads,

Hunt.

How can i get the 2nd, 3rd or N''th row from a table?

This would be a TOP Clause question.

if we use TOP clause for example;

SELECT Top(4) Col_A, Col_B

FROM Table_A

ORDER BY Col_A

This query gets the first 4 rows according to the order of Col_A.

How can i get only the second or the third row from this table?

Thanks for your helps...

--2
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 1 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]

--3
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 2 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]|||

Hi phokaia,

you can try the sample code as below:

--Start : Create sample db,table,data --

Create Database d1
go

use d1
go

Create table t1
(c1 int ,
c2 char(10)
)
go

insert t1 values (1,'a')
insert t1 values (2,'a')
insert t1 values (3,'a')
insert t1 values (4,'a')
go

-- In SQL2K, you can try this

declare @.i int
declare @.c char(10)

declare cur_top cursor SCROLL for select * from t1 order by c1
open cur_top;

Fetch ABSOLUTE 3 from cur_top into @.i,@.c
close cur_top;
deallocate cur_top;

select @.i,@.c

-- OR --

In SQL2K05, you can try this , if you need the 3rd row.

Select *
from (select *,'RowNum'=ROW_NUMBER() OVER (ORDER BY c1)
　　　from t1) temptable
where RowNum=3

try it

hoping this can help you.

Best Regrads,

Hunt.

How can I get a row count of CTE recordset?

Hi everybody,
I want to get a row count of temporary CTE recordset.
My query is like this:

With SQLPaging As
(
Select Row_number() Over (Order by Row_num) as RowNumber,
Row_num,
Column_A,
Column_B From OriginalTable
)
Select * from SQLPaging Where RowNumber between 21 and 30

Let's assume SQLPaging definition contains 1,000 rows.

If I use @.@.rowcount, it returns only 10. I.e. number of actual rows per page.
What I want is that I want to know row count of SQLPaging CTE without extra counting query.
I think SQLServer already knows how many rows CTE definition contains.
The reason that I don't want to use extra counting query is that if SQLPaging definition has row performance query (like wild card(%) character search), extra row counting query takes simillar amount of loads. (Select count(*) from SQLPaging)

Is there any way of getting actual CTE definition row count?

Thanks.

Try using count(*) over()

With SQLPaging As
(
Select Row_number() Over (Order by Row_num) as RowNumber,
count(*) over() as MaxNum, --<<<<
Row_num,
Column_A,
Column_B From OriginalTable
)
Select * from SQLPaging Where RowNumber between 21 and 30

|||Yes, use Mark's answer - it's most definitely the one you want.

The great thing about this is that you won't find yourself having a massive performance hit from doing it this way. It will do it in the same pass.

Rob|||Cool! Thanks guys. I really appreciate it. :)

How can I find when a password is close to expiring

I am using VB6 with SQL server 2005. In order to implement password expiry properly I need to know how to find out when a user's password is due to expire so that I can output a message to prompt him to change his password. How can I interrogate this information?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1189633&SiteID=1

cheers

U

How can I find when a password is close to expiring

I am using VB6 with SQL server 2005. In order to implement password expiry properly I need to know how to find out when a user's password is due to expire so that I can output a message to prompt him to change his password. How can I interrogate this information?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1189633&SiteID=1

cheers

U