Home » SQL & PL/SQL » SQL & PL/SQL » why can't i select row number 2?
why can't i select row number 2? [message #216621] Mon, 29 January 2007 14:18 Go to next message
dan_thorman
Messages: 14
Registered: January 2007
Junior Member
Hi all. For some reason, this code works when i set the rownum = 1, but doesn't seem to work when rownum is set to 2. I don't get it! Help?

select property_name
from (select rownum, property.property_name 
      from property, deal 
      where deal.accnt_id = 16025 and
            deal.deal_status_id = 9 and
            deal.prop_id = property.prop_id
      order by property_name)
where rownum = 2
Re: why can't i select row number 2? [message #216622 is a reply to message #216621] Mon, 29 January 2007 14:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Because the 1st row selected is ALWAYS 1 which is never equal to 2.
Because you never get the 1st row, you never get to the 2nd row.
Re: why can't i select row number 2? [message #216623 is a reply to message #216621] Mon, 29 January 2007 14:24 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You cannot choose a specific rownum as you are trying to do.

You can say where rownum < 3 to get only the 1st 2 rows in the result set.
Re: why can't i select row number 2? [message #216625 is a reply to message #216621] Mon, 29 January 2007 14:31 Go to previous messageGo to next message
dan_thorman
Messages: 14
Registered: January 2007
Junior Member
so is there a way i can get only the 2nd row? (if i have, say, 32 rows returned and they are ordered alphabetically)
Re: why can't i select row number 2? [message #216626 is a reply to message #216621] Mon, 29 January 2007 14:44 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Yes, use a second in-line view for your query. Use an alias for rownum

So select .... from (**Your query) where rn=2

BTW. What would be the reason that you want only the second row from the output?

Regards,

Arian
Re: why can't i select row number 2? [message #216627 is a reply to message #216621] Mon, 29 January 2007 14:46 Go to previous messageGo to next message
dan_thorman
Messages: 14
Registered: January 2007
Junior Member
Arian,

Thanks for the idea, this seems to work if i want the default order, but the alias appears to negate the "Order By" statement.

Basically, I was planning on doing this to avoid having to learn to use ADO Recordsets in a VBA macro i'm writing, but it looks like i'm just going to have to cave in and do it the right way.
Re: why can't i select row number 2? [message #216630 is a reply to message #216621] Mon, 29 January 2007 14:54 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Dan,

I doubt if the alias does anything with the order by. If the inner query has an order by, and returns rows in the correct order, then the outer query will give the correct result.

But that's a lot of if's. And honestly, I don't understand your comment about the default order. It sounds to me like, the resultset is unordered.

Just to make sure. Is this your final query:
Quote:

select * from (
select property_name, rn
from (select rownum rn, property.property_name property_name
from property, deal
where deal.accnt_id = 16025 and
deal.deal_status_id = 9 and
deal.prop_id = property.prop_id
order by property_name)
where rn=2



Regards,

Arian
Re: why can't i select row number 2? [message #216632 is a reply to message #216621] Mon, 29 January 2007 15:05 Go to previous messageGo to next message
dan_thorman
Messages: 14
Registered: January 2007
Junior Member
yes, that would be my final query. The value it is returning though is not the 2nd value alphabetically in the result set. It is the 2nd value according to a numeric ordering if ordered by the primary key (deal id) which is not selected or mentioned anywhere in the query.

I would've thought that the order by property_name would order the results alphabetically by property_name.

Thanks for all your help so far, I do appreciate it.
Re: why can't i select row number 2? [message #216634 is a reply to message #216621] Mon, 29 January 2007 15:15 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Ok. Something is wrong here. I just tried on a test system. And the basic query returns the right result to me. Based on scott.emp, and order by on whatever.

Can you post a very simple create table, insert (3 or 4 rows) statement, followed by your query giving the wrong result?

Also. Is there anything special about your table? Organization, partitioned, etc. What indexes are in place?

Also what database version are you on?

Regards,

Arian
Re: why can't i select row number 2? [message #216635 is a reply to message #216621] Mon, 29 January 2007 15:23 Go to previous messageGo to next message
dan_thorman
Messages: 14
Registered: January 2007
Junior Member
honestly, i think i give up on this working. My guess is that there is something funky about the tables i'm working on that I simply don't know about. I didn't create this particular schema, nor do i work on it often enough to know all of the indexes or anything like that.

I was going in this direction because i thought it might be a shortcut around writing some VBA that i didn't know, but it turns out not to be.

Thanks for all your help, and if i happen to come up with an sql solution, i'll make certain to come back to this thread to post it.
Re: why can't i select row number 2? [message #216639 is a reply to message #216635] Mon, 29 January 2007 16:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This is probably the problem you are encountering:
SQL> select * from test_rn;

        A1
----------
         1
         9
         3
       100

Be careful on where the ROWNUM keyword is located. Notice what line is listed as ROWNUM = 2 (and this can vary):
SQL> select a1, rownum rn from test_rn a
  2       order by 1;

        A1         RN
---------- ----------
         1          1
         3          3
         9          2
       100          4

SQL> select a1 from (
  2         select a1, rownum rn from test_rn a
  3         order by 1)
  4  where rn = 2;

        A1
----------
         9

You should rewrite as:
SQL> select a1 from (
  2     select a1, rownum rn from (
  3             select a1 from test_rn a
  4              order by 1)
  5     )
  6  where rn = 2;

        A1
----------
         3

or better yet, use analytics:
SQL> select a1 from (
  2       select a1, row_number() over (order by a1) rn from test_rn a
  3       order by 1)
  4  where rn = 2;

        A1
----------
         3


You may want to consider DENSE_RANK too if you are concerned with values that tie for "2nd" place.

[Updated on: Mon, 29 January 2007 16:13]

Report message to a moderator

Re: why can't i select row number 2? [message #216656 is a reply to message #216621] Mon, 29 January 2007 17:34 Go to previous messageGo to next message
dan_thorman
Messages: 14
Registered: January 2007
Junior Member
Well, i was having trouble figuring out how to use ADODB.ResultSet in VBA, so i came back here, and that last post worked like a charm! Thanks!! Here is what (exactly) worked:

select property_name
from (select property_name, rownum rn
from (select property_name from property, deal
where property.prop_id = deal.prop_id and
deal.deal_status_id = 9 and
deal.accnt_id = 16025
order by property_name)
)
where rn = 2
Re: why can't i select row number 2? [message #216702 is a reply to message #216656] Tue, 30 January 2007 01:18 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's an old post explaining rownum a little bit.

MHE
Previous Topic: Delete duplicate rows,shows odd number of rows
Next Topic: STORED PROCEDURES
Goto Forum:
  


Current Time: Sat Dec 10 14:26:52 CST 2016

Total time taken to generate the page: 0.07644 seconds