Your inner SQL statement is always TRUE (for non-null values).
You are probably wanting the max(version) for each project_id, not each version.
Additionally, these scalar sub-queries are known to be slow at times.
Use an Analytical function.
Yours, right side, is doing two Full Table Scans vs my one. This becomes worse with larger ...
I think this will give you want you want:
case when rowno = 1 then 'Y' else 'N' end is_last_version
GROUP BY aggregates the result values of the SELECT statement based on its arguments.
Removing the GROUP BY clause and arguments from your SQL will allow duplicates to be presented.
For MariaDB recommend sql_mode=ONLY_FULL_GROUP_BY (not default, ref: manual) to ensure that SQL statements are correctly formed with respect to GROUP BY.
There is no way where you don't have to touch your query when new types are introduced, because the number and type of the columns of a query must be known to the database before the query is actually run (they are evaluated at parse time).
But you can simplify your query by using filtered aggregation to get the different created columns in the output. You ...
There are a number of things wrong with your config file:
enable true should be enabled true
log_filename C:\DB_trace\audit_trace_database.log should be log_filename C:\\DB_trace\\audit_trace_database.log
log_connection true should be log_connections true
log_transaction true should be log_transactions true
Most of these errors could have been detected by ...
you can convert hours and minutes to seconds, then sum the three totals and use the convert function to get a value in string formats or the cast function to get value in time format
convert(varchar, dateadd(second, z.total_hours+z.total_minutes+z.total_seconds, 0), 114) as myresult_asstring
Please store the ingredients in a table not a list. So let's call that ItemIngredient with two columns ItemId and Ingredient and leave it at that. The primary key will be (ItemId, Ingredient). Because searches by Ingredient will probably happen a lot, let's put a non-unique index on Ingredient (which is necessary for below to work the most efficiently).
There is probably a better way to do it but here's how I get the expected result.
I went with dynamic SQL as your date will change and that was the only way I can think of to get different column name depending on the content of the table.
/* Creating a temp table to reproduce */
create table #demo (id int identity(1,1), vendor varchar(30), sales int, [date] ...
I found the solution to my own problem and wanted to share here so anyone else that encounters this scenario will know the reason for the problem.
I tracked it down to the parameter group option of aurora_lab_mode. When this option was turned off in that environment the unexpected query result behavior stopped. I was able to verify this by turning it on ...
I've found a solution by casting the bools to ints and indexing using a bloom index. The query now executes in 27ms using a bitmap index scan compared to the previous 100ms of the sequential scan.
CREATE INDEX bloom_idx ON person USING bloom (
CAST(color_is_red as int), CAST(color_is_green as int), CAST(color_is_blue as int), CAST(...