Random results in SQL queries without repeating (Infinite Scroll)

Navigation
2

I have been having this problem for a long time and I can not resolve it.

I have a page with infinite scroll that returns a few lines from the database, but the results most often repeat. I am using the following query whenever the scroll reaches the bottom of the page:

SELECT TOP 10 * FROM table_name ORDER BY NEWID()

To remedy the situation I include a mask, which does not return the results already displayed on the page, for example:

SELECT TOP 10 * FROM table_name WHERE id NOT IN ($resultados_ja_exibidos) ORDER BY NEWID()

But the time comes for the query above to be slow because of the large number of ids that need to be checked before returning the results.

How do I optimize this function?

    
asked by anonymous 01.07.2014 / 16:13

1 answer

1

The select below takes from lines 1 to 20. Then just change the offset values of your pagination to work: 

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER ( ORDER BY uma_coluna_qualquer ) AS linha, *
    FROM      table_name 
) AS tabela_numerada
WHERE linha >= 1 AND linha < 20
ORDER BY linha

Better not to use NEWID() because it does not guarantee the order. Use any field in the table.

    
01.07.2014 / 23:07
add / remove array to an input hidden Error loading web address XML file