Selecting Top # Records From Table

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 2000 Forums

Transact-SQL (2000)

Selecting Top # Records From Table

Author

Topic

marko
Starting Member

1 Post

Posted - 2005-01-24 : 17:41:20

Ok, selecting the top 5 records is easy ofcourse, but i have an extra feature i'd like to add - and haven't been able to figure out myself yet.

Situation
For a website i would like to select the top 5 most recent newsitems in a stored procedure in order to show them in a column with 'latest news'.

So far, no problem. The corporate website handles several departments though, and each department has its own newsitems. If i want to show the newsitems of a particular department, i'll just add a 'where department = x' criterium.

But now for the tricky part: if a department has less than 5 newsitems, i would like to select newsitems from other departments so i'll always have 5 records.

I've spent some time thinking how to achieve this :)
The simplified stored procedure looks like this:

SELECT TOP 5 news_ID, news_Title, news_Text, news_Date
FROM tblNews
WHERE news_departmentID = @variable

The SP above works fine, but if there are less than 3 records available, it won't show more ofc. However, I would like to show an extra 2 records from random other departments in order to have 5 :-/

How to do?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-24 : 18:01:06

something like:


declare @Count int
set rowcount 5

SELECT news_ID, news_Title, news_Text, news_Date
FROM tblNews
WHERE news_departmentID = @variable

set @Count = @@Rowcount
if @Count <= 3
begin
set rowcount 5 - @Count
     select from other tables
end 

set rowcount 0

this will return you 2 sets which you can join on the client.

or


declare @Count int
SELECT @Count = count(*) FROM tblNews
WHERE news_departmentID = @variable

if @Count<=3 
begin
exec('
select * from (
select top ' + convert(varchar(1), @Count) + ' 
news_ID, news_Title, news_Text, news_Date
FROM tblNews
WHERE news_departmentID = ' + @variable + '
union all
SELECT top ' + convert(varchar(1), 5-@Count) +  ' 
news_ID, news_Title, news_Text, news_Date
FROM tblNews
WHERE news_departmentID = ' + @variable + '
) t'
end

or use sp_executesql instead of th exec to keep it parametrised. look up sp_executesql in BOL

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources