Skip to Main Content
  • Questions
  • Is it possible to print first 10 records of a table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Abhijit.

Asked: March 17, 2016 - 7:14 am UTC

Last updated: March 21, 2016 - 5:21 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Is it possible to print first 10 records of a table only using group by and having clause.U dont have to use rownum and rowid

and Connor said...

Yes, but I can't see why you would want to do it that way.

SQL> select a.x
  2  from t1 a
  3  where
  4    ( select count(*)
  5      from   t1 b
  6      where  b.x < a.x ) < 10 ;

         X
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10


and if you needed the sequencing

SQL> select a.x, d.seq
  2  from t1 a,
  3       ( select b.x, count(*) seq
  4         from  t1 b, t1 c
  5         where b.x >= c.x
  6         group by b.x
  7       ) d
  8  where a.x = d.x
  9  and d.seq <= 10
 10  order by 1;

         X        SEQ
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10



Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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".
Chris Saxon
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 :-)
Chris Saxon
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.
Connor McDonald
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.

Connor McDonald
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".
Chris Saxon
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