SQL MAX FUNCTION PROBLEM - 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

General SQL Server Forums

New to SQL Server Programming

SQL MAX FUNCTION PROBLEM

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-26 : 11:12:46

Hello

The following is my query,and I want the latest Version in my result.

SELECT Max(SEVersion),s.SystemElementID,m.DocumentID,DocumentTitleText,SETitleText as "Title",SESummaryText as "SearchElement"
FROM SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
where Freetext(SESummaryText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID
GROUP BY s.SystemElementID
Having SEVersion=MAX(SEVersion)

But I am getting this error
Column 'SystemElement.SEVersion' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Please HELP

Thank You

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 11:16:42

Don't need the having clause -- it's meaningless as you are returning the MAX anyway.

What did you intent it to do / filter?

Post some sample data / expected results.

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-26 : 11:18:19

basicly you have to do something like this:
SELECT s.SEVersion,s.SystemElementID,m.DocumentID,DocumentTitleText,SETitleText as "Title",SESummaryText as "SearchElement"
FROM SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
WHERE Freetext(SESummaryText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID
AND SEVersion=(select MAX(SEVersion) from SystemElement)

oh and start using normal join syntax:
table1 join table2 on .... etc

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-26 : 11:33:54

okay I did what you said and now I am getting some different error.

this what I did
SELECT Max(SEVersion),s.SystemElementID,m.DocumentID,DocumentTitleText,SETitleText as "Title",SESummaryText as "SearchElement"
FROM SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
where Freetext(SESummaryText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEVersion=(select MAX(SEVersion) from SystemElement)

GROUP BY s.SystemElementID

AND error is
Column 'MasterDocument.DocumentID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So here are the Tables

Table--SystemElement
Columns SystemElementID PK
SETitleText
SESummaryText

Table ---TOCMap
Columns---TOCMapID PK
SystemElementID FK
TableOFContentsID FK

Table ---TableOfContents
Columns--TableOFContentID
DocumentID................Here lies the documentID

table MasterDocument
Columns--DocumentID--pk
DocumentTitleText

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-26 : 11:36:51

who said to leave the group by part and the max(SEVersion) in the select list?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-26 : 11:39:11

so I dont need them?????

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 11:40:45

Well, which results in the other columns that you are selecting to you want to return when you group by (s.SystemElementID) -- how do they relate to the other columns?

It's not obvious here what you are trying to accomplish. Can you post a small dataset showing what you expect to get back at the end?

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

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-26 : 11:47:34

okay 1st of all i am pasting all the tables,and then You will know how they are linked .
Table--SystemElement
Columns SystemElementID PK
SETitleText
SESummaryText

Table ---TOCMap
Columns---TOCMapID PK
SystemElementID FK
TableOFContentsID FK

Table ---TableOfContents
Columns--TableOFContentID
DocumentID................Here lies the documentID

table MasterDocument
Columns--DocumentID--pk
DocumentTitleText

table---TextElement
columns--SystemElementID PK
--ElementText

Table--VideoElement
columns--SystemElementID PK
--VideoMimeType

Table MetaTag
Columns--MetaTagID PK
--SystemElementID FK
-- MetaTagText FK

Table MetaTagDictionary
Columns--MetaTagText PK

I am performing "search" function.This will search TextElement,VideoElement,MetaTagText,BookMarkText

And in the gridview I will display
DocumentTitletext,SystemelementTitle and the search word.

Now I am performing search an a document management system,where user will edit /update the elements.
When the search is performed I have to get result which is of latest version.

Hope I am clear.

Please do reply

Thank You

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-26 : 11:48:19

And here is my stored procedure

USE [charlotte]
GO
/****** Object: StoredProcedure [dbo].[sp_SystemSearch] Script Date: 08/26/2008 10:32:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SystemSearch]

@text as varchar(300)
AS

BEGIN
SET NOCOUNT ON;
declare @srh as nvarchar(50)
SET @srh = 'FORMSOF(INFLECTIONAL,'+@text+')'

SELECT Max(SEVersion),s.SystemElementID,m.DocumentID,DocumentTitleText,SETitleText as "Title",SESummaryText as "SearchElement"
FROM SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
where Freetext(SESummaryText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEVersion=(select MAX(SEVersion) from SystemElement s)

GROUP BY s.SystemElementID

Union all
Select MAX(SEVersion),t.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",ElementText as "SearchElement"
FROM TextElement t,SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
Where t.SystemElementID=s.SystemElementID AND Freetext(ElementText,@srh) AND t.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEVersion=(select MAX(SEVersion) from SystemElement s)
GROUP BY t.SystemElementID

UNION ALL

Select MAX(SEVersion),v.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",VideoMimeType as "SearchElement"
FROM VideoElement v,SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
Where v.SystemElementID=s.SystemElementID AND Freetext(VideoMimeType,@srh) AND v.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEVersion=(select MAX(SEVersion) from SystemElement s)
GROUP BY v.SystemElementID

UNION ALL

Select MAX(SEVersion),tm.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",MetatagText as "SearchElement"
From SystemElement s,MetaTag tm,TOCMap tp,TableOfContents tc,MasterDocument m
Where tm.SystemElementID=s.SystemElementID AND Freetext(MetaTagText,@srh)AND tm.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEVersion=(select MAX(SEVersion) from SystemElement s)
GROUP BY tm.SystemElementID

UNION ALL

Select MAX(SEVersion),b.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",BookmarkText as "SearchElement"
FROM Bookmark b,SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
Where b.SystemElementID=s.SystemElementID AND Freetext(BookmarkText,@srh) AND b.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEVersion=(select MAX(SEVersion) from SystemElement s)
GROUP BY b.SystemElementID

END

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 12:20:14

Hi,

First of all....

Please start using ANSI joins. They are so much easier to debug.

I think this might be the solution (to your first UNION'd query) -- forget about the whole stored proc right now..

I think that


SELECT Max(SEVersion),s.SystemElementID,m.DocumentID,DocumentTitleText,SETitleText as "Title",SESummaryText as "SearchElement"
FROM SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
where Freetext(SESummaryText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEVersion=(select MAX(SEVersion) from SystemElement s)

GROUP BY s.SystemElementID

Should be


SELECT
	m.[documentId]
	, m.[documentTitleText]
	, s.[SETitleText] AS [Title]
	, s.[SESummarytext] AS [SearchElement]
	, s.[SeVersion] AS [version]
	, s.[SystemElementId] AS [SystemElementId]
FROM
	masterDocument m

	JOIN tableOfContents tc ON tc.[documentId] = m.[documentId]
	JOIN TOCMap tp ON tp.[tableOfContentsId] =  tc.[tableOfContentsId]

	JOIN (
		SELECT
			[systemElementId] AS seId
			, MAX([SEVersion]) AS highestV
		FROM
			SystemElement
		GROUP BY
			[systemElementId]
		)
		highestS ON highestS.[seId] = tp.[SystemElementID]

	JOIN SystemElement s ON
		s.[systemElementId] = highestS.[seId]
		AND s.[SEVersion] = highestS.[highestV]
WHERE
	FREETEXT(s.[SESummaryText], @srh)

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 12:24:43

Though you probably want to move the FREETEXT serach into the derived table.

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

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-26 : 12:33:54

Okay Charlie,Your method is more accurate and advance.

But I have a question,

In my previous procedure,
I did Union all only because of one reason.

I have to select ElementText from TextElement
VideoElement from VideoElement
BookmarkText from Bookmark
MetaTagText from MetaTag
SESummaryText from SystemELement

How will I implement this logic in your given stored procedure.

I am very new to this,please help me .

Thank You.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 13:28:14

I think for your data you should use that framework I posted and then hang the video etc tables off of it using left joins but we'll get to that later...

Can you tell me if this code actually works for you...



DECLARE @srh NVARCHAR(50)

SET @srh = '<please put some relevet search code here>'

SELECT
	m.[documentId]
	, m.[documentTitleText]
	, s.[SETitleText] AS [Title]
	, s.[SESummarytext] AS [SearchElement]
	, s.[SeVersion] AS [version]
	, s.[SystemElementId] AS [SystemElementId]
FROM
	masterDocument m

	JOIN tableOfContents tc ON tc.[documentId] = m.[documentId]
	JOIN TOCMap tp ON tp.[tableOfContentsId] =  tc.[tableOfContentsId]

	JOIN (
		SELECT
			[systemElementId] AS seId
			, MAX([SEVersion]) AS highestV
		FROM
			SystemElement
		WHERE
			FREETEXT(s.[SESummaryText], @srh)
		GROUP BY
			[systemElementId]
		)
		highestS ON highestS.[seId] = tp.[SystemElementID]

	JOIN SystemElement s ON
		s.[systemElementId] = highestS.[seId]
		AND s.[SEVersion] = highestS.[highestV]

You can just copy and paste all of this into a query analyser window and substitute some valid search condition.

If this brings back the data that you should get based on that search string (most recent SEVersion that contains the search string) then we can modify the query to include other fields from the other tables using LEFT JOINS (so that if video doesn't exist for a given master document we still get the other info).

However, I'm now off work and at home and just about to go play football, so I'll be away for the rest of the day.

Others here know far more than I so either wait for a reply from me tomorrow or someone else will step in.

Regards,

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources