Group by a field and get the first document

SQL++
1

Hi,
I’m using nodejs sdk and I have documents with this structure:

    {
        ....
         date: "YYYY-MM-DD"
         group_id: "the id of the group"
         ....
    }

How can I group by the documents with the same
group_id and get the one with the lower date?

Thanks in advance

2

SELECT t.group_id, MIN([t.date,t])[1]
FROM default AS t WHERE …
GROUP BY t.group_id;

Explanation is available at Select non-group by fields using sql query

3

Hi,
How could I get the id of the document. I tried with META().Id but it didn’t work.
If I select t, there is not the id in the json.

Thanks in advance

4

SELECT t.group_id, MIN([t.date, META(t).id ])[1]
FROM default AS t WHERE …
GROUP BY t.group_id;

When you alias kesyspace u need to provide alias as argument to META() and id is lowercase

5

Hi,
maybe i explained wrong.
I’d like to retrive the document AND it’s id, so using

SELECT MIN([t.date, t])[1]...

i select only the document, and with

SELECT MIN([t.date, META(t).id)])[1]...

i select only the id.
The only way i found work is:

SELECT MIN([t.date.date, t, t])[1], MIN([t.date.date, t, META(t).id])[2] ...

but i was wondering if it was the right way.

Also, in the case of two document with the same lowest date, i’d like to get if exists the one with the field is_outward true. In case there is no document with is_outward true, the query should fallback to the doc with is_outward false.
So, if i have these 3 docs:

{
    "date": "2018-05-30",
    "is_outward": true,
    "group_id": 1,
    "_id": 1
}
{
    "date": "2018-05-30",
    "is_outward": false,
    "group_id": 1,
    "_id": 2
}
{
    "date": "2018-06-01",
    "is_outward": true,
    "group_id": 1,
    "_id": 3
}

the query should return the document with id = 1.
If the first document would not exist, return the document with id = 2.

Thanks in advance and sorry for not be explicit in the previous questions.

6

Assuming all documents have is_outward , if not replace(t.is_outward != true) with IFMISSINGORNULL(t. is_outward != true, 1)

SELECT m[2] AS id, m[3].* FROM default
LETTING m = MIN([t.date,(t.is_outward != true), META(t).id,t]);

As you need to select whole document it needs to fetch all documents.

OR

The following query uses covering index determine MIN document and fetches only one document

CREATE INDEX ix1 ON default(date,is_outward);
SELECT q.*, META(q).id FROM  FROM default  AS q
                     USE KEYS (SELECT RAW MIN([t.date,  (t.is_outward  != true), META(t).id])[2]  FROM default AS t WHERE WHERE t.date IS NOT NULL );