returning unique PageID - 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 2005 Forums

Transact-SQL (2005)

returning unique PageID

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-08-25 : 07:46:39

Dear All

I have a query, that is returning distinct strings from pages. For example
Page String
1 abc
1 bcd
2 ddd
3 eee
3 fff

Now I want to (ignore) the String, and just return the distinct pages. In this case, I just want to return 1,2,3

My query is as follows:-

SELECT DISTINCT MAX(p.pageId) AS PageID, MAX(st.StringName) as StringName
FROM dbo.Pages AS p INNER JOIN
dbo.PageStrings AS pr ON p.fk_projectId = pr.fk_projectId AND p.pageId = pr.fk_pageId INNER JOIN dbo.Strings AS st

Thanks for your help and time

Johann

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-25 : 07:50:17

You'd probably want something like this..


SELECT DISTINCT 
	sub.[pageId]
FROM
	(
	SELECT DISTINCT
		MAX(p.pageId) AS PageID
		, MAX(st.StringName) as StringName
	FROM
		dbo.Pages AS p
		INNER JOIN dbo.PageStrings AS pr ON
			p.fk_projectId = pr.fk_projectId
			AND p.pageId = pr.fk_pageId

		INNER JOIN dbo.Strings AS st 
	)
	sub

Btw -- your posted query has an INNER JOIN with no join conditions. I don't know what you are trying to do there.

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 08:00:07

Why do you want the joins if you just want distinct page info? just use
SELECT DISTINCT pageId FROM dbo.Pages

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-25 : 08:04:45

I assumed he wanted the distinct pages from his previous result set. (which was highest page per string?)

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 08:05:38

quote:
Originally posted by Transact Charlie

I assumed he wanted the distinct pages from his previous result set. (which was highest page per string?)

-------------
Charlie

Sorry i was asking OP

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-08-25 : 08:43:09

Hi Charlie

I just posted part of the sql query, thought it would highlight the problem

Here is the full sql query

DECLARE @projectID int
SET @projectID = 30011

SELECT DISTINCT MAX(p.pageId) AS PageID, MAX(p.pageTitle) AS PageTitle, MAX(p.pageName) AS PageName, MAX(lg.langCode) AS Language
FROM dbo.Pages AS p INNER JOIN
dbo.PageStrings AS pr ON p.fk_projectId = pr.fk_projectId AND p.pageId = pr.fk_pageId INNER JOIN
dbo.Strings AS st INNER JOIN
dbo.Localstrings AS loc ON er.stringId = loc.fk_stringId INNER JOIN
dbo.Languages AS lg ON loc.fk_langID = lg.langId ON pr.fk_stringId = er.stringId
WHERE p.fk_projectid=@projectId
AND lg.langid=9
AND p.parentId = 0

GROUP BY st.stringId

how can I apply the distinct here to get only the distinct pages

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-25 : 08:48:01

Well, have you tried just removing the other columns from your select statement?

I can't see why that wouldn't work.


SELECT DISTINCT
	MAX(p.pageId) AS PageID
FROM
	dbo.Pages AS p
	INNER JOIN dbo.PageStrings AS pr ON p.fk_projectId = pr.fk_projectId AND p.pageId = pr.fk_pageId
	INNER JOIN dbo.Strings AS st
	INNER JOIN dbo.Localstrings AS loc ON er.stringId = loc.fk_stringId
	INNER JOIN dbo.Languages AS lg ON loc.fk_langID = lg.langId ON pr.fk_stringId = er.stringId
WHERE
	p.fk_projectid=@projectId
	AND lg.langid=9
	AND p.parentId = 0
GROUP BY
	st.stringId

I realised that what I posted earlier wasn't neccessary at all -- as long as your joins and where clauses remain the same the returned columns won't matter.

-------------
Charlie

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-08-25 : 08:49:09

Hi Charlie

Thanks

That worked like a treat!

Thanks a lot

Johann

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources