CODEWITHSUNDEEP
sqloracle-database
Elementxo
Elementxo

Reputation: 27

Earliest Date in a year in multiple years

Trying to set up a query which returns the earliest date in a year in multiple years.

Ex:

06-apr-1990
07-may-1991
03-apr-1992
07-jun-1993

the earliest would be 03-apr-1992

any help is appreciated (-:

I'm using Oracle SQL Developer

the dates are in Date format

Upvotes: 1

Views: 213

Answers (4)

Michael Buen
Michael Buen

Reputation: 39393

ANSI SQL, supported on Oracle 12: 

select * 
from tbl 
order by extract(month from the_date), extract(day from the_date)
fetch first 1 row only;

For lower version of Oracle, you can use this:

with a as
(
  select 

      row_number() 
          over(order by 
               extract(month from the_date), extract(day from the_date)) as rn, 

      tbl.the_date

  from tbl 
)
select the_date 
from a
where rn = 1;

Live Code: http://sqlfiddle.com/#!4/e6a75/20

Upvotes: 0

singhswat
singhswat

Reputation: 906

How about something like this...

create table #temp1
(
 ID INT , 
 CreatedDate datetime
)

INSERT #temp1 (Id, CreatedDate )values(1, '06-apr-1990')
INSERT #temp1 (Id, CreatedDate )values(2, '07-may-1991')
INSERT #temp1 (Id, CreatedDate )values(3, '03-apr-1992')
INSERT #temp1 (Id, CreatedDate )values(4, '07-jun-1993')

SELECT A.CREATEDDATE FROM
(
    SELECT TOP 1 CREATEDDATE, CAST(YEAR(CreatedDate) as char(4)) [YEAR], DATENAME(MONTH,CreatedDate)   [MONTH]
    , DATENAME(DAY,CreatedDate) [DAY]
    FROM #temp1
    GROUP BY CreatedDate
    ORDER BY [DAY], [MONTH], [YEAR]
 ) A

Upvotes: 0

Z.a.A.h
Z.a.A.h

Reputation: 1

Try the following query:

SELECT column_name,column_name FROM table_name ORDER BY column_name,column_name ASC|DESC;

source w3school.

Upvotes: 0

Ndech
Ndech

Reputation: 965

If you are using SQL Server, try :

SELECT TOP(1) date FROM table ORDER BY Month(date), Day(date)

For MySQL this should do the trick :

SELECT date FROM table ORDER BY Month(date), Day(date) LIMIT 1;

For Oracle :

SELECT date FROM table ORDER BY Month(date), Day(date) WHERE ROWNUM <= 1;

Upvotes: 1

Related Questions