select satement / limit results issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select satement / limit results issue

Author

Topic

cgoasduff
Starting Member

21 Posts

Posted - 2009-06-11 : 05:59:44

Hi there

I have a database with a table holding news article.

On a web page I have a statement which calls the 3 top articles (ordered by date)

SELECT TOP 3 * FROM [news_tbl] WHERE [dpt] = news ORDER BY [newsID] Desc

the result is displayed on the top of the page and all is well.

At the bottom of the page which is formatted differently i would like to diplay the TOP 12 articles but of course without the TOP 3 ones since they are already disaplayed at the top.

SELECT TOP 15 * FROM [news_tbl] WHERE [dpt] = news ORDER BY [newsID] Desc (but ignore the 3 first results)

is this possible ?

Thanks

Christophe

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 06:06:38

SELECT TOP 15 * FROM [news_tbl] n
LEFT JOIN
( SELECT TOP 3 * FROM [news_tbl] WHERE [dpt] = news ORDER BY [newsID] Desc) s ON s.newsid = n.newsid
WHERE [dpt] = news AND s.newsid IS NULL ORDER BY [newsID] Desc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-11 : 06:44:56

quote:
On a web page I have a statement which calls the 3 top articles (ordered by date)

SELECT TOP 3 * FROM [news_tbl] WHERE [dpt] = news ORDER BY [newsID] Desc

newsID is a date column ? If not, why are you ordering by that instead of the date column ?

basically you wanted the TOP 15 and display 3 at the top and 12 at the bottom. Why not just do it in a single query to select TOP 15 and then in your front end display 3 at the top and rest at the bottom ?

KH
[spoiler]Time is always against us[/spoiler]

cgoasduff
Starting Member

21 Posts

Posted - 2009-06-11 : 07:16:30

Hi BKLR

works great, thanks a lot.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 07:27:14