i feel that limit select is very problematic thing

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

SQL Server Development (2000)

i feel that limit select is very problematic thing

Author

Topic

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-11 : 12:07:34

half of day i spending in internet searching answer , how to make limited select, i realy was surprised, because no one can help me, all, some people told me very curious things,

very strange, on my php i'am using function selectlimit, but i dont know how exactly this function work, i can find anywhere detailed description of this function

there is this function

SelectLimit($sql,$numrows=-1,$offset=-1)

there is this function in action
$connection->SelectLimit('SELECT * FROM TABLE',3,2)
=======================================================

in my page people can do different things, they can use filters , choose count of searched fields, this is similar to sql build

but yes, one word said people build on my page sql

result of sql always is one , in result people see ( for example) only returned ID

and my question is how to make limited select, i readed BOL but top cannot helps me with my task ( i think so )

i really cannot find out, and only one hope is that somebody can understand my problem ad realy help ???

when my query is builded only one part always is static in sql
this is

select id
rom table
where

but where conditions and order by conditions are always unknown for me because they formed by client

and when this query is builded, then people on my page choose

1-5, 5-10, 10-20

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-11 : 15:21:32

If you search SQL Team for "top":

http://www.sqlteam.com/SearchResults.asp?SearchTerms=top

There's a number articles, especially (no pun intended) the TOP one.

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-12 : 02:42:19

SELECT TOP 10 ID
FROM TEST_A
WHERE ID IN (
SELECT TOP 90 ID
FROM TEST_A
ORDER BY ID DESC
)
ORDER BY ID ASC
==========================================================

by using this variant, i get result
from 11-20

but how you think ? it is normal way for solving this problem ????

maybe are other ways , more fastter ????

thanks !

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-13 : 05:06:21

SELECT TOP 10 Id
FROM Table1
WHERE Id in (SELECT TOP 20 Id
FROM Table1
ORDER BY Quantity DESCENDING)
ORDER BY Quantity ASCENDING

this example depends on ORDER statements,
but my clients forming her query trought my page,

in my page WHERE and ORDER statements are dynamiccaly created, and i dont know what exactly is choosed , for example client can choose

order by img_id asc /desc or ........

any of you have solved problem with limited select, where order statements are dynamically created ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-13 : 07:41:38

marconi -- there has to be some concept of ORDERING for you to page records! Does that make sense? It isn't just random! If it is, then how can you know where you've been and what you've shown, and what you need to show?

If you want to just return results in a random order, put them into a temp table with an identity field in any order you want and then filter your results based on the ID in the identity field.

For multi-users, this will be a problem, though.

Also, look at this post for a way to return rows @a through @b of a resultset. (WHICH MUST HAVE DISTINCT ORDERING, OF COURSE)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25496

- Jeff

Edited by - jsmith8858 on 05/13/2003 07:44:14

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-13 : 08:33:40

my solution for my task at this moment is next

--------------------------------------------------------------
@SQL = nvarchar, this variable i use in dynamic sql

@O_STAT = dynamically created ORDER Conditions ( this conditions build clients )

@W_STAT = dynamically created WHERE Conditions ( this conditions
build clients )
--------------------------------------------------------------

 
SET @SQL=
         ' SELECT ID,F1,F2,..... FROM T1'			
	+' WHERE ID IN ('
		        +' SELECT TOP '+CAST(@SAG AS CHAR)+' ID FROM T1' 
	                     +' WHERE '+@W_STAT
                                  +' AND ID IN ('
			            +' SELECT TOP '+CAST(@C_COUNT AS 
                                                                                  CHAR)+' ID FROM T1'
		 	            +' WHERE '+@W_STAT
		 	            +' ORDER BY START_DATA ASC'
			            +' )'	
		        +' ORDER BY START_DATA DESC'
	  	        +' )'
             +' ORDER BY '+@O_STAT

my solution = 3 select statement, this 3 select don't give me a normal speed, can you something advice ,, how can i make from this 3 selects more fast select....

i'am going to make this 3 selects more faster

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources