Home » SQL & PL/SQL » SQL & PL/SQL » small logical query doubt
small logical query doubt [message #246659] Thu, 21 June 2007 10:22 Go to next message
sudheer_vempali
Messages: 3
Registered: June 2007
Location: Hyderabad
Junior Member

Hi,
I need a query to get the 12th record in a table. I used the following query. But didnt get the required result,

SELECT * FROM HELP WHERE ROWNUM = 12;

How can i get this... please help me...
Re: small logical query doubt [message #246693 is a reply to message #246659] Thu, 21 June 2007 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which one is the 12th apple in my basket?

Regards
Michel
Re: small logical query doubt [message #246696 is a reply to message #246659] Thu, 21 June 2007 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Which one is the 12th apple in my basket?
Good Answer!
Way To Go, Michel!
Re: small logical query doubt [message #246703 is a reply to message #246693] Thu, 21 June 2007 11:46 Go to previous messageGo to next message
sudheer_vempali
Messages: 3
Registered: June 2007
Location: Hyderabad
Junior Member

sorry michel,
i didnt get u...
Re: small logical query doubt [message #246710 is a reply to message #246703] Thu, 21 June 2007 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define 12th row.

Regards
Michel
Re: small logical query doubt [message #246722 is a reply to message #246659] Thu, 21 June 2007 12:22 Go to previous messageGo to next message
sudheer_vempali
Messages: 3
Registered: June 2007
Location: Hyderabad
Junior Member

thnq michel,
I got it. T he query lik this...

SELECT rownum, topic, seq, info
FROM help
GROUP BY rownum, topic, seq, info
HAVING rownum = 12.

Thanks,
Sudheer.V
Re: small logical query doubt [message #246723 is a reply to message #246659] Thu, 21 June 2007 12:30 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
And just what do you think that got you ?
Re: small logical query doubt [message #246724 is a reply to message #246659] Thu, 21 June 2007 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Sudheer.V got a collection of 12 random rows which could be different the next time the same SQL is issued after DML changes the contents of the table.
Re: small logical query doubt [message #246766 is a reply to message #246659] Thu, 21 June 2007 16:37 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
search the forum for 'top N'. do some research. if you still don't get it, come back, show these guys what you did, and they'll take you through it.
Re: small logical query doubt [message #246788 is a reply to message #246659] Fri, 22 June 2007 00:27 Go to previous messageGo to next message
esspee.soft
Messages: 4
Registered: June 2007
Location: Hyderabad
Junior Member


select * from help where rowid=(select max(rowid) from help where rownum<13)
Re: small logical query doubt [message #246789 is a reply to message #246788] Fri, 22 June 2007 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
esspee.soft wrote on Fri, 22 June 2007 07:27


select * from help where rowid=(select max(rowid) from help where rownum<13)


Why? What does this give you? Explain!

Regards
Michel
Re: small logical query doubt [message #246791 is a reply to message #246659] Fri, 22 June 2007 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think the best answer is:
SELECT * FROM HELP WHERE ROWNUM = 1;

Regards
Michel
Re: small logical query doubt [message #246792 is a reply to message #246659] Fri, 22 June 2007 00:34 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>select * from help where rowid=(select max(rowid) from help where rownum<13)
Is the result consistantly reproducable against a table under DML changes?
I think NOT!
Yes, rows will be returned, but random rows only.
What good is/are random rows?????????????????????
Re: small logical query doubt [message #246804 is a reply to message #246659] Fri, 22 June 2007 01:12 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Check this out

select * from (select col_name,rownum s from
(select col_name from help order by col_name desc)) where s = 12

[Updated on: Fri, 22 June 2007 01:13]

Report message to a moderator

Re: small logical query doubt [message #246807 is a reply to message #246789] Fri, 22 June 2007 01:21 Go to previous messageGo to next message
esspee.soft
Messages: 4
Registered: June 2007
Location: Hyderabad
Junior Member

It gives the 12 record of the table
Re: small logical query doubt [message #246808 is a reply to message #246659] Fri, 22 June 2007 01:23 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Check this out
>select * from (select col_name,rownum s from
>select col_name from help order by sal desc)) where s = 12
SQL will always return results.
Is the result consistantly reproducable against a table under DML changes?
I think NOT!
Yes, rows will be returned, but random rows only.
What good is/are random rows?????????????????????

Return to original question.

Given any arbitrary table, what is 12th row; under changling DML environment????????????????

It is a nonsensical question!
Re: small logical query doubt [message #246810 is a reply to message #246789] Fri, 22 June 2007 01:26 Go to previous messageGo to next message
esspee.soft
Messages: 4
Registered: June 2007
Location: Hyderabad
Junior Member

rownum and rowid is pseudo columns rownum is not constant to that row and in where condition for rownum you should not = operator only < have to use
Re: small logical query doubt [message #246813 is a reply to message #246808] Fri, 22 June 2007 01:35 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Quote:
It is a nonsensical question


In my opinion,Not really. It depends on the end user's actual requirement and as per the DML change done to the table,Output is obtained.
Re: small logical query doubt [message #246816 is a reply to message #246810] Fri, 22 June 2007 01:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
esspee.soft wrote on Fri, 22 June 2007 08:26

rownum and rowid is pseudo columns rownum is not constant to that row and in where condition for rownum you should not = operator only < have to use


Take this query:
SELECT *
FROM   (SELECT col_name
             , ROWNUM s
        FROM   (SELECT   col_name
                FROM     HELP
                ORDER BY sal DESC))
WHERE  s = 12


1. Oracle executes the inner query => returning the resultset from the table "HELP" sorted.
2. Oracle executes the query selecting the ROWNUM along with the sorted output.
3. Oracle executes the outer query, now having the column "S" from the middle query. You can evaluate this with "=", "<" or ">".


As for your query:
SELECT *
FROM   HELP
WHERE  ROWID = (SELECT MAX (ROWID)
                FROM   HELP
                WHERE  ROWNUM < 13)


This will: limit the selection from "HELP" to 12 rows, using a stopkey. No sort whatsoever. From this random 12 rows, the MAX(ROWID) is taken. This selection does not include ANY sort. The outer query takes this random ROWID and uses it to select a random row from the HELP table.

If I were the OP, I would search the board for one of the zillion threads about this subject. I would also look at the FAQ pages. A lot of people seem to forget that OraFAQ is more than a forum Wink.

MHE
Re: small logical query doubt [message #246819 is a reply to message #246808] Fri, 22 June 2007 01:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
anacedent wrote on Fri, 22 June 2007 08:23
It is a nonsensical question!
No it isn't (IMHO, of course), but it is an incomplete question. In order to answer this question we need:

- sort order
- treatment of duplicates

If I sort a table on column_1 and my output looks like this:

COLUMN_1 COLUMN_2
-------- --------
1        ADAM
2        BART
3        CLARK
3        DAVID
5        ETHAN
6        FELIX
Should I consider the record with "ETHAN" as the fifth? Or is it the record with "FELIX" ( the COLUMN_1 value 6 has in fact the 5th DENSE_RANK...

MHE

[Updated on: Fri, 22 June 2007 01:48]

Report message to a moderator

Re: small logical query doubt [message #246838 is a reply to message #246819] Fri, 22 June 2007 03:13 Go to previous messageGo to next message
esspee.soft
Messages: 4
Registered: June 2007
Location: Hyderabad
Junior Member

ETHAN
Re: small logical query doubt [message #246843 is a reply to message #246838] Fri, 22 June 2007 03:49 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
esspee.soft wrote on Fri, 22 June 2007 10:13
ETHAN

Laughing It was a rhetorical question. Besides, I added too little information myself. The sort is not deterministic. You assumed that the sort was also on COLUMN_2. But is it? So the desired record might as well be the one with "DAVID".

Or even the one with "FELIX" if we were looking for the fifth DISTINCT value (DENSE_RANK comes to mind). My point was: if you don't have sufficient information, it is impossible to give an answer you're 100% sure of. You can't answer it witout making an assumption.

MHE
Previous Topic: autonomous transaction
Next Topic: case statment doubt
Goto Forum:
  


Current Time: Wed Dec 07 02:40:30 CST 2016

Total time taken to generate the page: 0.10841 seconds