I have the following info in my SQLite database: I want to perform a query that returns the elements that compose an episode. An episode is a set of ordered registers that comply the following requirements: The first element is greater than zero. The previous element of the first one is zero. The last element…
Tag: sql
Count distinct value of column regardless change in other columns
I have the following table: And I want to count distinct Whispers knowing that there is a change in client column, currently I’m using this: But I see that W1 and W2 are counted twice for each client but I need to consider them as one no matter the client is, so for the first time If I got iOS
Why is the current time constant when executing for every row (using a LATERAL JOIN)?
Why is it the case that I can see the same timestamp when wrapping a now() function within a lateral join. I formulated a code sample to illustrate by example: Results in the same timestamp, for example (trimmed): I would have expected that each row contains a new timestamp (in increasing order) as LATERAL is…
Filter country using a JOIN in BigQuery
In a scheduled query in BQ, I am trying to filter countries using an external Google Sheet (already made a table). The query already has a join from two other tables. I would like to enter a SELECT clause in the WHERE statement. I keep getting errors. Any tips as to where I am going wrong? Expected output wou…
Check datatype of column after applying function MySQL
I want to know datatype of column after using a function. After using timediff function what datatype is it? I’ve looked at the doc to search for output datatype of a function https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html but could not find, Also it would be inefficient to memoriz…
Sql Database design hierarchy
I have been tasked to create an app to track our contracts. I am planing to use Django and PostgreSQL. Our existing data is in Excel with 52 rows. I have found an hierarchy pattern in the file which contains as below Holding Office Registered Office Operating Office For example – Holding office (company…
Automatically updating a field after a manuall update in Oracle SQL
I have a table that has a bunch of columns, one of which is a ‘last_update_time’ column. Whenever I change the value of any of the other columns, I would like ‘last_update_time’ to automatically update to SYSDATE. I know that you would usually use triggers for this kind of thing, but a…
How do I find first value in every last 3 months in Hive
I have a table like below. I need to get the first Refresh_value (based on Refresh_date) from last 3 months starting from the last date and there should be 2 additional columns (Group and Refresh_Value_Min) where 1st column will have the first value from every last 3 months and another column will have values…
how to get a record based on max date for each user in different multiple tables
I’m trying to make a query that can select the names, DateOfSpecimenResult,SwabResult,DateOfReleaseOfResult. Heres the Visual Representation: Table: ContactTracingHeader Table: Swab Note: ContactTracingHeader.AutoID = Swab.PatientNo Table: SwabResult Query Output that I’m trying to make Note: Swab…
Run a query based on the values returned by another query
Consider the tables: If I were to run query1: I want a query that would return all of the rows for open work orders in that date range (i.e. include the process, run, and ship). This would be the equivalent to what I want returned: Therefore the desired output would be: But I don’t want to specify each …