how to select middle row in table

Question

how to select middle row in table

khushbu

SSCarpal Tunnel

Points: 4591
More actions
August 21, 2008 at 7:03 am

#70650

I want to select n row from a table
where n can be any number...
say in a table there are 100 rows , now i want to see only 10 row..
how can it be done
All i know in table is its col name

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

khushbu.kumar (8/21/2008)
I want to select n row from a table
where n can be any number...
say in a table there are 100 rows , now i want to see only 10 row..
how can it be done
All i know in table is its col name

Is there any kind of ordering available in the table? if, so you can use a where clause and maybe a TOP as well.

e.g.

select name from sysobjects where id>50 and id<100 order by id

or with TOP

select top 100 name from sysobjects where id>50

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 2

no ordering

and i just need to display 4 row.. not the other row

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

That's an interesting question because it depends on what you expect row 10 to be. SQL does not guarantee order unless you specify an ORDER BY. In most cases data will be returned in order of the clustered index, but with parallel you may not get that order. So tell me how you will identify it is the 10th row. Do you have an identity column? Since this is a 2000 forum we can't use the row_number function. Something like this might work:

[font="Courier New"]DECLARE @data TABLE(id INT IDENTITY(1,1), data VARCHAR(100))

INSERT INTO @data

(

data

)

SELECT TOP 10

name

FROM

MASTER.dbo.syscolumns

SELECT

*

FROM

@data

WHERE

id = 10[/font]

There might be a better way, but I don't know it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 4

khushbu.kumar (8/21/2008)
no ordering
and i just need to display 4 row.. not the other row

if there is no ordering, then there is no guarantee that the next time you execute the query you will be able to get the same four rows (consider your table to be a set). Basically, it is almost like selecting four random rows, almost like:

select top 4 name from sysobjects order by newid()

if you want to make it repeatable and have no ordering, you may need to dump the data into a temp table with some ordering column, etc.

Could you describe us what you are trying to do? Is it just getting four random rows? Is it for paging? Does it need to be repeatable? The four rows you need, do they need to be in sequence (although this already assumes some ordering :))

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

steveb. SSC-Forever Points: 46733 More actions · Answer 5

khushbu.kumar (8/21/2008)
no ordering
and i just need to display 4 row.. not the other row

Without any ID or order it is hard to see why you would need this, but anyway try

SELECT TOP 4 *

FROM YourTable

EXCEPT

SELECT TOP 3 *

FROM YourTable

note: I am assuming EXCEPT works in 2000...