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

Question

Example of my table:

My question is this... On my featured news page have to appear the 2 latest news and on the secondary news page have to appear the 2 other news in sequence. Example: in the spotlight the news that will appear are news 4 and 3, already in the news secondary to 2 and 1.

I thought about using LIMIT but this command does not exist in SQL Server so I informed me it is only Mysql, I also thought about BETWEEN but the logic I use did not avenged, then I saw that Top does something similar to LIMIT, so in the highlight I made the following command

SELECT TOP 2 * FROM CADNOTICIAS ORDER BY CODIGO DESC

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

Answer 1

Just use a subquery to make your filter.

The sulução stay like this;

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