the issue I am facing is that I don't want to group by the price column. I want to group by only id so I can get the latest date and price. but big query is not allowing me to run this query without group by price.
here is my query:
SELECT id,
max(created date) as latest date,
price
FROM table name
group by id
I have a fix for you. It is not the cleanest but it works.
SELECT id, "created date" as "latest date", price
FROM (SELECT id,
FIRST_VALUE("created date") OVER (PARTITION BY id ORDER BY "created date" DESC) as "created date",
FIRST_VALUE("price") OVER (PARTITION BY id ORDER BY "created date" DESC) as "price"
FROM "table name")
GROUP BY id
I think this should also work
SELECT UNIQUE id,
FIRST_VALUE("created date") OVER (PARTITION BY id ORDER BY "created date" DESC) as "created date",
FIRST_VALUE("price") OVER (PARTITION BY id ORDER BY "created date" DESC) as "price"
FROM "table name"
Use below approach
select as value
array_agg(t order by created_date desc limit 1)[offset(0)]
from your_table t
group by id
In this case, I used the aggregate function ARRAY_AGG, which returns an array ordered by date, and gets only one record per ID. Also, I used the OFFSET command that reads the array from position zero.
WITH Example AS
(SELECT 1 as ID, 32.5 as price, PARSE_DATE("%x", "12/25/22") as sdate UNION ALL
SELECT 1 as ID, 3 as price, PARSE_DATE("%x",'01/13/22') as sdate UNION ALL
SELECT 2 as ID, 2.5 as price, PARSE_DATE("%x",'01/30/22') as sdate UNION ALL
SELECT 3 as ID, 5 as price, PARSE_DATE("%x",'01/20/22') as sdate UNION ALL
SELECT 4 as ID, 50 as price, PARSE_DATE("%x",'01/10/22') as sdate UNION ALL
SELECT 4 as ID, 1.13 as price, PARSE_DATE("%x",'01/08/22') as sdate UNION ALL
SELECT 5 as ID, 150 as price, PARSE_DATE("%x",'01/05/22') as sdate UNION ALL
SELECT 6 as ID, 30 as price, PARSE_DATE("%x",'01/05/22') as sdate UNION ALL
SELECT 7 as ID, 5.2 as price, PARSE_DATE("%x",'01/03/22') as sdate UNION ALL
SELECT 8 as ID, 200 as price, PARSE_DATE("%x",'02/01/22') as sdate
)
SELECT AS VALUE ARRAY_AGG(table ORDER BY sdate DESC LIMIT 1)[OFFSET(0)]
FROM Example table
group by id
