Urgent - SQL Server Forums

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

Transact-SQL (2000)

Urgent

Author

Topic

smousumi
Starting Member

19 Posts

Posted - 2004-03-17 : 02:08:21

Hii,

I have to retreive a 500 records from a table using select
but i have to retreive 100 records each time for ex
first 1 to 100 then 101 to 200 and so on so how could it
be possible...pls suggest a solution

Thanks

mousumi

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-17 : 02:51:06

If you only want to retrieve 500 rows, I would retrieve them all with one single statement into an array and do the rest at the client. Depending on what your front-end language is, this should be fairly simple. You might also want to search for *paging*.

--Frank
http://www.insidesql.de

smousumi
Starting Member

19 Posts

Posted - 2004-03-17 : 03:28:15

Thanks Frank for ur rply but i want it to do in Query Analyzer i.e
at the back end so can u suggest a solution for this....

mousumi

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-17 : 03:36:46

Something like this?


select top 100 
	id 
from 
	mails_header 
order by 
	id 
desc

select top 100 
	id 
from 
	mails_header 
where 
	id not in(
		select top 100 
			id 
		from 
			mails_header 
		order by 
			id 
		desc)
order by 
	id 
desc

Change the NOT IN ...TOP XXX to whatever you like.

HTH

--Frank
http://www.insidesql.de

The first SELECT statement is just for my own verification. No need for it.

smousumi
Starting Member

19 Posts

Posted - 2004-03-17 : 03:46:27

Thanks Frank, But it is displaying only Last 200 records....

mousumi

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-03-17 : 03:53:21

what is it that u r trying to do?

check this :
http://www.sqlteam.com/item.asp?ItemID=566

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-17 : 04:08:38

Of course it is only displaying 200 rows. I left a little bit of work for you

--Frank
http://www.insidesql.de

smousumi
Starting Member

19 Posts

Posted - 2004-03-17 : 04:11:35

Hi,

I want the output like this...
Empid Empname sal
1 dddd 4000
2 uuuu 9000
to
100 jjj 5000
Then again
Empid Empname sal
101 eee 1000
to
200 eeee 2000
This way

mousumi

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-03-17 : 08:17:54

Create another table (temp table perhaps) of your data but include an identity column (if you don't have one already). So for 500 records you should have an 1-500 unique integers. Now all you need is select from your new table, like :


SELECT *
FROM myTable
WHERE myNewID between 1 and 100
order by myNewID
UNION ALL
SELECT *
FROM myTable
WHERE myNewID between 101 and 200
order by myNewID

... etc

________________
Make love not war!

smousumi
Starting Member

19 Posts

Posted - 2004-03-18 : 07:18:29

That all right, i got my part of my problem,

I had created a stored procedure, taking input as int and based on that input value only i want my top rows
like this.

Select top @N from <TableName>

but its givign error
"Incorrect syntax near '@N'."

Thisis the correct way or not...
Pls help

Thanks in advance

mousumi

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-18 : 07:39:26

[code]
DECLARE @stmt NVARCHAR(200)
DECLARE @no NVARCHAR(2)
SET @no = '10'
SET @stmt = 'SELECT TOP ' + @no + ' * FROM mails_header'
EXEC sp_executesql @stmt
[/code]
I think I have somewhere seen a workaround on avoid dynamic sql, but I can't find it right now.

--Frank
http://www.insidesql.de

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-18 : 07:49:43

Perhaps the following link will be useful for you situation:

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources