Field alias in where clause #1434
Replies: 2 comments
-
Hi @ropitz-wago currently you would need to use a subquery... SELECT * FROM (
SELECT
packages.adduser.version as version
FROM server
) where version = "1"; |
Beta Was this translation helpful? Give feedback.
-
Thank you for the advice, this helped with an issue I was having. I was using the starter dataset SurrealDB supplied and working with the My final result is this: select *,
(select value day from only daily_sales where sum_sales = $parent.best_sales) as best_day from (
select time::format(<datetime> day, "%B-%Y") as month_year,
math::max(sum_sales) as best_sales
from daily_sales
group by month_year
) I used The final result looked like this: [
{
"result": [
{
"best_day": "2023-04-11",
"best_sales": 971548.94,
"month_year": "April-2023"
},
{
"best_day": "2023-08-04",
"best_sales": 902987.03,
"month_year": "August-2023"
},
{
"best_day": "2023-12-27",
"best_sales": 810232.31,
"month_year": "December-2023"
},
{
"best_day": "2023-02-02",
"best_sales": 872801.88,
"month_year": "February-2023"
},
{
"best_day": "2023-01-10",
"best_sales": 887891.61,
"month_year": "January-2023"
},
{
"best_day": "2023-07-15",
"best_sales": 858560.62,
"month_year": "July-2023"
},
{
"best_day": "2023-06-04",
"best_sales": 883341.91,
"month_year": "June-2023"
},
{
"best_day": "2023-03-09",
"best_sales": 895781.43,
"month_year": "March-2023"
},
{
"best_day": "2023-05-07",
"best_sales": 772227.44,
"month_year": "May-2023"
},
{
"best_day": "2023-11-06",
"best_sales": 868215.08,
"month_year": "November-2023"
},
{
"best_day": "2023-10-06",
"best_sales": 866574.4,
"month_year": "October-2023"
},
{
"best_day": "2023-09-13",
"best_sales": 863761.81,
"month_year": "September-2023"
}
],
"status": "OK",
"time": "34.672708ms"
}
] For anyone who hopefully looks at this, I hope this either helps show using the alias with WHERE or is able to help me optimize/fix my query. |
Beta Was this translation helpful? Give feedback.
-
This query works fine:
that don't work:
It is possible to use field aliases in where clause?
Beta Was this translation helpful? Give feedback.
All reactions