Group by nid and Order by date with group ID Drupal

Question

I am using drupal, and I have nodeaccess module. I have many role for a single user. And I have granted many role for a single node.

I need output like the below.

a) Order By Date (first preference)
b) In the date order I need to group by gid

Example

I have wrote a query

SELECT n.nid, na.gid, UNIX_TIMESTAMP(fav.field_date_posted_value), fav.field_date_posted_value
FROM node as n
JOIN nodeaccess AS na ON na.nid = n.nid 
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
ORDER BY fav.field_date_posted_value DESC, n.nid ASC
LIMIT 10

I got result as below

+-------+-----+-------------------------+
| nid   | gid | field_date_posted_value |
+-------+-----+-------------------------+
| 12501 |   1 | 2014-12-07              |
| 12501 |  10 | 2014-12-07              |
| 12502 |   1 | 2014-12-07              |
| 12502 |   3 | 2014-12-07              |
| 12502 |   8 | 2014-12-07              |
| 12502 |  10 | 2014-12-07              |
| 12502 |  11 | 2014-12-07              |
| 12505 |   1 | 2014-05-05              |
| 12505 |  10 | 2014-05-05              |
| 12575 |   1 | 2014-12-24              |
| 12575 |  10 | 2014-12-24              |
| 12576 |   1 | 2013-05-26              |
| 12576 |  10 | 2013-05-26              |
| 12577 |   1 | 2013-05-14              |
| 12577 |  10 | 2013-05-14              |
+-------+-----+-------------------------+

But when I try to group by nid like below

SELECT n.nid, na.gid, UNIX_TIMESTAMP(fav.field_date_posted_value), fav.field_date_posted_value
FROM node as n
JOIN nodeaccess AS na ON na.nid = n.nid 
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
GROUP BY n.nid ORDER BY fav.field_date_posted_value DESC, n.nid ASC
LIMIT 10

The result it

+-------+-----+-------------------------+
| nid   | gid | field_date_posted_value |
+-------+-----+-------------------------+
| 12501 |  01 | 2014-12-24              |
| 12502 |  11 | 2014-12-07              |
| 12505 |  01 | 2014-12-07              |
| 12575 |  01 | 2014-05-26              |
| 12576 |  01 | 2013-05-14              |
| 12577 |  01 | 2013-05-05              |
+-------+-----+-------------------------+

But my expected result is

+-------+-----+-------------------------+
| nid   | gid | field_date_posted_value |
+-------+-----+-------------------------+
| 12501 |  10 | 2014-12-24              |
| 12502 |  11 | 2014-12-07              |
| 12505 |  10 | 2014-12-07              |
| 12575 |  10 | 2014-05-26              |
| 12576 |  10 | 2013-05-14              |
| 12577 |  10 | 2013-05-05              |
+-------+-----+-------------------------+

Is there any way to do this?

I want to reorder my result as group by nid and ordered by date(DESC) and order by gid (DESC)

Answer 1

You could try using aggregate functions on the gid and field_date_posted_value columns, which is what you should be doing anyway in a proper GROUP BY query:

SELECT n.nid,
       MAX(na.gid),
       MAX(fav.field_date_posted_value)
FROM node AS n
JOIN nodeaccess AS na
    ON na.nid = n.nid 
LEFT JOIN field_data_field_date_posted AS fav
    ON fav.entity_id = n.nid
GROUP BY n.nid
ORDER BY fav.field_date_posted_value DESC,
         n.nid ASC
LIMIT 10

Actually, since the nid column seems to perfectly correlate with the field_date_posted_value column, you might be able to group on both of these columns, i.e. use this:

SELECT n.nid, MAX(na.gid), fav.field_date_posted_value
FROM ...
...
GROUP BY n.nid, fav.field_date_posted_value