Variation on TOP n - 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

Transact-SQL (2000)

Variation on TOP n

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-17 : 11:06:18

Now it's my turn...

remebering all of the nonsense with the 1st10, last 10, MIDDLE 10!

All well and good...

But if I have a nice normalized table

CREATE TABLE myTable99 (EmpId Int, ContractNum char(6))
GO

I've been tasked with populating...

CREATE TABLE myTable00 (EmpId Int, ContractNum1 char(6), ContractNum2 char(6), ContractNum3 char(6))
GO

And have been give the green light that the results can be arbitrary (like the first 3 ordered by contract number). Only if they have a contract number. If they have 1, I need to supply space.

Now I want to do this without a cursor, and my first shot, I found the 1st distinct byte and left joined to it...one per type...but if they all had the same type, then I would only populate 1 column, and I would need to fill it with three..

Here's some sample data...I'll post the code I took a shot at...but it's wrong as a mentioned above. Any ideas?

INSERT INTO myTable99 (EmpId, ContractNum)
SELECT 1, 'ABCD56' UNION ALL
SELECT 2, 'AB12EF' UNION ALL
SELECT 3, 'QWERTY' UNION ALL
SELECT 4, NULL UNION ALL
SELECT 5, 'ABZXEF' UNION ALL
SELECT 5, 'LMNOPQ' UNION ALL
SELECT 5, 'QWESEY' UNION ALL
SELECT 6, 'AOBWEF' UNION ALL
SELECT 6, 'LMTWEP' UNION ALL
SELECT 6, 'SWESTY' UNION ALL
SELECT 6, 'ZOB2EF' UNION ALL
SELECT 6, 'DMT3EP' UNION ALL
SELECT 6, 'JWES4Y' UNION ALL
GO

Don't freak it's DB2.."||" = +..but the rest is pretty similar..


SELECT PERSON_ID, ID_TYPE_CD                                                    
, SUBSTR(CONTRACT_NUMS,1,6)  AS CONTRACT_NUM1                                   
, SUBSTR(CONTRACT_NUMS,7,6)  AS CONTRACT_NUM2                                   
, SUBSTR(CONTRACT_NUMS,13,6) AS CONTRACT_NUM3                                   
, SUBSTR(CONTRACT_NUMS,19,6) AS CONTRACT_NUM4                                   
, SUBSTR(CONTRACT_NUMS,25,6) AS CONTRACT_NUM5                                   
, SUBSTR(CONTRACT_NUMS,31,6) AS CONTRACT_NUM6                                   
, SUBSTR(CONTRACT_NUMS,37,6) AS CONTRACT_NUM7                                   
, SUBSTR(CONTRACT_NUMS,43,6) AS CONTRACT_NUM8                                   
, SUBSTR(CONTRACT_NUMS,49,6) AS CONTRACT_NUM9                                   
, SUBSTR(CONTRACT_NUMS,55,6) AS CONTRACT_NUM10                                  
FROM ( SELECT A.PERSON_ID, A.ID_TYPE_CD                                         
, REPLACE(                                                                      
        VALUE(B.CONTRACT_NUM1 ,' ')                                             
     || VALUE(C.CONTRACT_NUM2 ,' ')                                             
     || VALUE(D.CONTRACT_NUM3 ,' ')                                             
     || VALUE(E.CONTRACT_NUM4 ,' ')                                             
     || VALUE(F.CONTRACT_NUM5 ,' ')                                             
     || VALUE(G.CONTRACT_NUM6 ,' ')                                             
     || VALUE(H.CONTRACT_NUM7 ,' ')                                             
     || VALUE(I.CONTRACT_NUM8 ,' ')                                             
     || VALUE(J.CONTRACT_NUM9 ,' ')                                             
     || VALUE(K.CONTRACT_NUM10,' ')                                             
,' ','')||SPACE(60)                                                             
  AS CONTRACT_NUMS                                                              
  FROM  PRU_REL A                                                               
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MAX(CONTRACT_NUM) AS CONTRACT_NUM1            
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = 'Z'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS B                                          
ON A.PERSON_ID = B.PERSON_ID AND A.ID_TYPE_CD = B.ID_TYPE_CD                    
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MAX(CONTRACT_NUM) AS CONTRACT_NUM2            
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = '9'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS C                                          
ON A.PERSON_ID = C.PERSON_ID AND A.ID_TYPE_CD = C.ID_TYPE_CD                    
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MAX(CONTRACT_NUM) AS CONTRACT_NUM3            
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = '8'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS D                                          
ON A.PERSON_ID = D.PERSON_ID AND A.ID_TYPE_CD = D.ID_TYPE_CD                    
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MAX(CONTRACT_NUM) AS CONTRACT_NUM4            
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = '7'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS E                                          
ON A.PERSON_ID = E.PERSON_ID AND A.ID_TYPE_CD = E.ID_TYPE_CD                    
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MAX(CONTRACT_NUM) AS CONTRACT_NUM5            
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = '6'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS F                                          
ON A.PERSON_ID = F.PERSON_ID AND A.ID_TYPE_CD = F.ID_TYPE_CD                    
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MAX(CONTRACT_NUM) AS CONTRACT_NUM6            
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = '5'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS G                                          
ON A.PERSON_ID = G.PERSON_ID AND A.ID_TYPE_CD = G.ID_TYPE_CD                    
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MAX(CONTRACT_NUM) AS CONTRACT_NUM7            
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = '4'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS H                                          
ON A.PERSON_ID = H.PERSON_ID AND A.ID_TYPE_CD = H.ID_TYPE_CD                    
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MAX(CONTRACT_NUM) AS CONTRACT_NUM8            
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = '3'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS I                                          
ON A.PERSON_ID = I.PERSON_ID AND A.ID_TYPE_CD = I.ID_TYPE_CD                    
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MIN(CONTRACT_NUM) AS CONTRACT_NUM9            
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = 'Z'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS J                                          
ON A.PERSON_ID = J.PERSON_ID AND A.ID_TYPE_CD = J.ID_TYPE_CD                    
LEFT JOIN                                                                       
   (SELECT PERSON_ID, ID_TYPE_CD, MIN(CONTRACT_NUM) AS CONTRACT_NUM10           
      FROM PRU_REL                                                              
     WHERE SUBSTR(CONTRACT_NUM,1,1) = '9'                                       
  GROUP BY PERSON_ID, ID_TYPE_CD) AS K                                          
ON A.PERSON_ID = K.PERSON_ID AND A.ID_TYPE_CD = K.ID_TYPE_CD                    
WHERE A.CONTRACT_NUM <> ' ') AS XXX                                             
;

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-17 : 12:39:53

i assume the pk of the table is empid/contract ?

If so, just calc the "rank" of each contract per employee. then pivot on the rank, using the first 3.

i.e.,

start with this:


SELECT empid, contract, (select count(*) from myTable99 where a.empid = myTable99.empID and a.contract <= myTable99.contract) as Rank
FROM myTable99 a

and then do a simple cross tab:


select empID, 
  MAX(When rank=1 then contract else ' ' end) as Contract1,
  MAX(When rank=2 then contract else ' ' end) as Contract2,
  MAX(When rank=3 then contract else ' ' end) as Contract3
FROM
  (above SQL) a
WHERE
  Rank <= 3
GROUP BY
   EmpID

something like that.

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-17 : 13:32:06

OMG,

now my head hurts...

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-17 : 13:50:32

Jeff,

awesome...

some tweaking though and it works like a champ


  SELECT     empID 
	   , MAX(CASE When rank=1 then contractNum else ' ' end) as Contract1
	   , MAX(CASE When rank=2 then contractNum else ' ' end) as Contract2
	   , MAX(CASE When rank=3 then contractNum else ' ' end) as Contract3
    FROM ( SELECT  empid
		 , contractNum
		 , (SELECT count(*) 
		      FROM myTable99 b
	             WHERE a.empid = b.empID 
	               AND a.contractNum <= b.contractNum) as Rank
             FROM myTable99 a) a      
   WHERE Rank <= 3
GROUP BY EmpID

However it doesn't work in DB2...

I tested, and I don't it supports SELECT (SELECT...syntax yet..

I wonder if I can turn that part in to a join...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-17 : 13:55:29

Yes! you can. in fact, that's how I used to always do these queries before I became fairly proficient with T-SQL.

something like this:


Select 
  A.empID, A.Contract, count(*) as Rank
FROM
  myTable99 A
INNER JOIN
  myTable99 B
ON
  A.empID  = B.EmpID and
  A.Contract >= B.Contract
GROUP BY 
  a.empID, a.Contract

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-17 : 14:03:34

Wow....


    SELECT A.empID, A.ContractNum, count(*) as Rank
      FROM myTable99 A
INNER JOIN myTable99 B
	ON A.empID  = B.EmpID 
       AND A.ContractNum >= B.ContractNum
  GROUP BY a.empID, a.ContractNum

An I image to flip it on it's side it's a self join?

Is there an easier way?


empID       ContractNum Rank        
----------- ----------- ----------- 
2           AB12EF      1
1           ABCD56      1
5           ABZXEF      1
6           AOBWEF      1
6           DMT3EP      2
6           JWES4Y      3
5           LMNOPQ      2
6           LMTWEP      4
3           QWERTY      1
5           QWESEY      3
6           SWESTY      5
6           ZOB2EF      6

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-17 : 14:48:55

I figure I'll just drop it all in to a temp table...and away we go...

THANKS JEFF!

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources