Article View: bit.listserv.sqlinfo
Article #348SQL challenge
From: Patrick J. Lademan
Date: Thu, 21 Oct 1999 00:00
Date: Thu, 21 Oct 1999 00:00
72 lines
2368 bytes
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-mailShow 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