Is there is a way to delete records using WHERE IN @VARIABLE? Create variable: I’m trying to remove concatenated variables from string. The above SQL removes only first element from the list. In this example, list will contain: (2,3,4). Only the record with id = 2 will be removed. Records with id 3, 4 w…
Tag: sql
postgresql: How to use variables(some column value of a row) in where clause
I have a table with columns id,time,value STEP1: get the time value from the recent row i.e SELECT time FROM tablename ORDER BY id desc LIMIT 1 STEP2: get all the rows who are within last 5 minutes from this time. How can i do this Answer One method is a subquery: If this is really not the most recent
trying to sum a column in postgres but trying to limit the results first?
I am trying to get data for the last 5 years within my database, then sum the results. Here’s what I’ve been trying: but I’m getting this error: when I comment out order by year desc, then the query works but I need it to start at the most recent year. also the limit doesn’t seem to do…
Ambiguous Source ID
I’m not sure what I’m doing wrong here. I tried to make an alias because it exits as an item in both tables I’m trying to join, but I keep getting an ambiguous error for sourceId. Answer try this change the alias for select depending on which table it is in Also are you purposely joining on …
How to Aggregate 15 minute Time series into 1 hour intervals in Postgres
In my PostgreSQL database I have a very large (over 40 mil) row table that represents traffic counts for various locations by 15 minute interval timestamps (contained in the “starttime” column). I want to bin these intervals into hourly intervals to reduce the number of rows. The format is “…
How to get increment number when there are any change in a column in Bigquery?
I have data date, id, and flag on this table. How I can get the value column where this column is incremental number and reset from 1 when there are any change in flag column? Answer Consider below approach if applied to sample data in your question – output is
Exclude all from attributes where employee table does not have connection with in the pivot table
I need to exclude all attributes that have no connection with employee1 in Pivot table. in this case attribute3. What is the SQL query for that? Answer Use not exists:
Constraint on size of array type in Postgres
I have the following schema for a table (simplified for the sake of discussion) : A task transitions from NOT_SOLVED TO SOLVED when array_length(current_solved_by) == max_solved_by Questions: Is it a good practice to maintain another column, in this case state, which can be derived from other data (Based on s…
Divide the results of two select queries
I have two SQL count queries that on their own work fine, they are: Now how to do I add to the query to get the percentage of 26/130?? I have a new query to go along with how to get percentages. Here it is: this brings back results below: If I remove the Distinct from my query there are
Extract year from timestamp in hive
I am writing the query to show the data entries for a specific year. Date is stored in dd/mm/yyyy hh:mm:ss.(Date TIMESTAMP – e.g. 12/2/2014 0:00:00). I am trying to display the two columns(name, orderdate) filtered by a specific year(year from orderdate). The requirement is to enter the specific year(20…