?
Solved

select partial data using T-SQL

Posted on 2010-04-06
10
Medium Priority
?
361 Views
Last Modified: 2012-05-09
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

0
Comment
Question by:rmtogether
  • 5
  • 4
10 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 29971472
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 29971648
>>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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 29972214
>>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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:rmtogether
ID: 29973610
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 29980012

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

Based on which column you want to sort ?

Raj
0
 

Author Comment

by:rmtogether
ID: 29980842
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
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 600 total points
ID: 29982306
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
 
LVL 41

Accepted Solution

by:
Sharath earned 1400 total points
ID: 29986127
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
 

Author Closing Comment

by:rmtogether
ID: 31711645
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
 

Author Comment

by:rmtogether
ID: 30026789
thank you, Sharath_123

it works
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question