SQL to select which record to display 1

[amberlynn]

Hello,

I have a table that contains a field "Block", which is the field I want to group on, and "Source" that may contain one of three choices (Cruise, Precomp, Estimate).
There are bunch of numerical fields in this same table.
Each 'block' will have one of the 3 'source' choices, and maybe more. (Eg. one block may have a cruise record, and a precomp record).
What I'm trying to do is create a query that pulls each block, and if there is cruise data, pull that, otherwise pull the precomp data, otherwise pull estimate data. If a block has all 3 sources, I only want one displayed...

Make sense?
Thanks for your help!
Amber

 

[Dagon]

SQL> create table cruises (block varchar2(10), source varchar2(10), numval number);

Table created.

SQL> insert into cruises values ('X', 'Cruise',1);

1 row created.

SQL> insert into cruises values ('X', 'Precomp',10);

1 row created.

SQL> insert into cruises values ('X', 'Estimate',50);

1 row created.

SQL> insert into cruises values ('Y', 'Precomp',30);

1 row created.

SQL> insert into cruises values ('Y', 'Estimate',80);

1 row created.

SQL> insert into cruises values ('Z', 'Estimate',100);

1 row created.

select block, source, numval
from
(select block, source, numval, row_number() over (partition by block order by
case source when 'Cruise' then 1 when 'Precomp' then 2 else 3 end) as rn
from cruises)
where rn=1
/

BLOCK      SOURCE         NUMVAL                                                
---------- ---------- ----------                                                
X          Cruise              1                                                
Y          Precomp            30                                                
Z          Estimate          100

For Oracle-related work, contact me through Linked-In.

 

[amberlynn]

Thanks!
That worked perfectly!

Amber

 

[amberlynn]

Okay - here's another question related to this SQL.

I have pasted below my SQL (based on what you suggested) and it works to pull the records I need from a single table.
How do I incorporate this it of SQL into a bigger script, pulling from multiple tables?
I've been playing around, but getting nowhere.
I'm not sure where I can add other fields, tables, and links between the tables...

select *
from
(select
CUTB_SEQ_NBR,
CRSI_SOURCE_IND,
BLKC_NET_MERCH_M3_PER_HA,
BLKC_NET_MERCH_M3,
BLKC_AREA_HA,
BLKC_NET_VOL_PER_TREE_M3,
row_number()
over (partition by CUTB_SEQ_NBR order by
case CRSI_SOURCE_IND when 'CRUISE' then 1 when 'PRECOMP' then 2 else 3 end) as rn
from BLOCK_CRUISE_STATS)
where rn=1
ORDER BY CUTB_SEQ_NBR

 

[Dagon]

Without knowing exactly what you are trying to do, it's difficult for me to answer. What have you tried so far ? It might give me some idea what you are trying to achieve.

For Oracle-related work, contact me through Linked-In.

 

[amberlynn]

If I have another table called CUT_BLOCK that contains CUTB_SEQ_NBR (as does the table I'm referring to above) - how can I pull records from both tables? For each record in the CUT_BLOCK table, there will be one pulled using the script above (because it only pulls the CRUISE record).

Is this enough details?

 

[Dagon]

There's nothing to stop you changing:

from BLOCK_CRUISE_STATS

to something like:

...
from BLOCK_CRUISE_STATS b,
     CUT_BLOCK cb
where cb.cutb_seq_nbr = b.CUTB_SEQ_NBR
...

The rest of the SQL should work exactly as before, providing the cardinality of the join is as you say 1-to-1.

For Oracle-related work, contact me through Linked-In.

 

[amberlynn]

Thank You!
That worked

 

[amberlynn]

Ok - here's a new new twist to make this more complicated.

I realised that there can be more than one record.
For each 'CUTB_SEQ_NBR' the same rules apply to selecting Cruise, Precomp and Estimate, but there may be more than one of each.
I realized this after needing to pull 2 more fields from this table - "SPECIES" and "PERCENT".
So for a given 'block', it may look like this:

CUTB_SEQ_NBR SOURCE SPECIES PCT
40012243 CRUISE Spruce 23
40012243 CRUISE Pine 75
40012243 CRUISE Aspen 2
40012243 PRECOMP Spruce 20
40012243 PRECOMP Pine 80

I would just want to pull CRUISE records if they exists - PRECOMP if not - etc.
But using the SQL I was using, I pull only the 1st record (in this case Spruce).
I see why this happens - just not sure how to fix.

Amber

 

[Dagon]

Are you saying you want to pull all the CRUISE records rather than just one ? And, if no CRUISE records, then all the PRECOMP records etc ?

That's a very easy fix. You just change ROW_NUMBER to RANK:

select *
from
   (select
    CUTB_SEQ_NBR,
    CRSI_SOURCE_IND,
    BLKC_NET_MERCH_M3_PER_HA,
    BLKC_NET_MERCH_M3,
    BLKC_AREA_HA,
    BLKC_NET_VOL_PER_TREE_M3,
    [b]rank[/b]()
    over (partition by CUTB_SEQ_NBR order by
    case CRSI_SOURCE_IND when 'CRUISE' then 1 when 'PRECOMP' then 2 else 3 end) as rn
   from BLOCK_CRUISE_STATS)
where rn=1
ORDER BY CUTB_SEQ_NBR

For Oracle-related work, contact me through Linked-In.

 

[amberlynn]

Thanks - that did the trick
Amber