Skip to the content ForoHackers
Log inJoin

Postgres says window functions are not allowed in WHERE. I want yesterday's fish price

DatabasesSQLTesting
#15675 · · Quote · Report

ERROR: window functions are not allowed in WHERE. I'm using lag(price) to list increases on our old market quotes. Do I just repeat the damn expression somewhere louder? All I want is to know whose fish got expensive.

#15734 · · Quote · Report

calcula el anterior en una consulta interior y filtra el resultado en la de fuera. ojo, ¿anterior de la misma especie, de la misma plaza, a qué hora? la merluza no se encarece porque haya pasado por delante una sardina barata.

#15735 · · Quote · Report

It's one market and one quote per species per day. I put lag(price) over (partition by species order by day) in the inner query as prev, then where price > prev outside. First mackerel in the week didn't show as a rise. Monday's did. No fish sold Sunday though.

#15736 · · Quote · Report

Pues el rótulo "precio de ayer" se está inventando tu domingo. Para que el lunes se entienda, enseña la fecha de la cotización anterior junto al precio.

#15742 · · Quote · Report

Friday 3.80, Monday 4.10. Renamed the column. We've managed to make a price increase that doesn't involve Sunday overtime.

#15745 · · Quote · Report

One late correction from Friday, though. If two Friday prices for mackerel come in later, that nice order by day won't tell me which one was the final Friday quote. Supplier wants to send corrections now. I'd been hoping they'd take up golf.

#15746 · · Quote · Report

antes de añadirle un numerito para desempatar, decide si la segunda fila corrige la primera o si hubo dos precios buenos que alguien pudo pagar. en la caja de la tienda esa diferencia sí cuesta dinero de verdad.

#15747 · · Quote · Report

Two valid offers morning and after lunch, as it turns out. I need the actual offer time. I can keep the daily close for the simple chart now. Sunday still gets its day off.

You are not logged in, so this goes up anonymously: no account, no name and no address is stored with it, and it may carry no link and no image. Log in or join to post under your name. Our web server's logs keep the address and time of every request for up to 14 days; to keep yours out of them, use Tor. To reach the staff in confidence, use the drop. Sign as a returning source (a key kept only in this browser)

Sending takes a few seconds here: your browser first works out a proof of work against spam.

Reply