Order by date listing those with same name together

SQL++
1

Hello, I am looking for a n1ql query where I select data sorting it by date but listing those with the same name together.
IE:
{
{ date:0, name: “John”}
{date:1, name: “Peter”}
{date:2, name: “John”}
{date:3, name:“Peter”}
}

What I want is: The one with the newest date go first followed by those with the same name ordered by date, then the next newer date followed by those with the same name ordered by date and so on.
[
{date:3, name:“Peter”}
{date:1, name: “Peter”}
{date:2, name: “John”}
{ date:0, name: “John”}
]
They are ORDER BY date DESC but those which have the same name are selected in togheter.
Thanks

2

There is no way you can get results your desired way. But you can try one of the following.

You can use ORDER BY date DESC, name
ORDER BY name, date DESC

3

Thanks for your reply,
I’ve already tried that with no success that is why I asked. I also tried nested SELECT but I haven’t find a solution yet, maybe because it is not possible.

4

@Jesus_Perez_Felipe, If results set is not too big try this

SELECT RAW uv
FROM (SELECT ARRAY_AGG(d) AS av
      FROM default AS d
      WHERE d.name > ""
      GROUP BY d.name
      ORDER BY MAX(d.date) DESC) AS t
UNNEST (SELECT RAW t1 FROM t.av AS t1 ORDER BY t1.date DESC) AS uv;
5

Thanks, it seems that it works. The result set isn’t big because I am using it for pagination so I limit the request.
Thanks