Select specific line of record within query result

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 2008 Forums

Transact-SQL (2008)

Select specific line of record within query result

Author

Topic

fnc
Starting Member

4 Posts

Posted - 2010-04-18 : 23:17:01

Halo I have problem for my MS SQL 2008 project:

For example there is a tabel called transaction with 200 millions records, then user do select with filter range only specific date, itemid and transaction type, the user also want to sort the result records with itemgroup and itemid.

After that query, got 2 millions records of data, now within those 2 millions records result, I just want to take 10 records of it anywhere for example from records line 10050 - 10060 or record line 24537 - 24547 or whatever I like to choose the line from.

How can I do that in select statement?

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 23:19:12

[code]
select *
from
(
select *, row_no = row_number() over (order by somecol)
from sometable
) r
where r.row_no between 24537 and 24547
[/code]

KH
[spoiler]Time is always against us[/spoiler]

fnc
Starting Member

4 Posts

Posted - 2010-04-18 : 23:46:13

hi is row_no (row number) is field part of the table?

Because in my project I will never have row number field, because I'm not allowed to change the fields existing on that table. But anyway it will still wrong if presume I may add that field on the table, because what I've got within the 2 millions line of records result of the query may not ordered by the row number, which mean the number may not all available on the records result also the order line will surely not by row_no field, but by ItemGroup and ItemId which may scramble the result.

Actually I want to select record line ### - ### but without reference on a field such row number, because I will never have that field also I think it is not correct.

So is there any other solution?

Thanks you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 23:48:08

row_number() is a function that generates a continuous running number. It is not part of your table

refer to BOL ROW_NUMBER (Transact-SQL)

KH
[spoiler]Time is always against us[/spoiler]

fnc
Starting Member

4 Posts

Posted - 2010-04-19 : 00:45:54

wow great :)
so row_number() is a standard function exists on Ms SQL 2008 Server?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-19 : 00:48:13