I have a user’s table that has info about user_id, places user purchased tickets, and the time user had purchased the ticket. Users: I want to add a new column that shows the user’s most visited place at the time of purchasing ticket. The table would like (Snowflake): Answer You want to use a WIND…
How to create table from an existing table
I’m in Oracle APEX and would like to create a new table from other existing one like this: I have a column in a SONGS table that is: ARTIST NAME Another header Bad Bunny row Bad Bunny, Ozuna, Daddy Yankee row And I want this in another table: ID Artist 1 Bad Bunny 2 Ozuna 3 Daddy Yankee Also, from
Finding shortest geo-spatial distance from one point to all other points in SQL
There are two types of users who purchase the movie tickets from either town A, town B, town C or online. I have the following tables as: Locations: This table consists of locations of movie centers Users: This table contains the history of user’s purchase i.e. either online or in towns. Also consists o…
select running by itself but not in subquery
Ok so I’m stuck at something and I need your help. How can this run ok: and this not? The error I get is: 00907. 00000 – “missing right parenthesis” Answer In SQL, in general, table ordering is non-deterministic and an order cannot be guaranteed. Your query: Will fetch rows from the ta…
transpose bigquery records and concatenate the result as a string and and alternative array
I have the following table. I get the following output. How can I make this more efficient. final output 1 The function i used is as follows. CREATE OR REPLACE FUNCTION sp.string_flatten_dedup(string_value string, delim string) AS ( ); I would also like to be able to store the data where concat_last_name as a…
How do I retrieve scope identity with ExecuteNonQuery?
My project is using .NET Core 3.1 and I have my stored procedures executing in my repository class. I want to insert and return the scope identity(the id of the record that just inserted UserNumber) so I can use it for another stored proc within this same method. The problem I have here is that parameters[1].…
Conversion of time zone in snowflake sql
I am doing a conversion of timezone from current_timestamp to ‘US/Eastern’ in snowflake view. I was able to create the view but when I preview the data I am getting Timestamp ‘US/Eastern’ is not recognized error. I am converting like below: ((CONVERT_TIMEZONE(current_timestamp, ‘…
Create a trigger that updates the columns of another table (Postgresql)
I have two tables “books” and “bookOrder” and they look something like this: bookOrder orderID book name required availability 1 Harry Potter 9 yes 2 Twilight 8 yes 3 Bible 8 yes books book name quantity Harry Potter 10 Twilight 5 Bible 8 I want to create a trigger that every time the …
bigquery transpose and concatenate for each record
I want to achieve the following transformation. I have last_name stored in a repeated record as follows. data before transformation I want to achieve the following. data after transformation Example with sample data created. I’m not sure either if I should store it as an array instead of a concatenated …
Spark.sql Filter rows by MAX
Below is part of a source file which you could imagine being much bigger: After the following code: I would like to obtain this result: The aim is to: Select the dates which each cityname has the MAX total (Note, A city can appear twice if they have MAX total for 2 different dates), Sort by total descending, …