Re: Question for an SQL expert

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/05/25
Message-ID: <8gjhto$u09$1@s2.feed.news.oleane.net>#1/1

Could be something like that:

v734> create table tab (PRDGRP_ID number, zone_id number, srp number); Table created.

v734> insert into tab values (1,1,1.99);
v734> insert into tab values (1,2,1.89);
v734> insert into tab values (1,3,1.79);
v734> insert into tab values (2,1,2.99);
v734> insert into tab values (3,1,6.99);
v734> insert into tab values (3,3,7.05);
v734> commit;
v734> select tab.PRDGRP_ID, tab.zone_id, tab.srp
  2  from ( select PRDGRP_ID, max(zone_id) zone_id
  3         from tab
  4         where zone_id in (1 /* default zone */,
  5                           2 /* requested zone */)
  6         group by PRDGRP_ID) a,
  7       tab

  8 where a.PRDGRP_ID = tab.PRDGRP_ID
  9 and a.zone_id = tab.zone_id
 10 /

 PRDGRP_ID ZONE_ID SRP
---------- ---------- ----------

         1          2       1.89
         2          1       2.99
         3          1       6.99

3 rows selected.

--
Have a nice day
Michel


<miltjef_at_co-opsonline.com> a écrit dans le message : 8gjf1s$b5p$1_at_nnrp1.deja.com...


> Could some SQL expert answer the following question?

>

> I have a table that contains the following columns:

>

> PRDGRP_ID

> ZONE_ID

> SRP

>

> Sample data:

> 1,1,1.99

> 1,2,1.89

> 1,3,1.79

> 2,1,2.99

> 3,1,6.99

> 3,3,7.05

>

> I am trying to write a SQL query that will return the SRP for a givin

> zone_id.  Example if I request zone_id "1", I want the following to be

> returned:

> 1,1,1.99

> 2,1,2.99

> 3,1,6.99

> That was easy, now the hard part.  If I request a zone_id and it

> doesn't exists for a prdgrp_id, I want the default zone_id record to be

> returned (in this example the default zone_id is 1).  So if I request

> zone_id "2", I want the following:

> 1,2,1.89

> 2,1,2.99 (since there isn't a zone_id of "2", return the default)

> 3,1,6.99 (since there isn't a zone_id of "2", return the default)

>

> Could someone help me with this query?

>

> Thanks,

> Jeff Milton

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.

Received on Thu May 25 2000 - 00:00:00 CDT