How do I return the records between two values with the SQL Server Database?

1

Example of my table:

My question is as follows ... In my news page highlight has to appear the 2 latest news and on the secondary news page have to appear the 2 other news following. Example: in the highlight the news that will appear are the news 4 and 3, already in the secondary news to 2 and 1.

I thought of using the LIMIT but this command does not exist in SQL Server so I informed it is just from MySQL, I also thought about BETWEEN but the logic I used did not get any better, so I saw that Top does something similar to LIMIT , so in the highlighted I made the following command

SELECT TOP 2 * FROM CADNOTICIAS ORDER BY CODIGO DESC

In this beautiful highlight is what I want, is returning perfect, my question and how to do in the secondary news bring the news 2 and 1. Everything has to be dynamic in the case of adding a news 5 on the page highlight show news 5 and 4 and in the secondary notice shows 3 and 2.

    
asked by anonymous 13.10.2016 / 23:40

1 answer

1

Just use a subquery to make your filter.

declare @CADNOTICIAS table
(
  CODIGO int,
  THUMB VARCHAR(10),
  TITULO VARCHAR(50),
  TEXTO VARCHAR(100),
  CATEGORIA VARCHAR(100)
)


INSERT INTO @CADNOTICIAS VALUES
(1, 'php.jpg', 'Noticia 1', 'teste', 'Noticia'),
(2, 'php.jpg', 'Noticia 2', 'teste', 'Noticia'),
(3, 'php.jpg', 'Noticia 3', 'teste', 'Noticia'),
(4, 'php.jpg', 'Noticia 4', 'teste', 'Noticia')

SELECT TOP 2 * FROM @CADNOTICIAS 
ORDER BY CODIGO DESC

SELECT TOP 2 * FROM @CADNOTICIAS 
where CODIGO < (SELECT top 1 CODIGO - 1 FROM @CADNOTICIAS ORDER BY CODIGO DESC)
ORDER BY CODIGO DESC
    
26.10.2016 / 16:19