sql server new users:
--TOP 5
SELECT TOP 5 ColList
FROM table
ORDER BY DateCol

--NOT TOP 5
SELECT ColList
FROM table t1
WHERE NOT EXISTS (SELECT TOP 5 ColList
FROM table t2
WHERE t1.PriKeyCol=t2.PriKeyCol
ORDER BY DateCol)
ORDER BY DateCol

Note: I normally use a LEFT OUTER JOIN and IS NULL for this, but I
thought I'd try a NOT EXISTS clause. If it blows up, chalk it up to my
unfamiliarity with the concept :)

Stu

I have a table with news, they have dates and right now I

select * from table
order by date

How can I select only the top 5 results

And then how could I select all others BUT the top five ?

Thanks for the help,

Aleks

OK, let's try it the way that I'm accustomed:

SELECT t1.*
FROM dbo.WebsiteNews t1
LEFT JOIN (SELECT TOP 3 *
FROM dbo.WebsiteNews
ORDER BY NewsDate DESC ) t2
ON t1.keyColumn = t2.keyColumn
WHERE t1.Firmidnews = 32 and t1.newstatus = 0
AND t2.keyColumn IS NULL

Question: does the TOP 3 status depend on the WHERE criteria you
specified? In other words, do you just want the TOP 3 records sorted
by date, or do you want the TOP 3 records where Firmidnews=32 and
newstatus=0?

The top 5 did work, but not the bottom five, I get no results where there
are really 17 results.

This is my query, maybe I missed something ?


------------
SELECT * FROM dbo.WebsiteNews t1
WHERE
t1.Firmidnews = 32 and t1.newstatus = 0
AND NOT EXISTS (SELECT TOP 3 *
FROM dbo.WebsiteNews t2
WHERE t1.NewsId=t2.NewsId)
ORDER BY NewsDate DESC

-----------



Thanks Stu,

Aleks




[quoted text, click to view]

Regardless if I change the numer TOP3 or TOP4 or TOP5, I always get 19
results, does this makes sense ?

A


[quoted text, click to view]

With this problem I would use the "NOT IN ()" clause; it's one of the
rare occasions where I would use it. Like this:

create table #tmp
(
NewsID int not null identity(1,1) primary key clustered,
FirmIDNews int not null,
NewsStatus int not null,
NewsDate smalldatetime not null
)

set nocount on
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 1,
'20051001')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 1,
'20051005')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 0,
'20051003')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (17, 1,
'20051006')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (17, 0,
'20051003')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (16, 1,
'20051006')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 0,
'20051006')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 1,
'20051002')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 1,
'20051002')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 1,
'20051004')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 0,
'20051006')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (17, 1,
'20051005')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (17, 0,
'20051007')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (16, 1,
'20051003')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 0,
'20051004')
insert into #tmp (FirmIDNews, NewsStatus, NewsDate) values (32, 1,
'20051001')
set nocount off

-- All articles with the right criteria
select * from #tmp
where FirmIDNews = 32
and NewsStatus = 1
order by NewsDate

-- Top 2
select top 2 * from #tmp
where FirmIDNews = 32
and NewsStatus = 1
order by NewsDate

-- The rest (using NOT IN () )
select * from #tmp
where FirmIDNews = 32
and NewsStatus = 1
and NewsID not in
(
select top 2 NewsID from #tmp
where FirmIDNews = 32
and NewsStatus = 1
order by NewsDate
)
order by NewsDate

drop table #tmp

Hope this helps.

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com



[quoted text, click to view]