Re: What should be SQL query to retrieve the following info?

From: D.Y. <dyou98_at_aol.com>
Date: 23 Feb 2003 15:00:05 -0800
Message-ID: <f369a0eb.0302231500.7dc1dfb5_at_posting.google.com>


Natasha.Ponomariova_at_comverse.com (Natasha) wrote in message news:<4e41037a.0302230708.6d439e05_at_posting.google.com>...
> Hi,
>
> I have the following table:
> table t (id number not null,
> status number not null,
> priority number not null,
> dn number not null)
> There is only one constraint on the table: id is a primary key.
> Priority could be: 1(low), 2(medium), 3(high).
>
> I should retrieve from the table the following info: id and rowid
> of the records that persist the following conditions:
> 1) if there are several records in the table with the same dn, I
> should retrieve only one with highest priority
> 2) there is no record in the table with the same dn as retrieved and
> status = 2
> 3) status = 1
> 4) order by priority
> 5) rownum<10
>
> What should be SQL query to retrieve the following info?

If your table is small you can use regular SQL. If you have a large table, here is the idea. Please double check the syntax, select * from (
  select id,row_id from
  (select id,row_id,row_number() over

     (partition by dn order by priority desc) row_no    from (select id,rowid row_id,dn,priority from t where dn in

         (select dn from t minus select dn from t where status=2))   ) where row_no=1 order by priority
) where rownum<10;

Not pretty, but it does the job. Basically you use analytic function to satisfy condition(1). It's a lot faster than regular SQL. We use a number of these queries in our data warehouse (with a much larger rownum of course). If criteria (4) and (5) can switch their places, you can move the rownum<10 clause inside the inline view. It'll be even faster.

This should work fine for reporting purposes. If you need OLTP type response time you need to come up with something more creative, and I'll be interested to know how it's done.

> Of course performance is very important.
>
> Thank you in advance,
> Natasha.
Received on Mon Feb 24 2003 - 00:00:05 CET

Original text of this message