query - 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)

query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-18 : 11:52:42

Mike writes "CPT_LIST
CPT
DOC

for each distinct doc in T1, need to return the top 10 CPT.
I've come up with this just to make it work. The problem is I have only get one DOC value at a time, the other problem is I know this is probably quite messy code, I'm looking for some cleaner way to do this.
-----------------------------------------
select distinct cpt, count(cpt) as cnt, doc
into tmp_drcpt
from CPT_LIST
where doc = 3
group by cpt, doc
order by cnt desc
go
select top 10 cnt, cpt, doc
into tmp_cpt_t10
from tmp_drcpt
go
create view vdocpt as
select *
from CPT_LIST
where doc = 3
go
SELECT vdocpt.doc, vdocpt.cpt
FROM dbo.tmp_cpt_t10 INNER JOIN
dbo.vdocpt ON dbo.tmp_cpt_t10.cpt = dbo.vdocpt.cpt"

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-07-18 : 15:41:59

Hiya!
The only improvement I can suggest is to be able to run the query for more than on doctor at a time using the IN clause. You will need to do dynamic SQL for this (Ugh!)
Declare a parameter @Dr of type varchar according to how long you will need, pass it a comma delimited set of doctors. If your doctor codes are character fields, your parameter must look like this
'''dr1'',''dr2'',''dr3'''. If your code is a number your parameter needs to be like this '3,7,9'. And you have a SELECT statement in a variable which you then concatenate with the dr parameter and EXEC it.
It works, but probably degrades performance.

Sarah Berger MCSD

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-18 : 16:26:30

I'm a bit confused how you expect anyone to be able to help you write a query like this when you don't provide ddl and sample data to explain how the tables are structured and what kind of data this is. Not to mention you don't provide an example of an expected rowset.

As best I can tell, you are looking for a top n records within groupings. I hope this example helps you.


create table #temp (
    JobID    int,
    Step     varchar(10)
)
set nocount on
insert #temp values (1,'a')
insert #temp values (1,'b')
insert #temp values (1,'c')
insert #temp values (1,'d')
insert #temp values (1,'e')
insert #temp values (1,'f')
insert #temp values (1,'g')
insert #temp values (1,'h')
insert #temp values (1,'i')
insert #temp values (2,'a')
insert #temp values (2,'b')
insert #temp values (2,'c')
insert #temp values (2,'d')
insert #temp values (2,'e')
insert #temp values (2,'f')
insert #temp values (2,'g')
insert #temp values (2,'h')
insert #temp values (2,'i')
go

select
    JobId,
    Step
from
    #temp t
where
    exists (
        select 1
        from
            #temp
        where
            jobid = t.jobid and
            step = t.step
        group by
            jobid
        having (select
                    count(*)
                from
                    #temp
                where
                    jobid = t.jobid and
                    step < t.step ) < 3 )
go

This example will return the top 3 'Steps' for each 'JobID' in #temp.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources