Help for SELECT DISTINCT

[fabiuz]

Hi,

I am trying to get a ordered list of dates, I have only one table where I store articles and their date. I am trying a query like the following but I get more tha one single month/year ...in other words one month/year is listed for each article :-(

Is there anything I can change to get single month/year ?

SELECT DISTINCT (YEAR(date_article)) as p_year, (MONTH(date_article)) as p_month FROM articles ORDER BY date_article DESC

thank you !

Fabi

[Lars_Berg]

SELECT DISTINCT (YEAR(date_article)) as p_year, (MONTH(date_article)) as p_month FROM articles ORDER BY p_year desc, p_month DESC 

Which dbms are you using? Mysql presumably, since that query of yours should not be allowed.

[fabiuz]

Yes, I am using MySQL, your query perfectly works... but could the same if I use a GROUP BY p_month, p_year ?

SELECT DISTINCT (YEAR(data_art)) as p_anno, (MONTH(data_art)) as p_mese FROM articoli GROUP BY p_mese,p_anno ORDER BY data_art DESC

Is there any difference ? Only a more complex query ?

😕

[Lars_Berg]

The problem is the order by, i.e. that you use expressions not present in the select-list for ordering.

If there are multiple dates within each distinct group, which should be chosen for ordering? Mysql seems to blunder and take all whereas a decent dbms would say that the query can't be processed.

Having both group by and distinct is superfluous. Distinct is actually converted to a query with group by on all columns.