SELECT statement 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

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT statement problem

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2008-08-14 : 10:32:54

Hi All,

I am having problems with the following statement

select DISTINCT ([surname]), Match, [ni number]
from HoldingtableFUZZYMatch where match >20 order by 1 asc , 2 desc

It returns three columns
Surname, Match, Ni Number

it returns:

Beames 60 123456789
Beames 50 123456789
Beames 44 123456789
Beames 40 123456789
Beames 36 123456789
Beames 22 123456789
Aitken 50 YK121182C
Aitken 40 YK121182C
Aitken 36 YK121182C
Aitken 33 YK121182C
Alexander 62 987654321
Alexander 57 987654321
Alexander 53 987654321
Alexander 47 987654321
Alexander 46 987654321
Alexander 42 987654321
Alexander 40 987654321
Alexander 37 987654321
Allan 80 abcd123465
Allan 60 abcd123465
Allan 57 abcd123465
Allan 55 abcd123465

i need a select statement that returns the highest match and distinct name for example

Allan has 4 rows and the highest match value of 80 so i need to return only that row, likewise with alexander the match value is 62 so need to return that row and so on for the other values...

any ideas??

Kind Regards

Pete.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 10:38:43

select * from HoldingtableFUZZYMatch as t
where match=(select max(match) from HoldingtableFUZZYMatch where match >20 and surname=t.surname)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:27:58

Maybe this?

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources