Max function independent from group by

Get Help
1

I want to get the max() value of all the dates in my table, but the group by makes it so it searches for a max in the row, and the result is the same date. Changing the group by is not an option, I need to have the max() total value for a comparison. The following code is an example of the much larger query I'm working with. Guess what I'm trying is to get a type of variable independent from the table.
image

2

Hi @Vallistruqui
Post "Diagnostic Info" from Admin > Troubleshooting, and which database you are querying.
You should post your actual query.
If you want to group by day, then you need to do that - you are grouping by the column, which is perhaps a datetime or timestamp, so it will return every value of that.
GROUP BY DATE(ready_to_pick)

3

This is my actual query, the filter I'm concerned with is the

having (extract(day from orders.shipped_at) = extract(day from max(orders.ready_to_pick_at)) or orders.shipped_at is null)

That doesn't get the max absolute date, but the date of the same row. So this filter is pointless.

select
orders.service_code,
orders.id,
orders.status,
accounts.company_name,
orders.ready_to_pick_at,
date_part('day', orders.ready_to_pick_at - orders.created_at) as Dias_Retrasados_Ready_Pick,
orders.ready_to_ship_at,

orders.shipped_at,



CASE 
    when orders.service_code in ('shipnow_same_day') then 12
    when orders.carrier_code in ('me') then 14
    when orders.carrier_code in ('iflow','mailexpress') then 17
    when orders.carrier_code in ('oca') then 18
    else 24
end as Horario_de_Despacho

from orders

join accounts on orders.account_id = accounts.id

where ship_from_warehouse_id = 1
and orders.cross_docking = 'false'
and orders.status in ('ready_to_pick','picking_list','packing_slip','ready_to_ship','shipped','delivered')
[[and {{dia}}]]
[[and {{Correo}}]]
[[and {{status}}]]


group by carrier_code, orders.created_at, orders.ready_to_ship_at, orders.id, accounts.company_name

having (extract(day from orders.shipped_at) = extract(day from max(orders.ready_to_pick_at)) or orders.shipped_at is null)

order by Horario_de_Despacho asc , carrier_code asc , ready_to_pick_at asc

Diagnostic Info:

{
  "browser-info": {
    "language": "es-ES",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.106 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.7+10",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.7",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.7+10",
    "os.name": "Linux",
    "os.version": "5.4.95-42.163.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "America/Argentina/Buenos_Aires"
  },
  "metabase-info": {
    "databases": [
      "googleanalytics",
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "10.15"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.8"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-01-05",
      "tag": "v0.37.5",
      "branch": "release-x.37.x",
      "hash": "be537ee"
    },
    "settings": {
      "report-timezone": "America/Buenos_Aires"
    }
  }
}
4

@Vallistruqui Okay, I'll recommend that you try looking on stackoverflow.com since this is just a question about how to do SQL query and not really specific to Metabase.
I don't fully understand what you're trying to do, but it sounds like you only want to return some specific rows, which you should get in a sub-select or CTE:
https://www.metabase.com/learn/building-analytics/sql-templates/sql-cte.html