Select top n for each item in group

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

SQL Server Development (2000)

Select top n for each item in group

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-27 : 11:29:26

Brian writes "Hello,

Hopefully this is an easy one. I've got two tables "articles" and "categories". Each record in the articles table is associated with a category from the categories table.

How would I create a query that pulled in the top 5 articles for each category (i.e. if a category had more than 5 articles associated with it, I'd just get the top 5. If a category had less than 5 articles associated with it, I would get as many as were available)?

In case it matters, I'm using SQL-Server 2000 and the returned dataset will be displayed in an ASP page.

Thanks in advance!

Brian"

chadmat
The Chadinator

1974 Posts

Posted - 2002-08-27 : 13:00:40

Post the DDL for your tables. Also, what is the TOP based on? Alphabetical order?

-Chad

bmcelhany
Starting Member

2 Posts

Posted - 2002-08-27 : 16:40:12

Chad, here's the basic table structure:

Categories
CategoryID int
CategoryName nvarchar

Articles
ArticleID int
CategoryID int
Headline nvarchar
ArticleText text
PublishDate datetime

Basically, I just need to get the 5 most recent articles (based on the PublishDate field) for each category.

Thanks!

Brian

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-27 : 18:54:00

I found a solution, but it really sucks. There must be a better way.
My solution gives you the top 2, you need to write the rest to get the other 3.

 
CREATE TABLE #Cat(CatID INT, CatName VARCHAR(50))
CREATE TABLE #Art(ArtID INT, CatID INT, HeadLine VARCHAR(50), PublishDate DATETIME)

INSERT INTO #Cat(CatID, CatName) VALUES(1, 'Hunting')
INSERT INTO #Cat(CatID, CatName) VALUES(2, 'Fishing')
INSERT INTO #Cat(CatID, CatName) VALUES(3, 'Football')

INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(1, 1, 'Hunting 1', '8/1/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(2, 1, 'Hunting 2', '8/2/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(3, 1, 'Hunting 3', '8/3/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(4, 1, 'Hunting 4', '8/4/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(5, 1, 'Hunting 5', '8/5/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(6, 1, 'Hunting 6', '8/6/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(7, 1, 'Hunting 7', '8/7/2002')

INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(8, 2, 'Fishing 1', '8/1/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(9, 2, 'Fishing 2', '8/2/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(10, 2, 'Fishing 3','8/3/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(11, 2, 'Fishing 4','8/4/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(12, 2, 'Fishing 5', '8/5/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(13, 2, 'Fishing 6', '8/6/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(14, 2, 'Fishing 7', '8/7/2002')


INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(15, 3, 'Football 1', '8/1/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(16, 3, 'Football 2', '8/2/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(17, 3, 'Football 3', '8/3/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(18, 3, 'Football 4', '8/4/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(19, 3, 'Football 5', '8/5/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(20, 3, 'Football 6', '8/6/2002')
INSERT INTO #Art(ArtID, CatID, HeadLine, PublishDate) VALUES(21, 3, 'Football 7', '8/7/2002')

SELECT c.CatID, (SELECT top 1 a.ArtID FROM #art a WHERE a.CatID = c.CatID ORDER BY a.PublishDate DESC) as ArtID1,
(SELECT TOP 1 ArtID FROM #art WHERE ArtID IN (SELECT top 2 a.ArtID FROM #art a WHERE a.CatID = c.CatID ORDER BY a.PublishDate DESC)ORDER BY PublishDate ASC) as ArtID2

FROM #Cat c

DROP TABLE #art
DROP TABLE #cat

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-08-27 : 19:24:35

Nice job on DDL and DML Michael..

Take note Brian!!


SELECT CATNAME, HEADLINE
FROM #CAT C
INNER JOIN #ART A ON C.CATID = A.CATID 
WHERE PublishDate 
IN (SELECT TOP 5 PublishDate FROM #ART WHERE CATID = C.CATID)

HTH

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 08/27/2002 19:28:21

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-27 : 19:26:32

/me smacks head
Damn, why didn't I think of that!

Nicely done, but I think he wanted the most recent ones though..



SELECT CATNAME, HEADLINE
FROM #CAT C
INNER JOIN #ART A ON C.CATID = A.CATID 
WHERE PublishDate 
IN (SELECT TOP 5 PublishDate FROM #ART WHERE CATID = C.CATID ORDER BY PublishDate DESC)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Edited by - michaelp on 08/27/2002 19:29:12

bmcelhany
Starting Member

2 Posts

Posted - 2002-08-29 : 10:01:32

That looks like that did the trick. Thanks to everyone who helped out!

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources