Home » SQL & PL/SQL » SQL & PL/SQL » extract second (or earlier) value from dense_rank
extract second (or earlier) value from dense_rank [message #226094] Thu, 22 March 2007 09:11 Go to next message
masijade
Messages: 7
Registered: November 2006
Junior Member
Okay, consider the following table

table bogus (
  id varchar2
  rating number
  type enum(a, b, c) /* actually varchar2 but with set values */
)


with the following data
a 10 b
a 12 b
a 14 b
a 16 b
b 12 b
b 22 b
b 31 b
b 42 b
c 11 b


I have the query
select id, max(rating) as rating
from
  (select id, rating, dense_rank() (partition by id, type order by rating) as rank
   from bogus
   where type = b)
where rank <= 2


this works and returns

a 12
b 22
c 11


My question is, is there a way to do this query without using a subquery and still return only those rows? Maybe some function other than max and dense_rank, although I can't think of any.

I only ask, because this query will become part of an already very complex (and deep) query and I would relly prefer to add just one level, instead of 2, if possible.

Thanks, in advance.

[Updated on: Thu, 22 March 2007 09:12]

Report message to a moderator

Re: extract second (or earlier) value from dense_rank [message #226098 is a reply to message #226094] Thu, 22 March 2007 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can't avoid the subquery but you can enhance your query:

select id, rating
from
  (select id, rating, 
   dense_rank() over (partition by id order by rating) as rk,
   count(distinct rating) over (partition by id) as cnt
   from bogus
   where type = b)
where rk = when cnt < 2 then 1 else 2 end
/


Regards
Michel
Re: extract second (or earlier) value from dense_rank [message #226105 is a reply to message #226094] Thu, 22 March 2007 09:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it, but it's not pretty, and I'll not vouch for it's performance when your tables get big (actually, I will. I'll vouvh that it performs fairly poorly):
create table bogus (id varchar2(10)
                  ,rating number
                  ,type varchar2(10));

insert into bogus values ('a' ,10, 'b');
insert into bogus values ('a' ,12, 'b');
insert into bogus values ('a' ,14, 'b');
insert into bogus values ('a' ,16, 'b');
insert into bogus values ('b' ,12, 'b');
insert into bogus values ('b' ,22, 'b');
insert into bogus values ('b' ,31, 'b');
insert into bogus values ('b' ,42, 'b');
insert into bogus values ('c' ,11, 'b');

select a.id
      ,min(a.rating) rating
from   bogus a
      ,bogus b
where a.id   = b.id
and   a.type = b.type
and   a.type = 'b'
and   a.rating > b.rating
group by a.id
union all
select a.id
      ,min(a.rating) rating
from   bogus a
where  a.type = 'b'
having count(a.id) = 1
group by a.id;

"ID"	"RATING"
"a"	"12"
"b"	"22"
"c"	"11"
[Solved] Re: extract second (or earlier) value from dense_rank [message #226305 is a reply to message #226098] Fri, 23 March 2007 04:06 Go to previous messageGo to next message
masijade
Messages: 7
Registered: November 2006
Junior Member
Thanks a lot guys. I was pretty sure the answer would fall into those two categories. Wink

And "Michel Cadot" I believe your right. Putting a second function into the subquery and avoiding the function in the "outer" query is probably more efficient. I will perform a few tests with that (and the original) to make sure though. Every little bit of performance here is important, as I am dealing with anywhere between 5 and 25 million rows (dependent on the type).

And "JRowbottom" thanks, but I think, given the choice, I would rather add two levels than that. Wink (I am not trying to be offensive, but rather congenial, so please don't take offense.)

Once again, thanks for taking the time guys.
Re: [Solved] Re: extract second (or earlier) value from dense_rank [message #226343 is a reply to message #226305] Fri, 23 March 2007 05:33 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
NO offense taken. I'd do it with a subquery too, given the choice.
Previous Topic: ORA-01722: invalid number after setting user preferences
Next Topic: how to insert timestamp
Goto Forum:
  


Current Time: Fri Dec 02 12:40:08 CST 2016

Total time taken to generate the page: 0.10244 seconds