How to select distinct date ranges with Oracle SQL

Question

I'm looking to get distinct rows with a start and end date from a table with structure below. I dont want duplicate rows with same start and end month. Please note that start and end date are NUMBER type here, not date.

tbl_app_ranges:
rg_id   start_month   end_month
105     200401        200409    
105     200401        200409    
110     200701        200712    
110     200701        200710

What I want is the below result set

rg_id   start_month   end_month
105     200401        200409    
110     200701        200712    
110     200701        200710

I know this can be done with analytics but not sure how. Is there a way to do this in pure SQL? I need the query to work against Oracle database.

Use DISTINCT/UNIQUE or a GROUP BY rg_id, start_month, end_month. — Lalit Kumar B
– Lalit Kumar B, Commented Nov 13, 2015 at 13:48

Naren · Accepted Answer · 2015-11-13 13:18:11Z

2

select distinct rgid,start_month,end_month from tbl_app_ranges;

edited Nov 13, 2015 at 13:18

user330315

answered Nov 13, 2015 at 13:17

Naren

361 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Hellmar Becker · Accepted Answer · 2015-11-13 13:15:23Z

2

You can use GROUP BY rg_id, start_month, end_month in your query.

answered Nov 13, 2015 at 13:15

Hellmar Becker

3,05215 silver badges19 bronze badges

1 Comment

Lalit Kumar B Over a year ago

DISTINCT would internally do a GROUP BY. +1 for the answer though.

Wolfgang · Accepted Answer · 2015-11-13 13:19:02Z

1

Try this:

SELECT DISTINCT start_month   , end_month 
FROM tbl_app_ranges;

SQL DISTINCT clause example would return each unique start month and end month combination.

answered Nov 13, 2015 at 13:19

Wolfgang

5752 gold badges14 silver badges43 bronze badges

Comments

Roman Marusyk · Accepted Answer · 2015-11-13 14:42:45Z

1

select rg_id, start_month, end_month from tbl_app_ranges
group by end_month, start_month, rg_id

edited Nov 13, 2015 at 14:42

answered Nov 13, 2015 at 13:15

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges

1 Comment

user330315 Over a year ago

No need for the derived table.

Lalit Kumar B · Accepted Answer · 2015-11-13 13:57:51Z

You asked,

I know this can be done with analytics but not sure how.

If you want to do it in Analytics, then you could use ROW_NUMBER() analytic function.

select rg_id, start_month, end_month from (
select rg_id,
       start_month,
       end_month,
       ROW_NUMBER() OVER(PARTITION BY rg_id, 
                                      start_month, 
                                      end_month 
                         ORDER BY rg_id) rn        
from tbl_app_ranges)
WHERE rn = 1;

How the query works:

The ROW_NUMBER assigns an ordered row_number to each group as specified in the PARTITION BY clause. The sorting is taken care by ORDER BY clause. Thsi becomes the inner sub-query, the outer query filters the rows based on the row_number.

Collectives™ on Stack Overflow

How to select distinct date ranges with Oracle SQL

5 Answers 5

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related