Need help limiting a result set

Question

Post reply

Need help limiting a result set

Randy Doub

SSCertifiable

Points: 5322
More actions
July 30, 2010 at 12:08 pm

#225628

I have a table of Case Numbers and Doctors involved in the case. There could be any number of docs. My users want me to add two docs to an existing report that is one row per case. My problem is the source table has no sequence number or anything to use as a sequence. I can't seem to figure out how to limit my result to just up to two docs per case. If I could get the proper results in a temp table I can incorporate it into the stored proc that compiles the report.
Table is like this:
create table #tmp(caseid int, doc varchar(10))
insertinto #tmp
select 12345, 'DrSmith'
union all
select 12345, 'DrJones'
union all
select 12345, 'DrWilson'
union all
select 12348, 'DrSmith'
union all
select 12349, 'DrSmith'
union all
select 12349, 'DrWaters'
--expected result:
12345 DrSmith
12345 DrJones
12348 DrSmith
12349 DrSmith
12349 DrWaters
--or
1 12345 DrSmith
2 12345 DrJones
1 12348 DrSmith
1 12349 DrSmith
2 12349 DrWaters
Thanks for all your help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Randy Doub SSCertifiable Points: 5322 More actions · Answer 1

I can do this with a cursor, I just thought there must be a better way.

create table #temp_surg_res(caseid int not null, res varchar(25) not null)

create clustered index ix_caseid on #temp_surg_res(caseid)

insertinto #temp_surg_res

select 12345, 'DrSmith'

union all

select 12345, 'DrJones'

union all

select 12345, 'DrWilson'

union all

select 12348, 'DrSmith'

union all

select 12349, 'DrSmith'

union all

select 12349, 'DrWaters'

declare @i int, @cid int, @res varchar(25), @ocid int

select @i = 0, @ocid = 0

declare res_cur cursor for

select caseid, res

from #temp_surg_res

open res_cur

fetch next from res_cur into @cid, @res

while (@@fetch_status = 0)

begin

if @cid <> @ocid

begin

set @i=1

select @cid, substring(@res,1,25), @i

end

if @cid = @ocid and @i=1 select @cid, substring(@res,1,25), @i

if @cid = @ocid and @i=2 select @cid, substring(@res,1,25), @i

select @i = @i+1

select @ocid = @cid

fetch next from res_cur into @cid, @res

end

close res_cur

deallocate res_cur

VENUGOPAL.RR Ten Centuries Points: 1395 More actions · Answer 2

VENUGOPAL.RR

Ten Centuries

Points: 1395

August 2, 2010 at 9:48 am

#1201097

Can't we use distinct............

LutzM SSC Guru Points: 107049 More actions · Answer 3

Please confirm that you're using SQL2000 and not 2005 or 2008.

If SQL2000: can we add one more column to the temp table as well as a clustered index?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Randy Doub SSCertifiable Points: 5322 More actions · Answer 4

Its a SQL2000 system. Yes, any number of columns or indexes can be added to the temp table. That's what I did in my cursor example so that the caseid numbers were sequential. Also, within the cursor I did a select to display and troubleshoot the results. In production, they would be inserted into another temp table. Then doc 1 and doc 2 would be selected out, joined on the caseid number.

Here's the results of the cursor on the sample data:

12345 DrSmith 1

12345 DrJones 2

12348 DrSmith 1

12349 DrSmith 1

12349 DrWaters 2

This would all get incorporated into a stored procedure I have that compiles this 125+ column report (DTS'ed to a .xls).

The report is for a single month, so its not a very large dataset the cursor has to go through. Usually ~3000 rows or so. A curser in this situation would not be a performance issue. I just figure there's a better way and for the life of me I can't figure it out.

LutzM SSC Guru Points: 107049 More actions · Answer 5

I was hoping you just selected the wrong forum to post in so we could use ROW_NUMBER()...

But since you're at SQL2000 I would use the "quirky update method" as described in Jeff's great article[/url]. (Side note: it's important to read, understand and completely follow the rules described in the article. Otherwise you might end up with unexpected results...

The solution below will number all doc's per caseid. This makes it easy to change the final select to return the TOP(x) instead of TOP(2). Please note that the "ranking" will use the new ID column. If that's not what you're looking for, the clustered index need to be changed, too.

CREATE TABLE #tmp(id INT IDENTITY(1,1), caseid INT, doc VARCHAR(10),pos INT)

INSERT INTO #tmp(caseid,doc)

SELECT 12345, 'DrSmith'

UNION ALL

SELECT 12345, 'DrJones'

UNION ALL

SELECT 12345, 'DrWilson'

UNION ALL

SELECT 12348, 'DrSmith'

UNION ALL

SELECT 12349, 'DrSmith'

UNION ALL

SELECT 12349, 'DrWaters'

-- begin of quirky update method

CREATE UNIQUE CLUSTERED INDEX Idx1 ON #tmp(caseid,id); --IMPORTANT!! see the link for details!!

DECLARE

@valid_counter INT,

@running_caseid INT

UPDATE #tmp

SET

@valid_counter = pos = CASE WHEN @running_caseid = caseid THEN @valid_counter + 1 ELSE 1 END,

@running_caseid = caseid

FROM #tmp WITH (TABLOCKX) --IMPORTANT!! see the link for details!!

OPTION (MAXDOP 1) --IMPORTANT!! see the link for details!!

SELECT *

FROM #tmp

WHERE pos < 3

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

keith.gerritsen SSC Eights! Points: 973 More actions · Answer 6

I'm confused by this part of the narrative:

" I have a table of Case Numbers and Doctors involved in the case. There could be any number of docs. My users want me to add two docs to an existing report that is one row per case.

"

If there's a case with more than two doctors, how do you choose the two doctors displayed? If your report grain is one row per case, I'm confused that the target outcome is one or two rows per case. If there is an attribute of doctors (or an attribute of the doctor-case relationship) that you should select from, the problem should resolve itself. If the choice of doctors is arbitrary and limited to two, how about:

Select caseid, min(res) as Doc1, max(res) as doc2 from #temp_surg_res

group by caseid

having min(res) <> max(res)

union

select caseid, min(res) as doc1, '' as doc2 from #temp_surg_res

group by caseid

having min(res) = max(res)

Randy Doub SSCertifiable Points: 5322 More actions · Answer 7

Keith,

You are correct, it doesn't make a whole lot of sense. This is a vendor system and many secondary case participants stored by their "role". For example, I have the first and second nurse anesthetist in the report, but they are stored by their role "CRNA-1" and "CRNA-2". But the Surgical Residents I'm after now are stored by role "SURG RESIDENT" and there can be any number. I guess the input screens allow an unlimited entry. I've built views to get each of the secondary case participants, but this is the first where more than one per case can be returned. When I told the users we need to cap the number in order to keep the one-row-per-case format, they said a case with more than 2 surgical residents is the exception so go with two. So arbitrary, yes, but not too far out of line.

Anyway, you are also right about selecting the min and max by their name. That never occurred to me, probably because they're varchars.

Also... If I tried to put all that crap above in my original post, nobody would have replied:-).

Thanks all.