Truly DISTINCT Maximums - SQL Server Forums

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)

Truly DISTINCT Maximums

Author

Topic

JohnC28
Starting Member

11 Posts

Posted - 2009-09-28 : 19:13:59

Hi

I'd really appreciate some help with this.

I've got a table of stories - each with a date - linked to a table of categories. A story can appear in more than one category.

What I need is a results set that gives me a list of all the categories, together with the most recent story in that category BUT I don't want a story to appear more than once. So, if story 1 was the most recent story in category 1 AND category 2, then it should only appear next to category 1 and the NEXT MOST recent story against category 2. (Actually, it doesn't matter whether it comes against Cat 1 or Cat 2 so long as it's somewhere).

Can this be done on SQL2000 with a single query?

The SELECT at the bottom is my weak attempt and I don't know where to go next.

Hopefully the code below is helpful.

Regards and thanks

John


CREATE TABLE [#Stories](
	[storyID] int IDENTITY(1,1) NOT NULL,
	[story_date] smalldatetime NOT NULL,
	[story_title] nvarchar(128) NULL,
	[story_body] ntext NULL,
)

CREATE TABLE [#Categories](
	[CatID] int IDENTITY(1,1) NOT NULL,
	[cat_title] nvarchar(128) NULL
)

CREATE TABLE [#StoryCategories](
	[CatID] int NOT NULL,
	[StoryID] int NULL
)

INSERT INTO #Categories (cat_title) VALUES ('Category 1'), ('Category 2'), ('Category 3'), ('Category 4'), ('Category 5')

INSERT INTO #Stories (story_date, story_title, story_body) VALUES
	( DATEADD( mi, 5235, GETDATE()), 'My Story 1', 'Something in story 1'),
	( DATEADD( mi,  600, GETDATE()), 'My Story 2', 'Something in story 2'),
	( DATEADD( mi, 2351, GETDATE()), 'My Story 3', 'Something in story 3'),	
	( DATEADD( mi, 1386, GETDATE()), 'My Story 4', 'Something in story 4'),
	( DATEADD( mi, 7879, GETDATE()), 'My Story 5', 'Something in story 5'),
	( DATEADD( mi, 1245, GETDATE()), 'My Story 6', 'Something in story 6'),	
	( DATEADD( mi, 4435, GETDATE()), 'My Story 7', 'Something in story 7'),
	( DATEADD( mi, 9912, GETDATE()), 'My Story 8', 'Something in story 8'),
	( DATEADD( mi, 2345, GETDATE()), 'My Story 9', 'Something in story 9'),	
	( DATEADD( mi, 1190, GETDATE()), 'My Story 10', 'Something in story 10'),
	( DATEADD( mi, 3335, GETDATE()), 'My Story 11', 'Something in story 11'),
	( DATEADD( mi, 5672, GETDATE()), 'My Story 12', 'Something in story 12')


INSERT INTO #StoryCategories (CatID, StoryID) VALUES
	(1,2), (1,3), (1,4), (1,5), (2,2), (2,5), (2,7), (2,8), (2,9), (3,1), (3,4), (3,5), (3,7), (3,12),
	(4,6), (4,9), (4,10), (4,12), (5,1), (5,3), (5,5), (5,9), (5,11), (5,12)

SELECT MAX(s.story_date) as MaxDate, sc.catid FROM #Stories s INNER JOIN #StoryCategories sc ON sc.StoryID = s.storyID GROUP BY sc.CatID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-29 : 02:33:07

And what is the expected result (output) from the above sample data?

N 56°04'39.26"
E 12°55'05.63"

JohnC28
Starting Member

11 Posts

Posted - 2009-09-29 : 03:54:40

Something like this, but with alternative stories for Cats 3 and 5 as Story 5 has already appeared....


CatID       story_date              story_title
----------  ---------------------   -----------
1           2009-10-04 20:07:00     My Story 5
2           2009-10-06 06:00:00     My Story 8
3           2009-10-04 20:07:00     My Story 5
4           2009-10-03 07:20:00     My Story 12
5           2009-10-04 20:07:00     My Story 5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-29 : 05:05:06

Here's my attempt

-- Create and populate staging table
SELECT		IDENTITY(INT, 1, 1) AS RowID,
		sc.CatID,
		s.StoryID,
		MAX(s.Story_Date) AS theMax
INTO		#Temp
FROM		#StoryCategories AS sc
INNER JOIN	#Stories AS s ON s.StoryID = sc.StoryID
GROUP BY	sc.CatID,
		s.StoryID
ORDER BY	MAX(s.Story_Date) DESC

-- Prepare for iteration
DECLARE	@RowID INT,
	@CatID INT,
	@StoryID INT

-- Start with newest title
SET	@RowID = 1

-- Clean staging data
WHILE @RowID IS NOT NULL
	BEGIN
		SELECT	@CatID = CatID,
			@StoryID = StoryID
		FROM	#Temp
		WHERE	RowID = @RowID
	
		DELETE	
		FROM	#Temp
		WHERE	RowID > @RowID
			AND (CatID = @CatID OR StoryID = @StoryID)

		SELECT	@RowID = MIN(RowID)
		FROM	#Temp
		WHERE	RowID > @RowID
	END

-- Display the result
SELECT		c.Cat_Title,
		s.Story_Date,
		s.Story_Title
FROM		#Temp AS t
INNER JOIN	#Stories AS s ON s.StoryID = t.StoryID
INNER JOIN	#Categories AS c ON c.CatID = t.CatID
ORDER BY	t.CatID

-- Clean up
DROP TABLE	#Temp

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-29 : 05:07:52

The above code can easily be rewritten as a function or a stored procedure, depending on your need.

N 56°04'39.26"
E 12°55'05.63"

JohnC28
Starting Member

11 Posts

Posted - 2009-09-29 : 06:24:48

So no single query option? I've been thinking about it for a week now and haven't come up with one...

Thanks for the code Peso - I'll give it a go and let you know how I get on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources