gather top 5 results, but not the first

[tyutghf]

Is it possible in SQL to gather the top 5 results but excluding the first one? So I need to actually grab rows 2-6?

 

[markros]

In SQL Server 2005 and up:

;with cte as (select *, row_number() over (order by FieldstoOrder) as Row from myTable)

select * from cte where Row between 2 and 6

PluralSight Learning Library

 

[tyutghf]

Sorry, should have said that I am on 2000

 

[r937]

why don't you return the top 6 and simply skip over the first one?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

SELECT TOP 5 *
FROM (SELECT TOP 6 * FROM YourTable
             ORDER BY SomeField) Tbl1
ORDER BY SomeField DESC

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[tyutghf]

thanks for that, I am almost getting what I need but I want them in id order, the newest first but if I order by asc it is still pulling the first one, if I order by desc it shows the 5th newest, followed by forth etc

select TOP 5 * from (select TOP 6 * from table order by id desc) tbl1 order by id

gives

result 6
result 5
result 4
result 3
result 2

but I need them in the order

result 2
result 3
result 4
result 5
result 6


r937 - I could do that, how would I do it in. asp?

 

[markros]

select top 5 * from ... where MyField < (select Top 1 Field order by Field) order by Field

PluralSight Learning Library

 

[r937]

r937 - I could do that, how would I do it in. asp?

sorry, i don't do asp :-(


would you like to see how to do it in coldfusion?


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

SELECT * 
FROM(select TOP 5 *
       from (select TOP 6 * 
                    from table
             order by id) tbl1 
     order by id  desc) Tbl2
order by id

Other way is like markros said:

select TOP 5 *
       from table
WHERE Id > (SELECT TOP 1 Id FROM Table)
order by id

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[r937]

Other way is like markros said:

but markros had an ORDER BY in the subquery, which you forgot, and without which it won't work




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

Yes, it wont work w/o ORDER BY
BTW markros also had a typo


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Missed from myTable in the subquery - noticed, but decided since it's a pseudo-code, doesn't matter much anyway.

PluralSight Learning Library

 

[bborissov]

I missed that

I saw this:

MyField [COLOR=red][b]<[/b][/color](select Top 1 Field order by Field)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.