select partial data using T-SQL

Hey Experts,

Is it possible to select partial data using T-SQL.
for example
1. ignore first 2 rows in the table. only select everything from 3rd row to last row
2. only select row5 to row10

rmtogetherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
Here is wild guess! since I have no SQL Server now...
Have a try

>> 1. ignore first 2 rows in the table. only select everything from 3rd row to last row

SELECT * FROM
(
SELECT *, ROWNUMBER() OVER (ORDER BY ID) AS ROWNO
FROM Yourtable
) A
WHERE ROWNO%3 = 0

Raj
0
Rajkumar GsSoftware EngineerCommented:
>>2. only select row5 to row10

SELECT * FROM
(
SELECT *, ROWNUMBER() OVER (ORDER BY ID) AS ROWNO
FROM Yourtable
) A
WHERE ROWNO >=5 AND ROWNO <=10

Let me know
Raj
0
Rajkumar GsSoftware EngineerCommented:
>>2. only select row5 to row10

Another method, which may work in all SQL database.

SELECT TOP 5 * FROM
(
SELECT TOP 10 * FROM Yourtable ORDER BY ID
)
ORDER BY ID DESC

Yourtable - rename to your actual table name.
ID - Primary key - rename to yours

Raj
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

rmtogetherAuthor Commented:
thank you Raj you always doing so fast. ^_^

I have problem to run you code. I think the problem is in "ORDER BY ID".

I would like assume that there is no "ID" column in the original table.
I think it is possible to automatically or temporarily generate additional ID column before process. Could you please teach me how to do it?

thanks
0
Rajkumar GsSoftware EngineerCommented:

There is no need to an ID column to achieve your requirement.

Based on which column you want to sort ?

Raj
0
rmtogetherAuthor Commented:
Hi, Raj

you mention ID - is Primary key

actually I am using TSQL to get a "dirty" excel file. so there is no primary key on it. and I don't need do any sorting for them.

I am doing this because there is some part of data (rows) in excel that I don't need. So I would like use TSQL to select certain parts (rows) of data

can I use ROW_NUMBER()  function to automatically generate an extra column for "ID" ?
0
Rajkumar GsSoftware EngineerCommented:
Rename ROWNUMBER in my queries to ROW_NUMBER

SELECT ROW_NUMBER()  OVER (      ORDER BY <somecolumn>) AS ID
FROM YourTable

This will generate Identity values based on the sorting of <somecolumn>

Raj
0
SharathData EngineerCommented:
try this
--1. ignore first 2 rows in the table. only select everything from 3rd row to last row
select * 
  from (select *,row_number() over (order by (select 1)) rn 
          from your_table) t1 
 where rn > 3

--2. only select row5 to row10
select * 
  from (select *,row_number() over (order by (select 1)) rn 
          from your_table) t1 
 where rn between 5 and 10

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rmtogetherAuthor Commented:
Hi, Raj

I need get exactly data location  from the excel, so sorting of  could possible make me get wrong rows. Thank you for your help I appreciate
0
rmtogetherAuthor Commented:
thank you, Sharath_123

it works
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.