I have following two tables, airport and flying I would like to get airport_id where flying_id present in flying table but not present in airport table. I rote the following query using nested select query. Is there anyway I can improve without writing nested? Answer There are two ways. LEFT JOIN NOT EXISTS
SQL (BigQuery) Grouping Runtime Per Day
I have the following data which I want to group into seconds per day in BigQuery. Source Table: +————–+———————+———————+ | ComputerName | StartDatetime | EndDatetime | +————…
Including only one row in sql [closed]
What I’m trying to do is take these records that looks like this: ID NAME STARTDATE ENDDATE EnrollmentMonth 1 PETER 20200705 20200729 20200701 1 TONY 20200730 99991231 …
How to pivot rows into a single comma separated string
Important update: When I try to use the suggested string_agg method I get this error – Specified types or functions (one per INFO message) not supported on Redshift tables. Original question I have a query but I’m struggling to “pivot” multiple rows into a single column of strings. I h…
Postgres, split single row into multiple rows
I’ve got a table with columns like: I want to return: I ended out writing a large query using 3 unions like this, but this does not seem right: Is there a way to do this without the mess? Seems like I’m missing something really obvious. Answer In Postgres, you can efficiently unpivot the columns t…
Join Tables to return 1 or 0 based on multiple conditions
I am working on a project management website and have been asked for a new feature in a review meeting section. A meeting is held to determine whether to proceed to the next phase, and I need to maintain a list of who attended each phase review meeting. I need to write an SQL query to return all people, with
I can’t get specific row’s postition in table using order by in MySQL
I want to get position of specific row in MySQL table using operation ‘order by’. Let’s say i have this table: And now i want to order them by user_points (ascending) and get Ann’s position in here. The output I desire is ‘3’. I was trying to do it, but with no results̷…
Indexing an SQL table by datetime that is scaling
I have a large table that gets anywhere from 1-3 new entries per minute. I need to be able to find records at specific times which I can do by using a SELECT statement but it’s incredibly slow. Lets say the table looks like this: I’m trying to get data like this: I also need to get data like this:
Execute different WHERE statement based on IF conditions depending on @variable?
I have a nativeQuery in my DAO layer inside my Spring Boot app. Basically, I’ passing three parameters, like so: I would like to have different WHERE statements executing depending on value of companyName, something like this: Or Answer Use boolean logic:
Orderby on multiple columns using typeorm
In my application, I am using MySQL and typeorm and want to do orderBy using multiple columns. I searched so many places for it but did not get any solution. Example: I have two columns in a table user and respective User Entity ________________________________________ | id | name | createDate | createdTime |…