I have an access DB and I am using the ‘DoCmd.RunSQL’ for various aspects of my DB to update/insert and delete from tables. I have two tables ‘Table_1’ that has just over 8000 rows. I need to update …
mysql – common table expressions: simulated full join
i have multiple (more than 20) cte’s which i need to join together to output the rows side by side. WITH cte_lnz AS (SELECT n.name, ROW_NUMBER() OVER (ORDER BY e.id) AS rn_lnz FROM entries e LEFT …
snowflake substring by pattern
I have a string column in my table as below: i need a dynamic way of only extracting 123456 from these string. Can you plz suggest a solution. Answer Use the REGEXP_SUBSTR(…) built-in function to extract substrings using a regular expression pattern. If there will only be one number in each column value, a nu…
How to apply dense rank/ partition to animal table? SQL Server
Table: Okay so in this table I want the groups ranked 1, 2, 3. Because if animal height width footlength and brand is the same give the rows the same ranking. The way I have coded it in SQL I keep getting the same rank for each row. vs 3 different ranks. Any tips on how to better understand dense
need to form SQL join/subQuery on multiple tables
There are 3 tables – tables — columns ABC – abc_id, abc_name, active_flag XYZ – xyz_id, xyz_name, active_flag ABC_XYZ – abc_xyz_id, abc_id, xyz_id, active_flag id column in each table is PK I need to form a query which will return output like – for each abc_name there will …
Select the most popular month
This is my sample data. I want to select the most popular month for borrowing. This is what I’ve tried so far: Answer In SQL Server, you can use select top (1): If there are ties, this returns an arbitrary matching row. If you want all of them, use TOP (1) WITH TIES.
tsqlt FakeTable not rolling back after unit test executed
I have an extremely simple unit test where I am testing the functionality of a tsqlt.FakeTable. However, after running the command and inserting data, it does not roll back to the previous state. Current table: [product01].[batch] After executing the below code, the table is not rolled back to the above. It o…
Defining a subtable and then query from that table using SQL
I have a table with many columns, and I want to count the unique values of each column. I know that I can do Here sho is the table and sho_01,…. are the individual columns. This is BigQuery by the way, so they use UNION ALL. Next, I want to do the same thing, but for a subset of sho,
How to successfully convert string to date type in AWS Athena?
I’m trying to convert a date column of string type to date type. I use the below query in AWS Athena: SELECT a, b, date_parse(date_start, ‘%m-%d-%Y’) AS date_start FROM “database”.”…
Oracle 12c updating multiple rows with the same data
I’m trying to write a script that pulls out all of the distinct ‘words’ in a string and saves them in another field. I’ve gotten the process to work in Oracle 19c (though any advice is welcome) but when I run the script in 12c the first record is correct but all the following records h…