Problems in collecting data by select

Question

Problems in collecting data by select

Navigation

#1 by (2 votes)

0

I have a problem making a simple selection of a date. I commented:

I want to collect the month of the creation of a file and that is ordered by the date with this sentence:

select  to_char(fecha,'MONTH') as MES from archivos order by (fecha)  asc;

The problem is that there are several files created the same month and it shows me this:

SEPTIEMBRE
SEPTIEMBRE
OCTUBRE   
OCTUBRE   
NOVIEMBRE 
DICIEMBRE 
DICIEMBRE 
DICIEMBRE 
DICIEMBRE 
ENERO

I just want one month to go out, not all of them. I found something about the select distinc but it gives me an error ... I also think that it can be done with group by .

I've also tried with distinc

 select distinct(to_char(fecha,'MONTH')) as MES from hph_weekly order by fecha asc;

but I get this error:

01791. 00000 - "not a SELECTed expression"

Can someone help me?

sql oracle

asked by PictorGames 26.01.2017 в 10:46

source

1 answer

Conflict with condition in JavaScript How to hide menus on Android

score 2 · Accepted Answer

select  
    distinct(to_char(fecha,'MONTH')) as MES 
from 
    archivos 
order by to_number(to_char(to_date(mes,'mm'),'mm'))  asc;

In the select you get the months as text to_char from the date field with MONTH format (name of the full month), and you use the distinct to avoid duplicates.

In the order by, the month is taken and converted to number from 1 to 12 to order from January to February (the name of the month can not be used because it is alphabetic).