asked on

Select Top * From table

Hello experts

i am creating a latest news section on my clients website.
I want to show 5 news details on each section. i have four sections in total thus needing 20 news title.

getting the top 5 is okay.

for the second section, i am using this query to get the details.
SELECT TOP 5 ID, NewsTitle, DocumentName, UploadDate FROM (SELECT TOP 5 ID, NewsTitle, DocumentName, UploadDate FROM (SELECT TOP 10 ID, NewsTitle, DocumentName, UploadDate FROM News ORDER BY UploadDate DESC) AS X ORDER BY UploadDate) AS Y ORDER BY UploadDate DESC

Now lets assume, that there is only 5 news details entered in the datable. this query will retrieve me the same 5 new details as with :
SELECT TOP 5 NewsTitle, DocumentName, UploadDate FROM _News ORDER BY UploadDate DESC

How can i avoid getting the same content on the both section. Initally when the website goes live there will be one or two news details entered. hence i need to cater for the above scenario as well.

Appreciate your help.

SOLUTION

SoLost

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

phh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

phh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thomasian

Are you using SQL Server 2005? If so, you could use the row_number function. I assumed that ID is the PK/unique in the table.

--First 5
SELECT NewsTitle, DocumentName, UploadDate
FROM
  (SELECT NewsTitle, DocumentName, UploadDate, ROW_NUMBER() OVER (ORDER BY UploadDate, ID) rn
     FROM _News 
  ) T
WHERE rn BETWEEN 1 AND 5

--Next 5
SELECT NewsTitle, DocumentName, UploadDate
FROM
  (SELECT NewsTitle, DocumentName, UploadDate, ROW_NUMBER() OVER (ORDER BY UploadDate, ID) rn
     FROM _News 
  ) T
WHERE rn BETWEEN 6 AND 10

Open in new window

Thomasian

If you need the ID or any other fields returned, just include it in both select statements.

i.e.
SELECT ID, News...
FROM
(SELECT ID, News...
...

AsishRaj

ASKER

that worked.