Random list - 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

Random list

Author

Topic

sangeeta
Starting Member

16 Posts

Posted - 2006-12-11 : 17:36:56

Hi -

I am new to TSQL and any help on the following would be appreciated.

I have a table that looks like this
security Message
2345 Good
2346 Good
uoas Bad
fsaf More
sdad More
asdd More
ndgs More

I have to pull in a list of distinct messages with a count and any 3 cusips that belong to the message.

I have written, something like this

SELECT MESSAGE_TEXT, COUNT(*) AS Expr1, MIN(security) AS Expr2, MAX(SECURITY) AS Expr3
FROM dbo.DerivedSIACDataMessages
GROUP BY MESSAGE_TEXT
ORDER BY COUNT(*) DESC

However, this lets me pull only 2 securities per message and I want to pull in 4 cusips.

Any help would be apprecioated

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 17:42:57

[code]-- prepare test data
declare @test table (security varchar(4), message varchar(4))

insert @test
select '2345', 'Good' union all
select '2346', 'Good' union all
select 'uoas', 'Bad' union all
select 'fsaf', 'More' union all
select 'sdad', 'More' union all
select 'asdd', 'More' union all
select 'ndgs', 'More'

-- do the work
select q.message,
max(case when q.idx = 1 then security end) 'p1',
max(case when q.idx = 2 then security end) 'p2',
max(case when q.idx = 3 then security end) 'p3',
max(case when q.idx = 4 then security end) 'p4'
from (
select t1.message,
t1.security,
(select count(*) from @test t2 where t2.message = t1.message and t2.security <= t1.security) idx
from @test t1
) q
group by q.message
order by q.message[/code]

Peter Larsson
Helsingborg, Sweden

sangeeta
Starting Member

16 Posts

Posted - 2006-12-12 : 10:01:05

HI Peter.. thanks for your response. The problem that I face with your code is that my list of messages is huge and not fixed.

Please advise.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 10:20:11

Sigh...

You only have to copy and run the code AFTER the line with "Do the work"!
Copy the code to Query Analyzer and replace @test table name with the table name you use in YOUR environment.

select		q.message,
		max(case when q.idx = 1 then security end) 'p1',
		max(case when q.idx = 2 then security end) 'p2',
		max(case when q.idx = 3 then security end) 'p3',
		max(case when q.idx = 4 then security end) 'p4'
from		(
			select	t1.message,
				t1.security,
				(select count(*) from <YourTableNameHere> t2 where t2.message = t1.message and t2.security <= t1.security) idx
			from	<YourTableNameHere> t1
		) q
group by	q.message
order by	q.message

Peter Larsson
Helsingborg, Sweden

sangeeta
Starting Member

16 Posts

Posted - 2006-12-12 : 13:49:06

THansk a lot Peter. Really appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources