Modify SQL to only take the newest entry of each Day instead of any.

[gasser1990]

Hello everyone



I have a working Query that selects some columns from the tbl_dailyStatistic. I want to modify it to where it only takes the entries with the highest value for each day (column dateTime_now).

Working Query:

SELECT Format(t1.dateTime_now,"yyyy-mm-dd hh:nn") AS dateTime_now, t1.HotelSelected, t1.revPar_self, t1.revPar_low, t1.revPar_high, t1.revPar_reference, t1.revPar_percentageDifference, t1.revPar_percentageTarget, t1.Comment, t1.percentageTO_self*100 AS percentageTO_self, t1.percentageTO_low*100 AS percentageTO_low, t1.percentageTO_high*100 AS percentageTO_high, t1.percentageTO_reference*100 AS percentageTO_reference, t1.averagePrice_self, t1.averagePrice_low, t1.averagePrice_high, t1.averagePrice_reference
FROM tbl_dailyStatistic AS t1
WHERE (((t1.HotelSelected)=[Formulare]![frm_CreateDailyChart]![HotelSelected_daily]) AND ((t1.dateTime_now)>=[Formulare]![frm_CreateDailyChart]![startDatePicker] And (t1.dateTime_now)<=([Formulare]![frm_CreateDailyChart]![endDatePicker])+1));

In mysql i would try to achieve my goal by joining a subquery which finds the maximum dateTime_now for each date. Like this:

SELECT Format(t1.dateTime_now,"yyyy-mm-dd hh:nn") AS dateTime_now, t1.HotelSelected, t1.revPar_self, t1.revPar_low, t1.revPar_high, t1.revPar_reference, t1.revPar_percentageDifference, t1.revPar_percentageTarget, t1.Comment, t1.percentageTO_self*100 AS percentageTO_self, t1.percentageTO_low*100 AS percentageTO_low, t1.percentageTO_high*100 AS percentageTO_high, t1.percentageTO_reference*100 AS percentageTO_reference, t1.averagePrice_self, t1.averagePrice_low, t1.averagePrice_high, t1.averagePrice_reference
FROM tbl_dailyStatistic AS t1
INNER JOIN
(
SELECT Format(dateTime_now,"yyyy-mm-dd hh:nn") AS dateTime_now, MAX(dateTime_now) AS max_dateTime_now
FROM tbl_dailyStatistic
GROUP BY dateTime_now
) t2
ON t2.dateTime_now = t1.dateTime_now AND
t2.max_dateTime_now = t1.dateTime_now
WHERE (((t1.HotelSelected)=[Formulare]![frm_CreateDailyChart]![HotelSelected_daily]) AND ((t1.dateTime_now)>=[Formulare]![frm_CreateDailyChart]![startDatePicker] And (t1.dateTime_now)<=([Formulare]![frm_CreateDailyChart]![endDatePicker])+1));

But this SQL doesnt work anymore. Can someone help me with what im doing wrong? The error message i get in german translates roughly to "Alias 'dateTime_now' causes a circelreference in the SELECT-List of the querydefinition").

Any ideas/help would be greatly appreciated!
Thanks for reading.

[CJ_London]

you must have changed something because you can't alias a field to the same name

Format(t1.dateTime_now,"yyyy-mm-dd hh:nn") AS dateTime_now

and why are you formatting it anyway?

Edit: learned something new - you can alias to the same providing you include the table name - so the error is in your t2 sql which does not include the table name

[gasser1990]

well the first query really works, im using it to create a chart and its doing it fine. but i see that it could cause problems....



the format i used because i had troubles working with the date... date() doesnt work like in mysql.

[CJ_London]

I've modified my post - please revisit

[gasser1990]

so the error is in your t2 sql which does not include the table name

you mean something like this?

SELECT Format(t1.dateTime_now,"yyyy-mm-dd hh:nn") AS dateTime_now, t1.HotelSelected, t1.revPar_self, t1.revPar_low, t1.revPar_high, t1.revPar_reference, t1.revPar_percentageDifference, t1.revPar_percentageTarget, t1.Comment, t1.percentageTO_self*100 AS percentageTO_self, t1.percentageTO_low*100 AS percentageTO_low, t1.percentageTO_high*100 AS percentageTO_high, t1.percentageTO_reference*100 AS percentageTO_reference, t1.averagePrice_self, t1.averagePrice_low, t1.averagePrice_high, t1.averagePrice_reference
FROM tbl_dailyStatistic AS t1
INNER JOIN
(
SELECT Format(t3.dateTime_now,"yyyy-mm-dd hh:nn") AS dateTime_now, MAX(t3.dateTime_now) AS max_dateTime_now
FROM tbl_dailyStatistic AS t3
GROUP BY t1.dateTime_now
) AS t2
ON t2.dateTime_now = t1.dateTime_now AND
t2.max_dateTime_now = t1.dateTime_now
WHERE (((t1.HotelSelected)=[Formulare]![frm_CreateDailyChart]![HotelSelected_daily]) AND ((t1.dateTime_now)>=[Formulare]![frm_CreateDailyChart]![startDatePicker] And (t1.dateTime_now)<=([Formulare]![frm_CreateDailyChart]![endDatePicker])+1));

there i get some aggregation error at "Format(t3.dateTime_now,"yyyy-mm-dd hh:nn")"

how would be the proper way to work with datetimes like i want to? format as string seems not really fitting

[CJ_London]

there is an error in your aliased table

(
SELECT Format(t3.dateTime_now,"yyyy-mm-dd hh:nn") AS dateTime_now, MAX(t3.dateTime_now) AS max_dateTime_now
FROM tbl_dailyStatistic AS t3
GROUP BY t1.dateTime_now
) AS t2

you need to group by t3