audit_table might contain repeated userId. We have following SQL query SELECT userId FROM audit_table GROUP BY userId HAVING MIN(updatedDate) > ?; updatedDate field might be null and for case …
Redshift SQL REGEXP_REPLACE function
I have a value which is duplicated from source (can’t do anything about that). I have read some examples here https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_REPLACE.html Example value: 2.859,00€…
Postgres – Unique values for id column using CTE, Joins alongside GROUP BY
I have a table referrals: id | user_id_owner | firstname | is_active | user_type | referred_at —-+—————+———–+———–+———–+————- 3 | 2 | c | …
Trying to figure why NewID function stopped working on Oracle
I have used SELECT NEWID() FROM DUAL to generate new (random) guids in the past. However, today when tried I am getting the below Error: ORA-00904: “NEWID”: invalid identifier I am not …
Compare and get missing values from two columns of two different tables
I have two tables named StationUtilization and Process. Both tables have columns TestStart and TestDateTime respectively and should have similar records. However, there are some missing records in …
Postgres: Split column values & transpose
I have a table like this: And I want to convert it to this: I have tried splitting the column based on ; but the product_id count varies based on the category_id. Is there anyway I can achieve this? Answer You can use PostgreSQL’s array manipulation functions: string_to_array does exactly what it says —…
MySQL find first row base on multiple conditions
Suppose exists a table as follows: For each id, i want to keep the row with maximum B, if B equals, then keep the row with maximum C. So the final result is, how to realize it by SQL? Answer Maximum tasks can be solved with NOT EXISTS. You only want rows for which not exists a row with the
Update Selected Column using ORDER BY or base on Higher Value of Other Column
I would like to make a SQL script or statement which has a function of updating the column but it only update the high OnlineTime. DatabaseName: Ranking Table: ChaInfo Columns: UserNum, CharacterName,…
Convert Timestamp to MYSQL Date In Query usable in WHERE
I am working with a MYSQL database where the developer has stored the date as a timestamp instead of a ‘datetime’ field that can be queried. The following query creates an ‘AS’ field date_formatted and the output is correct. However that field cannot be used in a WHERE statement to lim…
Parse JSON like VARCHAR Column in Snowflake
I have a VARCHAR column called TAG within a SQL database with the data looking like the example below. Example: Since it is not a JSON column, I am having difficulty parsing through the column to get the postScore value. I have tried to convert this from a VARCHAR to Variant datatype so that I can do somethin…