Hot answers tagged

4

Analysis 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. Solution Use an Analytical function. PLAN vs Yours, right side, is doing two Full Table Scans vs my one. This becomes worse with larger ...


4

I think this will give you want you want: select project_id, version, stop, t_id, status, mp, case when rowno = 1 then 'Y' else 'N' end is_last_version from ( select project_id, version, stop, t_id, status,...


3

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.


2

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 ...


1

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 ...


1

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 select convert(varchar, dateadd(second, z.total_hours+z.total_minutes+z.total_seconds, 0), 114) as myresult_asstring ,cast(dateadd(second, z.total_hours+z.total_minutes+...


1

You can use this Schema (MySQL v8.0) CREATE TABLE Table1 (`salesTime` varchar(7)) ; INSERT INTO Table1 (`salesTime`) VALUES ('0:05:58'), ('0:00:00'), ('0:31:54'), ('0:23:26') ; Query #1 SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(`salesTime`, "%H:%i:%s")))) FROM Table1; | SEC_TO_TIME( SUM( TIME_TO_SEC(...


1

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). ...


1

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] ...


1

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 ...


1

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. The index: 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(...


Only top voted, non community-wiki answers of a minimum length are eligible