Date wise no.of rows required in sql query

Question

In my select query, I need date wise data using row num or any thing else.

Ex : data on : 31-Aug-2015 : 100 records 30-Aug-2015 : 100 records.

But I need only 10 records for both the dates in single query

            31-Aug-2015 :10 records
            30-Aug-2015 : 10 records.

Answer 1

There are quite a few details missing, but it sounds like you'll need to use the row_number() analytic function partitioning by date so that you can assign row numbers per date. Then you can easily filter the query where the assigned row number is smaller or equal to 10.

Assuming you have a table defined like this:

create table some_table (
  id number(10) not null primary key,
  some_date date not null
)

Your query could look like this:

select id, some_date
  from (select id,
               some_date,
               row_number() over (partition by trunc(some_date) order by null) as rn
          from some_table
         where some_date >= date '2015-08-30'
           and some_date <  date '2015-09-01')
where rn <= 10

SQLFiddle Demo

A few things to note:

• If your date field doesn't have a variable time component, then you can simply partition by some_date, without the trunc() function call.
• Because you haven't specified which 10 rows you want per date, you'll notice that I use order by null. If you decide that you do want 10 specific rows, you'll have to adjust the order by clause for that.