Display records sequentially without repetition

[jawad malik]

I want that each time i execute query it display records in a way that the top record will be positioned last and so on.
First time when i executed query it will display like this
name
A
B
C
D
second time
name
B
C
D
A
third time
name
C
D
A
B
fourth time
name
D
A
B
C
fifth time
A
B
C
D and so on

[NeoPa]

Without getting into the realms of pretty complicated programming where the data is manipulated every time (in other words this could not be triggered simply by the execution of a query), this is not possible.

[ck9663]

May I know what you will need this for?

~~ CK

[jawad malik]

i am working on search engine. I want this to rotate search results. A same user can see records sequentially that the last record will not be served at the top and so on. I have use random function but it does't solve the purpose.

[NeoPa]

I would suggest a UDF (User-Defined Function) that returns a Cursor or Table and that maintains the position in the file by saving something to a table every time it's run. Your question is too non-specific to go into much more detail with it.

[ck9663]

RAND() function returns the same value within the same query. If you just need to randomize the returned records, read this.

Good Luck!!!

~~ CK

[Greg Stowell]

Possibly add a column to the table with an integer defined, and assign identity to the field, then remove it (just to number it once) - and each time you run the query (and order by that integer) - you can take the min(integer) and make it max(integer)+1, and just to make sure you don't go over the 65535 (or whatever size # you assign) - perhaps do an update table set integer = integer -1 at the end.

[NeoPa]

If you are talking about some form of search query (as opposed to simpler stored data), and I imagine you must be if you are working on a Search process, then I cannot see any practical way that the data would even be available to process.

To order data in a special way you at least need to be able to define what that is in terms of data, or information, that is available. If I understand correctly what you're after, then I can't see that you even have any such information available. A search run has no access to information pertaining to any previous run of that same search, and unless you propose to save such information per search (in most cases highly impractical), then I see no way that this could be achieved.

Does this make sense to you?

[Greg Stowell]

Example of concept posted above:

drop table dummy
go
 
CREATE TABLE dummy
(
    thetext varchar(10)
)
 
go
 
insert into dummy values ('ABDC')
insert into dummy values ('CDEF')
insert into dummy values ('EFGH')
insert into dummy values ('HIJK')
insert into dummy values ('LMNO')
insert into dummy values ('PQRS')
insert into dummy values ('TUVW')
 
alter table dummy
add theidentity int identity(1,1)
, theid int
go
 
update dummy
set theid = theidentity
go
 
alter table dummy
drop column theidentity
go
 
select * from dummy
thetext    theid
ABDC    1
CDEF    2
EFGH    3
HIJK    4
LMNO    5
PQRS    6
TUVW    7
 
 

Then the stored proc:

create procedure dbo.sp_BottomBump (@tblname varchar(100))
 
as
 
BEGIN
 
DECLARE @Query varchar(8000)
DECLARE @Query2 varchar(8000)
SET @Query = 'SELECT * FROM ' + @tblname + ' order by theid'
 
 
 
SET @query2 = 'update ' + @tblname + '
set theid = (select max(theid) from ' + @tblname + ') + 1
where theid = (select min(theid) from ' + @tblname + ')'
 
exec (@query2)
 
SET @query2 = 'update ' + @tblname + '
set theid = theid - 1'
 
exec (@query2)
 
exec(@query)
 
end
 
go
 

And then the results:

exec sp_BottomBump 'dummy'
thetext    theid
CDEF    1
EFGH    2
HIJK    3
LMNO    4
PQRS    5
TUVW    6
ABDC    7
 
exec sp_BottomBump 'dummy'
thetext    theid
EFGH    1
HIJK    2
LMNO    3
PQRS    4
TUVW    5
ABDC    6
CDEF    7
 
 
thetext    theid
HIJK    1
LMNO    2
PQRS    3
TUVW    4
ABDC    5
CDEF    6
EFGH    7