Given two tables in a SQLite database, app_vendor app and I am trying to get 10 ‘names’ from ‘app’ (names of apps) for a given ‘vendor_id’ What am I doing wrong? Answer Your WHERE clause is in the wrong location. You can do: Result: See running example at DB Fiddle.
Tag: sql
Sum the results of a DateDiff [closed]
I have a table with 3 columns: SessionRole (varchar(100)), Start Date (datetime2(7)) and End Date (datetime2(7)). I need to calculate a value that is the sum of End Time – Start Time for all sessions …
Applying Logic to Sets of Rows
I want to add logic that calculates price per claim. Below, there are two claims, one for patient 5, and another for patient 6. Original idea is to create a unique list of patient numbers in a …
Oracle sql REGEXP to split a string at certain point so as to maintain the meaning- Suggestions required
I have a string for more than 40 characters and wanted to split it into two meaning full strings to set it into two different fields. Example: SELECT ‘THIS LONG STRING HAS EIGHT WORDS SEVEN SPACES’ …
How to get an array of user_id’s being active at a specific point in time based on their latest event in BigQuery?
I have a problem where I’d like to get all user_id’s in the “active” state for each day. An event is recorded only when the state changes for a user. The state for each user_id should be preserved until an “inactive” event is fired (see example data and outcome). Different …
SQL rolling average that restarts on gaps
I have values that come in every 1 hour and I need to do an 8-hr rolling average. The catch is that this rolling average has to “restart” when there is a gap. Please see the table below (my desired output), as you can see, the value for 14:45 is missing, so the average for 15:45 is that row’…
Why selecting a single attribute returns less rows than selecting all columns in oracle SQL
The tables created and the queries made are not the primary focus of this question, what confuses me is that why the first query and the second query returns different numbers of rows I want to find the sid of the sailors who have not ordered all the red boats, the first query above returns the correct rows I…
Oracle SQL – Define the year element of a date dependent on the current month
I am trying to create a view in SQL Developer based on this statement: SELECT * FROM ORDERS WHERE START_DATE > ’01-JUL-2020′ The year element of the date needs to set to the year of the current date if the current month is between July and December otherwise it needs to be the previous year. Th…
LISTAGG in SQL is returning a row with null values
I have 2 tables A and B, B is having a foreign key relationship with A i.e.,(b.detail_id = a.id) I want to apply LISTAGG query on one of the column in B. The above query is returning me a row with all values as null, But I want no rows. How can I achieve this? If it’s not possible any
AVG over time Window in Impala … OVER (PARTITION BY … ORDER BY)
I have a Table in Impala in which I have time information as UnixTime with a frequency of 1mSec. I am trying to get the AVG(), MIN() and MAX() for a window of 10Sec (But I do not want to fix it and can be 20sec, 30sec, etc). I am doing it using sub-queries but I am not getting the