August 21, 2008 at 7:03 am
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
August 21, 2008 at 7:11 am
khushbu.kumar (8/21/2008)
I want to select n row from a tablewhere 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
August 21, 2008 at 7:15 am
no ordering
and i just need to display 4 row.. not the other row
August 21, 2008 at 7:20 am
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
August 21, 2008 at 7:23 am
khushbu.kumar (8/21/2008)
no orderingand 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
August 21, 2008 at 7:38 am
khushbu.kumar (8/21/2008)
no orderingand 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...
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply