Retrieve a Representative of a Group

Hey, Ted:

I understand how to use the SQL column functions to select one record out of a group. However, as far as I know, the column functions cannot retrieve the entire record. Or do you know something I don't know?

--Brian

This is a good question, Brian. I had to use SQL for this very purpose recently, so the answer to your question is fresh on my mind.

First, you are correct that the column functions cannot retrieve all the columns (fields) in a row (record). To get the remainder of the columns requires a join, but it's an easy join.

To illustrate, consider a table of machining operations.

Item     Operation  Work 
Number   Sequence   Center
======   =========  ======
A-101    10         W1      
A-101    20         W2      
A-101    30         D5      
A-900    10         N48     
B-400     5         A90     
B-400    10         D91     
B-500    10         A90     
B-500    20         D8      
B-500    30         A90     
B-500    40         A10     
B-500    50         C80     

Let's suppose that I need to retrieve the highest-numbered operation for each item. That's easy enough, thanks to the MAX column function.

select itnbr, max(opseq)
  from operations       
 group by itnbr         
 order by itnbr

Item     Operation
Number   Sequence
======   =========
A-101        30 
A-900        10 
B-400        10 
B-500        50

Now suppose that I also want to see the work center for these operations. Here's the method I prefer.

with MaxSequence as                   
   (select itnbr, max(opseq) as opseq 
      from operations                 
     group by itnbr                   
   ) 
select Ops.*                          
  from Operations as ops              
  join MaxSequence as max             
    on ops.itnbr = max.itnbr          
   and ops.opseq = max.opseq          

Item     Operation  Work 
Number   Sequence   Center
======   =========  ======
A-101    30         D5 
A-900    10         N48
B-400    10         D91
B-500    50         C80

MaxSequence is a common table expression, a temporary table that contains the highest-numbered operation for each item. I join that temporary table to the base table on the item number and sequence number in order to get the work center.

Here's another method that works just as well. In this case, the summary query has been embedded in the join clause. This is called a subselect.

select Ops.*                             
  from Operations as ops                 
  join (select itnbr, max(opseq) as opseq
          from operations                
         group by itnbr) as max          
    on ops.itnbr = max.itnbr             
   and ops.opseq = max.opseq             

For more information about common table expressions and subselects, see Time for a Common Table Expression, Part 2 .

This is a good technique to master; retrieving the entire row that represents a group is not an uncommon requirement.

--Ted

Sponsored By WORKSRIGHT SOFTWARE

600 Billion That's how much a recent independent study estimated U.S. businesses spend on dirty data. How much of that 600 billion is spent by your company? Cleanse your dirty ZIP Codes and mailing addresses with our software and save big bucks. WorksRight Software, Inc. Phone: 601-856-8337 E-mail: software@worksright.com Web site: www.worksright.com