Strip rows from select - 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

SQL Server Development (2000)

Strip rows from select

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2003-12-04 : 16:08:02

CREATE TABLE #t (fn int, val varchar(100) , res varchar(7000),
CONSTRAINT thecons PRIMARY KEY (fn,val)
)

INSERT INTO #t(fn,val) VALUES( 1, 'abc')
INSERT INTO #t(fn,val) VALUES( 2, 'nmo')
INSERT INTO #t(fn,val) VALUES( 1, 'def')
INSERT INTO #t(fn,val) VALUES( 1, 'cpq')
INSERT INTO #t(fn,val) VALUES( 2, 'yzz')

SET NOCOUNT ON
DECLARE
@list varchar(8000),
@x int

SELECT
@list = '',
@x = -1

UPDATE #t SET
@list = res = CASE WHEN @x <> fn THEN val
ELSE @list + ', ' + val
END,
@x = fn
SELECT * FROM #t
DROP TABLE #t

This is a cool post thanks page47
[url]http://www.sqlteam.com/item.asp?ItemID=11021[/url]

result set

1 abc abc
1 cpq abc, cpq
1 def abc, cpq, def
2 nmo nmo
2 yzz nmo, yzz

I only want the rows with the biggest res field ie.
rows 3,5 returned.

How can I query that?

Thanks

slow down to move faster...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-04 : 17:20:55

[code]
select fn, max(val) val, max(res) res
from #t a
group by fn
[/code]

skillile
Posting Yak Master

208 Posts

Posted - 2003-12-04 : 18:51:49

Thank you very much :)

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources