CODEWITHSUNDEEP
postgresqlgrafana
Boky
Boky

Reputation: 12074

How to select DISTINCT records from the database in Grafana

I'm using Grafana and I want to select DISTINCT records from the database. How can I do that?

enter image description here

The query is:

SELECT reference AS "Reference", reference_url AS "Reference URL" FROM daily_run_vehicle WHERE retries >= 5 GROUP BY reference, reference_url ORDER BY 1;

But when I try:

SELECT DISTINCT (reference) AS "Reference", reference_url AS "References" FROM daily_run_vehicle WHERE retries >= 5 GROUP BY reference, reference_url ORDER BY 1;

I get the same result.

The data source that I use in Grafana is Postgres.

UPDATE

The result that I get is as follows:

enter image description here

You see that third and fourth records have the same first column (reference). I do not want that, I want to show only one.

Thus it should be:

enter image description here

Upvotes: 1

Views: 11540

Answers (1)

404
404

Reputation: 8572

You're getting 4 results because SELECT DISTINCT returns rows where the values are distinct across ALL selected fields. So because the combination of reference and reference_url are different in rows 3 and 4, they are still both returned in a DISTINCT.

To do a DISTINCT on only a subset of the fields being returned, use DISTINCT ON to tell it which fields to use for checking uniqueness, followed by a list of fields to return:

SELECT DISTINCT ON (reference) reference, reference_url
FROM daily_run_vehicle
WHERE retries >= 5
ORDER BY reference

I've removed the GROUP BY as DISTINCT ON with an order is the same as a GROUP BY 1 ORDER BY 1; however if you really do want to group by both fields then you need a DISTINCT ON (reference, reference_url), in which case you actually want 4 records returned.

Upvotes: 1

Related Questions