Select statement to keep only the best record

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

SQL Server Development (2000)

Select statement to keep only the best record

Author

Topic

RandyK
Starting Member

5 Posts

Posted - 2009-06-22 : 11:24:13

I have a table like this:
PHSID FormNum Status
1 3456 Y
2 2156 <NULL>
2 2157 Y

I need a view that will keep just one record per PHSID. The only values in the Status field are 'Y' and <NULL>. There can be between one and many records with the same PHSID value. This shouldn't be hard but it has me stumped.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 11:43:51

Please define what "best" record means to you.

delete s
from table1 as s
inner join (select phsid, min(formnum) as fm from table1 where status = 'u') AS x
on x.phsid = s.phsid and x.formnum < s.formnum

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-22 : 16:46:18

Maybe this:

SELECT
	T.PHSID,
	T.FormName,
	T.Status
FROM
	MyTable AS T
INNER JOIN
	(
		SELECT	
			PHSID,
			MAX(FormName) AS FormName
		FROM
			MyTable
		WHERE
			Status = 'Y'
		GROUP BY
			PHSID
	) AS D
	ON T.PHSID = D.PHSID
	AND T.FormName = D.FormName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources