A reader, March 17, 2016 - 9:08 am UTC
--->Select level from dual connect by level<=10;
Level
1
2
3
4
...
Martin Rose, March 17, 2016 - 9:29 am UTC
Surely the correct response to this is,
"Oracle tables have no implicit ordering of data within them. Therefore, saying the 'first 10' is totally meaningless without specifying any ordering criteria".
March 17, 2016 - 5:27 pm UTC
Yep, you need to define your sorting (and how to handle ties!)
Chris
A reader, March 17, 2016 - 6:35 pm UTC
Firstly , I don't see any realistic use for this question.
Secondly , usage of IM words ( like U , thx , .... ) . Tom would have informed him in his own way :-)
March 18, 2016 - 2:02 am UTC
I'd love to say that I'm younger and more hip/trendy than Tom, so I'm "so totally down" with all the IM speak.
But that would be fiction :-)
Martin Rose, March 18, 2016 - 10:57 am UTC
> Tom would have informed him in his own way...
Tom could be a grumpy (and impatient) git at times.
March 18, 2016 - 11:08 am UTC
I'll endeavor to be a patient git :-)
Cheers,
Connor
Group By and Having
Chuck Jolley, March 20, 2016 - 8:01 pm UTC
The original question was how it could be done "only using group by and having"
I can't fathom how it would be done using Group By and Having at all.
March 21, 2016 - 1:50 am UTC
SQL> create table t1 as select * from dba_objects where rownum < 100;
Table created.
SQL>
SQL> select object_id
2 from t1 a
3 group by object_id
4 having (
5 select count(*)
6 from t1 b
7 where b.object_id < a.object_id ) < 10 ;
OBJECT_ID
----------
6
11
2
5
4
8
3
7
10
9
10 rows selected.
But yes...a contrived SQL
Why?
John Stegeman, March 21, 2016 - 11:01 am UTC
My first response to this question would have been, "why?"
If, as my spidey sense is telling me, this question were asked in an interview, my response would have been, "well, it may be possible, but why would one want to do such a thing?"
Well, of course after pointing out that there is no such thing as "the first 10 records in a table" for 2 reasons: tables have rows, not records, and there's no such thing as the "first 10 rows" either - until you tell me the order.
A reader, March 21, 2016 - 1:10 pm UTC
The bizarrest interview question I've had was about ref. cursors. They went into all kinds of depths about them.
Then when I got the job, I asked the problem I assumed they must have had with ref. cursors, and they said "Oh there's no problem with them here. We just opened the manual & picked a page".
March 21, 2016 - 5:21 pm UTC
I do wonder why people pick interview questions that have nothing to do with the job. I'm not sure how being about to spout Oracle trivia (you're not going to use) shows you'll be good at the job!
Chris