Selecting row count - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting row count

Author

Topic

nickthurg28
Starting Member

4 Posts

Posted - 2007-06-29 : 07:52:09

Afternoon

I know how to do a row count to select the total amount of cells in a table, as the table i am using doesn't actually have a good incrementing PK to use, don't ask!

I would like to, after this row count, select the 1000th record for example, how would i do that without creating a temporary table?

Hope i've made it clearish

Nick

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-29 : 07:57:14

Article discussion is a forum for comments on existing articles. moved from Article discussion
I assumes you're using sql 2005

On with your question:

select top 1 *
from (
select top 1000 *
from MyTable
order by OrderColumn
) t
order by OrderColumn desc

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

nickthurg28
Starting Member

4 Posts

Posted - 2007-06-29 : 10:38:51

Hi

I'm just using the analyser, nice and easy. Erm yeah thats good but say i wanted the 50,000th record out of 100,000 records, or whatever number i want?

How easy is that?

mobius
Starting Member

13 Posts

Posted - 2007-06-29 : 11:05:42

select top 1 *
from(
select top 50000 *
from (
select top 100000 * --<===== Or whatever number you want.
from MyTable
order by OrderColumn
) t
order by OrderColumn desc
) u

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-30 : 09:17:00

Also search for Pagination

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-30 : 13:09:40

Here is a SQL Server 2005 and later solution

SELECT <Col list here> FROM (
SELECT <Col list here>, ROW_NUMBER() OVER (ORDER BY SomeCol) AS RecID FROM MyTable
) AS d WHERE RecID = 1000

Peter Larsson
Helsingborg, Sweden

nickthurg28
Starting Member

4 Posts

Posted - 2007-07-02 : 04:44:31

Ahhh yes i think we've got a few crossed wires here, probably me not explaining fully.

I do the row count to get the total amount of records (X), then from that row count of X i want to pull the record of Y. So say a total of 127,000 records rowcount X. I want to pull back the 97,149 record Y.

I've got some mamoth amount of code using temp variables etc but when i run it the record details keep changing, its very strange indeed

Nick

Kristen
Test

22859 Posts

Posted - 2007-07-02 : 05:06:41

What's wrong with Spirit's suggestion?

select top 1 *
from (
select top 97149 *
from MyTable
order by OrderColumn
) t
order by OrderColumn desc

Kristen

nickthurg28
Starting Member

4 Posts

Posted - 2007-07-02 : 10:31:18

Nothing at all, i was confusing myself with the total amount of records as there really is no need for that method. I found the select how many you want, flip it round and select the top record quite astute. God way around, now all i need is a bit of testing and roll it out

Cheers

Nick

Kristen
Test

22859 Posts

Posted - 2007-07-02 : 10:44:42

"Nothing at all"

That's excellent then!

In SQL 2005 you can do SELECT TOP @MyVariable which you couldn't do in SQL 2000.

If you are using dynamic SQL then your should be OK with either platform, but if you are trying to do it for example from an SProc WITHOUT Dynamic SQL then you can probably fudge something using SET ROWCOUNT

SET ROWCOUNT 97149 -- This you CAN do as SET ROWCOUNT @MyVariable
select top 1 *
from (
select *
from MyTable
order by OrderColumn
) t
order by OrderColumn desc

SHOULD work (but I ain't tested it!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources