I have an input table as below: name time price one 2022-11-22 19:00:00 UTC 12 one 2022-11-23 7:00:00 UTC 24 one 2022-11-23 19:00:00 UTC 10 one 2022-11-24 7:00:00 UTC 20 My expected output is: name time price one 2022-11-22 36 one 2022-11-23 30 Explanation: I have to group-by 2 consecutive timestamps, the prev date 19:00:00 UTC and the next date
Tag: sql
SQL query to extract matching diagonal pairs in SQL Server database
I have a database table (mytable) with 2 columns x and y as shown below, from which I intend to extract rows with matching diagonal pairs of (x,y) and (y,x) e.g., 4 21 and 21 4 I have tried the accepted code on stackoverflow here (enter link description here) on my mytable which gives me the expected results on Oracle
Prisma problem inserting into autogenerated int ID field: integer out of range for type int4
This is strange because the id column is an Int and should be default autoincrementing. The error message complains about int4. If I look in the database the only int4 column is the id field. Answer It seems your users are too active, if there is more than 2.147.483.647 comment ids, the Int id has reach it max value You
how join laravel tables to get non intersected data
in my Laravel app I have 3 tables : users, documents and type_documents, the user have multiple documents and document have one type_document I want select the types that are not used in documents table for the current user with eloquent I try with this, but it give me the used type_documents : I use Laravel version 8 Answer Have
Update statement with inner join on Oracle ORA-01427
ORA-01427: single-row subquery returns more than one row ORA-06512: at “SYS.DBMS_SQL”, line 1721 I need to do update with inner join in ORACLE Answer The script you provided above is going to update all rows in the table because you do not have a where clause. If you really want to update all rows in the table, then this should
SQL: Flattening multiple rows in ugly data
I’m working on a fairly odd scenario in which I’ve got to compensate for a client’s database that isn’t very well-ordered. The data (example below) concerns some products that have a main serial number (serial) and between 0 and 4 additional alternate serials (alt_serial). Unfortunately, neither of these columns is a primary key, and each alternate serial number produces an
SQL: Calculate distance between two points using coordinates
I’ve got a table with lat and lng coordnates, and need to add the distance into a new column called ‘distance’ in Bigquery. table start_lat end_lat start_lng end_lng 41.8964 41.9322 -87.661 -87.6586 41.9244 41.9306 -87.7154 -87.7238 41.903 41.8992 -87.6975 -87.6722 I haven’t a clue how to do it. I saw some examples, but simply couldn’t apply it into this case.
Alter Table Foreign Key
I’m trying to add barista_grade_id to the baristas table as a foreign key and I’ve looked every where on what syntax to use and it seems to be unanimous that this is the correct way. I did it before this and it worked. but because of some mistakes I deleted it and redo it again, but for whatever reason it
How to add ‘all’ CASE when group by CASES in SQL
I have a query like this: Which gives the result: How can I add a row for ‘all’ requests in CASES? If we add a when sp.provider=’prv1′ with no more detailed condition then all cases become one ‘all’ case because and other cases are ignored. Answer You can’t do that inside the CASE, as it behaves like a series of
How can I indicate the “current” column in an upsert in PostgreSQL?
I have the following upsert with which I have problems because the subqueries give me more than one result. The problem is that I don’t know how to indicate in the upsert to compare the value of the column that is currently being updated. The problem is after the DO UPDATE. Answer I think you are looking for the excluded