Maybe someone else has asked the same question, but I can't find the exact same. I'm sorry in that case!I have an aggregate that retrieves data from two different db tables, and contains the following:metric_id, registration_date, name, category, etc.
I want to generally sort on the registration date, and on the metric_id. I do that now, but the result is that I have data like this (letters being ID, numbers being registration date): (a, 1), (b, 1), (a, 2), (b, 2), (c, 5), (d, 1)
What I would like, is that even if the registration date is not super recent (like (c,5)) it still shows up on the top, after (b,1). So I want one of each metric_id, whatever the date is. And after that first one it doesn't really matter. How should I go about this? I can't use Group By, I believe, because I need the other data too as output for the table. So this is the result I'm going for: (a, 1), (b, 1), (c, 5), (d, 1), (a, 2), (b, 2)
Hi!
(a, 1), (b, 1), (c, 5), (d, 1), (a, 2), (b, 2)
Why (c,5) in that position ?
The widget list sort can be an expression , regarding the situation you can use different fields or groups of fields , but your sequence is not by metric_id, nor date .
By metric_id and date you should have (a, 1), (a, 2) , (b, 1) , (b, 2) , (c, 5), (d, 1)
By metric_id you could have (a, 1), (a, 2) , (b, 1) , (b, 2) , (c, 5), (d, 1) or
(a, 2), (a, 1) , (b, 1) , (b, 2) , (c, 5), (d, 1) or any other arrangement of a and b .
But by date (c, 5) will always be the last.
Could you explain what i'm missing?
Regards
Graça
Hi @Maria da Graça Peixoto
I think she needs the first distinct ones by metric_id to show them at the top, and then the rest.
As for a possible solution for advanced SQL node (If I'm interpreting well the question) this could be solved by something (generically) in the likes of:
/*Distinct metric_id values*/
SELECT metric_id, MAX(date) AS max_date
FROM your_table_name
GROUP BY metric_id
UNION
/* Ungrouped items*/
SELECT metric_id, date
WHERE metric_id NOT IN (SELECT DISTINCT metric_id FROM your_table_name)
ORDER BY metric_id, date;
Kind Regards,
João
This is indeed what I want! I'm gonna try it out. I'll post back soon.