select other columns - 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)

select other columns

Author

Topic

petek
Posting Yak Master

192 Posts

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

hi all,

i have the following code:

SELECT Surname ,
MAX(Match) AS Match,
[Ni Number]
FROM HoldingtableFUZZYMatch
WHERE Match > 10
GROUP BY Surname,
[Ni Number]
ORDER BY 2 desc

but if i add another column i get the following error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'HoldingtableFUZZYMatch.Row ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

is there away around this?

Kind Regards

Pete.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

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

Well yes,

either add it also to the group by clause (if you want to group by it) or constrain it in some sort of aggrigate function (do you want only 1 result per surname with this also : do you want the MAX value per surname / etc)

Just as the error message says really.

Give us the name of the column you want to add and how it relates to the surname and we can help you more.

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 11:25:10

I'm not sure but I think this might be what you are looking for...


SELECT
	derived.[surname]
	, derived.[match]
	, derived.[Ni Number]
	, MAX(hfm.[Row Id])
FROM
	HoldingtableFUZZYMatch hfm

	JOIN (
		SELECT
			Surname AS Surname
			, MAX(Match) AS Match
			, [Ni Number] AS [Ni Number]
		FROM
			HoldingtableFUZZYMatch
		WHERE
			Match > 10
		GROUP BY
			Surname,
			[Ni Number]
	)
	derived ON
		derived.Surname = hfm.surname
		AND derived.[Ni Number] = hfm.[Ni Number]
		AND derived.[Match] = hfm.[match]
GROUP BY
	derived.[surname]
	, derived.[match]
	, derived.[Ni Number]

(note I've grouped by and aggregated [Row Id] here) -- if you wanted to return all results that contained the max(match) then you wouldn't need the GROUP by or the MAX([Row Id])

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

petek
Posting Yak Master

192 Posts

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

thanks for your help charlie,

the group by statement is fine but i have another 15 columns in the table how do i bring them back?

Kind Regards

Pete.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

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

Well I don't really know exactly what you want the data to look like.

I don't know if the columns

Surname AS Surname
, MAX(Match) AS Match
, [Ni Number] AS [Ni Number]

Uniquely identify a row in you table or not. I assume not because there could be 2 exactly the same highest (Match)

Try taking out the outer group by and change to this..


SELECT
	derived.[surname]
	, derived.[match]
	, derived.[Ni Number]
	, hfm.[Row ID]
	, hfm.<your columns here>
	, hfm.<your columns here>
	, ....
	, ....
FROM
	HoldingtableFUZZYMatch hfm

	JOIN (
		SELECT
			Surname AS Surname
			, MAX(Match) AS Match
			, [Ni Number] AS [Ni Number]
		FROM
			HoldingtableFUZZYMatch
		WHERE
			Match > 10
		GROUP BY
			Surname,
			[Ni Number]
	)
	derived ON
		derived.Surname = hfm.surname
		AND derived.[Ni Number] = hfm.[Ni Number]
		AND derived.[Match] = hfm.[match]

If that doesn't suite your needs (you'll get multiple entries per unique key if the inner derived table (derived) doesn't uniquely identify a row), then you'll have to post some sample data and how you need it to look.

Regards,

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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

Been there, than that...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108840

E 12°55'05.25"
N 56°04'39.16"

petek
Posting Yak Master

192 Posts

Posted - 2008-08-26 : 11:55:28

thats fine but how do i return the other columns?

Kind Regards

Pete.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 12:34:29

quote:
Originally posted by petek

thats fine but how do i return the other columns?

Kind Regards

Pete.

What?

Are you serious.

Replace the <your columns here> with, guess what? your columns

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

petek
Posting Yak Master

192 Posts

Posted - 2008-08-27 : 03:53:51

Thanks Charlie,

I didnt see your message until after i replied apologise

PESO the code in the earlier topic worked. but i need to bring back more columns than the grouped items hence this post! but thanks for your keen observation.

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources