🚀 go-pugleaf

RetroBBS NetNews Server

Inspired by RockSolid Light RIP Retro Guy

Article View: bit.listserv.sqlinfo
Article #348

SQL challenge

#348
From: Patrick J. Lademan
Date: Thu, 21 Oct 1999 00:00
72 lines
2368 bytes
I have a SQL challenge for the SQL Gurus out there.
The challenge is listed below.

-- Create Sample Table
create table SAMPLE
(
  CATEGORY  char(8),
  ITEM      char(15)
);

-- Insert Sample Data
insert into SAMPLE values
( 'Wood', 'Oak'    ),
( 'Wood', 'Pine'   ),
( 'Wood', 'Maple'  ),
( 'Wood', 'Cherry' ),
( 'Tool', 'Power Miter' ),
( 'Tool', 'Band Saw'    ),
( 'Tool', 'Scroll Saw'  ),
( 'Tool', 'Table Saw'   ),
( 'Lamp', ' 40W'  ),
( 'Lamp', ' 60W'  ),
( 'Lamp', '100W'  );
commit;

-- Simple Query
select CATEGORY, ITEM 
from SAMPLE 
order by CATEGORY, ITEM;

CATEGORY ITEM           
-------- ---------------
Lamp      40W           
Lamp      60W           
Lamp     100W           
Tool     Band Saw       
Tool     Power Miter    
Tool     Scroll Saw     
Tool     Table Saw      
Wood     Cherry         
Wood     Maple          
Wood     Oak            
Wood     Pine  

The Challenge:
  Can someone create an SQL statement that produces the 
  same output minus the duplicate categories. I really 
  need a DB2 example, however I may be able to convert
  an Oracle example.  A select with subquery is acceptable
  (ie. select X from ( select Y from t ...) s2, t s1 ...).

Desired output:
CATEGORY ITEM           
-------- ---------------
Lamp      40W           
          60W           
         100W           
Tool     Band Saw       
         Power Miter    
         Scroll Saw     
         Table Saw      
Wood     Cherry         
         Maple          
         Oak            
         Pine  

Please Reply-All to this message so that I can retrieve
your solution as soon as it is posted.

Thanks in advance,

Pat
Technical Details
Message-ID: <380E852B.56808222@mad.scientist.com>
Path: arc1-ca.pugleaf.net!unknown!not-for-mail

Show Full Headers
Complete Article Headers
X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 11355a,e395b852c515295f,start
X-Google-Attributes: gid11355a,public
From: "Patrick J. Lademan" <plademan@mad.scientist.com>
Subject: SQL challenge
Date: 1999/10/21
Message-ID: <380E852B.56808222@mad.scientist.com>
X-Deja-AN: 538679332
Content-Transfer-Encoding: 7bit
X-Accept-Language: en,ja
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: abuse@home.net
X-Trace: news.rdc2.mi.home.com 940475321 24.2.64.224 (Wed, 20 Oct 1999 20:08:41 PDT)
Organization: @Home Network
MIME-Version: 1.0
NNTP-Posting-Date: Wed, 20 Oct 1999 20:08:41 PDT
Newsgroups: bit.listserv.sqlinfo