Filtering query result on paging function

Question

dear all,

suppose i have one table that has 17 news. i want to showing 4 news in one page(as paging result) with a condition : last news (recent) will show first . in other words first page will show news with id's 17,16,15 and 14 . below is my sql query :

select x . news_id , x . category_id , x . news_title , x . news_content , x . news_date , y . employee_name , x . NIK , x . news_image from news x , employees y where exists ( select count (*) from news where news_id < x . news_id ) and x . NIK = y . NIK and x . news_id <= 4 order by x . news_id desc ;  

where '4' is a results query that i want to show for each page. then after executed, it will show news with id 4,3,2,1 at the first page. whereas i hope it will show news with id 17,16,15,14 as the condition : last news will show first. 

i have tried to modify the queries,but not give query result as far as i hope. any ideas everybody ?

Big thanks for any clues :)

regards,

Aditya Ngindra

Answer 1

Hello,

To strictly answer your question you should run

SELECT TOP 4 <your columns>
FROM
    news AS x
    INNER JOIN employees AS Y ON x.NIK = y.NIK
ORDER BY
    x.news_id DESC
;

Note that this query will return only the first page of news. If you want to do paging refer to this thread.

Answer 2

dear Radu Negru, Mr.

big thanks for your answer. it's very helpful.
i've read already read your other thread if we want to do paging. i have a couple question to you. what should we do if we want to split the result using paging, if we entering second page of news. i've also modified the query like :

sqlNews =

"select top " + iHalaman + " x.news_id,x.category_id,x.news_title,x.news_content,";
sqlNews = sqlNews + "x.news_date,y.employee_name,x.NIK,x.news_image from news as x";
sqlNews = sqlNews + " inner join employees as y on x.NIK = y.NIK ";

 

if (currentRs != 0)
{
      sqlNews = sqlNews + " and x.news_id between " + currentRs + " and " + ((currentRs + iPage) - 1);

}

sqlNews = sqlNews +

" order by x.news_id desc ";

it will show the news with id's like this :

page 1 :  17,16,15,14  | page 2 : 8,7,6,5  | page 3 : 12,11,10,9 | page 4 : 16,15,14,13 | page 5 : 17

but, i want the query result shows the news with id's like :

page 1 : 17,16,15,14  | page 2 : 13,12,11,10 | page 3 : 9,8,7,6 | page 4 : 5,4,3,2 | page 5 : 1

i've tried to modify the queries and change the currentRs values using aritmatics function(formulas) but don't give results as i expect.

thanks for the big clues :)

regards,

Aditya Ngindra

Answer 3

Try this:

DECLARE
    @newPerPage int = 4,
    @pageNo = <your page number>
;

WITH cte AS (
    SELECT
        ROW_NUMBER() OVER(ORDER BY news_id DESC) AS seq,
        <your columns here>
    FROM
        <your joined tables here>
)
SELECT
    <your columns>
FROM
    cte
WHERE
    (seq BETWEEN (@pageNo - 1) * @newsPerPage AND @pageNo * @newsPerPage)
ORDER BY
    news_id DESC
;

Don't try to use news_id as a sequencer: even if now you have all the values in range, this may be not true in the future.