Get minimum duration by reference number

I have a joined query, and from the two tables i am calculating a field called “time_passed”
I need to sort and find the minimum duration for each reference number
looks like this

ref ref 2 Time passed
1234 6666 0 years 0 mons 58 days 23 hours 48 mins 31.651425 secs
1234 7777 0 years 0 mons 6 days 23 hours 34 mins 0.948793 secs
1555 8888 0 years 0 mons 12 days 23 hours 41 mins 41.971695 secs
1555 9999 0 years 0 mons 0 days 5 hours 59 mins 18.262243 secs
1555 4446 0 years 0 mons 9 days 15 hours 16 mins 56.131648 secs

“ref” comes from one table and “ref 2” comes from another where i have the join (the join comes from another cell)

I need to show for ref 1234 : 6 days
and for ref 1555 : 0 days 5 hours

Hi @Ninappp
I don’t know which database you’re querying, but I would guess you can do something like:
SELECT MIN(time_passed) FROM table GROUP BY ref

The thing is the TIME passed is a calculated field from two joined tables

Here´s that part of the query
MIN (“orders_2”.“client_sent_payment_at”-“public”.“orders1”.“client_sent_payment_at”) AS “TIME_passed”
FROM “public”.“orders”
LEFT JOIN “public”.“orders1” “orders_2” ON “public”.“orders1”.“user_id” = “orders_2”.“user_id”

@Ninappp Then do a CTE or subselect.

but how can i ask for the min time passed for each user id?

@Ninappp Since you’re not saying which database you’re querying or posting the entire query, it’s a bit difficult to help. You can probably find better help on stackoverflow.com or a forum specific to your database.
You can do what you want with a CTE or subselect. You return the calculated time together with the user_id, and then you select MIN(calculated_time) grouped by user_id.

My query is a bit big let me make it smaller and ill send it here

SELECT “public”.“orders1”.“client_sent_payment_at” AS “client_sent_payment_at”,
“public”.“orders1”.“ref” AS “ref”,
“public”.“orders1”.“user_id” AS “user_id”
“orders_2”.“client_sent_payment_at” AS “client_sent_payment_at_2”,
“orders_2”.“ref” AS “ref_2”,
MIN (“orders_2”.“client_sent_payment_at”-“public”.“orders”.“client_sent_payment_at”) AS “TIME_passed”
FROM “public”.“orders1”
LEFT JOIN “public”.“orders1” “orders_2” ON “public”.“orders1”.“user_id” = “orders_2”.“user_id”
WHERE
“public”.“orders1”.“client_sent_payment_at” >= timestamp with time zone ‘2019-01-02 00:00:00.000+01:00’
AND “orders_2”.“client_sent_payment_at” > “public”.“orders1”.“client_sent_payment_at”
AND NOT “orders_2”.“ref” = “public”.“orders”.“ref”
AND GROUP BY (“public”.“orders1”.“client_sent_payment_at”, “public”.“orders1”.“ref”, “public”.“orders1”.“user_id”, “client_sent_payment_at_2”, “ref_2”)
ORDER BY (“public”.“orders1”.“ref”)

@Ninappp Just add the entire thing to a CTE or subselect, and do the grouping there.

Could you give me an example how do i add the min time passed as well?
Thanks!

@Ninappp
Just do all the MIN and grouping outside of the raw data.

WITH mycte AS (
SELECT “public”.“orders1”.“client_sent_payment_at” AS “client_sent_payment_at”,
“public”.“orders1”.“ref” AS “ref”,
“public”.“orders1”.“user_id” AS “user_id”
“orders_2”.“client_sent_payment_at” AS “client_sent_payment_at_2”,
“orders_2”.“ref” AS “ref_2”,
MIN (“orders_2”.“client_sent_payment_at”-“public”.“orders”.“client_sent_payment_at”) AS “TIME_passed”
FROM “public”.“orders1”
LEFT JOIN “public”.“orders1” “orders_2” ON “public”.“orders1”.“user_id” = “orders_2”.“user_id”
WHERE
“public”.“orders1”.“client_sent_payment_at” >= timestamp with time zone ‘2019-01-02 00:00:00.000+01:00’
AND “orders_2”.“client_sent_payment_at” > “public”.“orders1”.“client_sent_payment_at”
AND NOT “orders_2”.“ref” = “public”.“orders”.“ref”
AND GROUP BY (“public”.“orders1”.“client_sent_payment_at”, “public”.“orders1”.“ref”, “public”.“orders1”.“user_id”, “client_sent_payment_at_2”, “ref_2”)
ORDER BY (“public”.“orders1”.“ref”)
)

SELECT ref, MIN(time_passed) FROM mycte GROUP BY ref
1 Like

It doesnt work, Im missing a clause entry for orders1

Fixed it!! Thank you so much!!

It again gives me all the possible outcomes for each reference, so I got the same result in the end as with what i started

@Ninappp This has nothing to do with Metabase, so I would highly recommend that you ask in a forum specific to your database.
In your first example, you want to get ref, but in your “real” query it seems like you want user_id - then group by that.

1 Like