@test works fine for me, thanks!

@test works with postgresql but not with mssql. Please look at #5959 where the respective was made.
Maybe @pmorrisarctg can help here.

Thanks for testing!
It should now work with MSSQL too.

Suggest performance improvement. No need to waste time on manipulating the date. Just order by created DESC.

@sovainfo: Unfortunately your suggestion does not work with MSSQL.

Tried to fix that, but solving one issue returns another. Guess the only proper solution would be to have an attribute archive or a function that returns YYYYMM.

with this PR the query issued is:

SELECT EXTRACT (MONTH 
  FROM "created") AS created_month,MIN("created") AS created,EXTRACT (YEAR 
  FROM "created") AS created_year
  FROM scaku_content
  WHERE state = 2 
  AND checked_out = 0
  GROUP BY EXTRACT (YEAR 
  FROM "created"), EXTRACT (MONTH 
  FROM "created")
  ORDER BY EXTRACT (YEAR 
  FROM "created") DESC, EXTRACT (MONTH 
  FROM "created") DESC 
  LIMIT 10

could be simplyfied like this

SELECT EXTRACT (MONTH FROM "created") AS created_month,
EXTRACT (YEAR FROM "created") AS created_year
  FROM scaku_content
  WHERE state = 2 
  AND checked_out = 0
  group by created_month,created_year
  ORDER BY created_month DESC, created_year DESC 
  LIMIT 10

@alikon:
No, your suggestion does not work with MSSQL (regarding to the group by and order by part of the clause).

And even more, since the created column is missing in your suggestion it would not work with any database engine at all.

i'm not able to test on MSSQL so i trust you
,but, to be precise here is how that query works fine both on postgresql

and on Mysql

Thanks a lot, that you trust me :-) .

Did you have a look at the second commit I have pushed.
I don't think so, otherwise you would have seen the changes I made to make the query work with MSSQL.

What you are showing me with the pictures just proves that the queries do not throw a fatal error which means that they are syntactical correct.

But they have to work also in the content of the module. The column created is still missing in your query which is used in the module's code for further calculations. So trust me again, your query does not work.

The created column can be not necessary be extracted from the query as you can in an alternative way extract the month name from month number with some php code

The created column can be not necessary be extracted from the query as you can in an alternative way extract the month name from month number with some php code

Sorry, not sure if I understand you correctly. Does it mean, that you want me to rewrite the module to adapt to your query suggestion? Does it than also work with MSSQL? Did you check that? Please provide some complete code, e.g. with a pull request against mine, which can be checked by me an others. Thank you.

@alikon MSSQL doesn't allow the created_year and created_month on the group, it requires the functions as mentioned in the select.

@Erftralle Don't get me wrong , I really appreciate your work, it's not so common when someone take care of all supported DB's , I'm just wondering if we can explore an alternative solution to avoid possible performance issue , not more

i've got some tests

• so it seems that we can not execute at least the year() and month() functions on ORDER BY clause

• for eliminate the created column

foreach ($rows as $row)
{
 $monthName  = date("F", mktime(0, 0, 0, $row->created_month, 10));
 $lists[$i] = new stdClass;
 $lists[$i]->link = JRoute::_('index.php?option=com_content&view=archive&year=' . $row->created_year . '&month=' . $monthName . $itemid);
 $lists[$i]->text = JText::sprintf('MOD_ARTICLES_ARCHIVE_DATE', $monthName,  $row->created_year);
 $i++;
}

@alikon Combine that with:
SELECT DISTINCT(YEAR([created]) * 100 + MONTH([created])) AS [YM]
FROM [j336_content]
ORDER BY [YM] DESC
Extract the month from $row->YM, last 2 positions.

First of all, thanks for your testing and your proposals.

@alikon:

so it seems that we can not execute at least the year() and month() functions on ORDER BY clause

Can't confirm. When using the following query

        $query->select($query->month($db->quoteName('created')) . ' AS created_month')
            ->select($query->year($db->quoteName('created')) . ' AS created_year')
            ->from('#__content')
            ->where('state = 2 AND checked_out = 0')
            ->group($query->year($db->quoteName('created')) . ', ' . $query->month($db->quoteName('created')))
            ->order('created_year DESC, created_month DESC');

I get the following error message with MSSQL

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'created_year'.
SQL=SELECT MONTH([created]) AS created_month,YEAR([created]) AS created_year , ROW_NUMBER() OVER (ORDER BY created_year DESC, created_month DESC) AS RowNumber FROM l72ei_content WHERE state = 2 AND checked_out = 0 GROUP BY YEAR([created]), MONTH([created])

for eliminate the created column

The link text must be a translated month name, not a number.
You are neither considering time offsets nor localization, which JDate does.

@sovainfo:

Unfortunately your query also does not work with MSSQL.
When using the following query

        $query->select('DISTINCT('. $query->year($db->quoteName('created')) . ' * 100 + ' . $query->month($db->quoteName('created')) . ') AS created_ym')
            ->from('#__content')
            ->where('state = 2 AND checked_out = 0')
            ->order('created_ym DESC');

I get the following error message with MSSQL

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'created_ym'.
SQL=SELECT DISTINCT(YEAR([created]) * 100 + MONTH([created])) AS created_ym , ROW_NUMBER() OVER (ORDER BY created_ym DESC) AS RowNumber FROM l72ei_content WHERE state = 2 AND checked_out = 0 

That both of the queries are throwing errors might be caused by the processLimit() function of JDatabaseQuerySqlsrv.

Indeed, it requires those order and group statements. So, if we can't get rid of them it leaves only the removal of the year and month from the select. Checked this against all three in SQL, not in PHP.
It might be worth to remove them for better performance on large sites.

We are hitting the real point
processLimit() function of JDatabaseQuerySqlsrv.

If we can have a requirement for mssql >= 2012
ie SQL:2008 compliant we can rewrite the limit function in a more modern way

Select* from table Offset 1 rows fetch next 10 rows only

But this is outside the scope of this pr

This has conflicts. If this is still a valid issue can you update the PR please :)

This breaks the URLs btw for the articles (i'm guessing removing the id from the select does that)

@wilsonge Can you elaborate?
This is not about article urls, it is about archive urls. The row->id was removed because it wasn't used anywhere.

This has conflicts. If this is still a valid issue can you update the PR please :)

Yes, it is. Rebased the pull request.

This breaks the URLs btw for the articles (i'm guessing removing the id from the select does that)

Agree to @sovainfo. The article id has never been used in the module code. If something is breaking the URLs it has for sure nothing to do with the changes suggested in this pull request.

Verified correct working of this PR on all platforms (MySQL, PGSQL and MSSQL). It works!
The archive view of com_content has some issues. With SEF on it doesn't get the Year right. Also it uses published_up instead of created (as in the module).

At least this PR is fixing multiple rows where only 1 row per month is expected.
Guess that fixing determining the archive (created vs published_up) should be in a separate PR.

@test successful moving to RTC. The other issues can be handeld by a new PR or disable SEF thanks @Erftralle