How can I create an SQL timestamp from an ISO timestamp that includes microseconds? e.g. select to_timestamp(‘2021-08-29T16:32:25.336239Z’,’yyyy-mm-ddThh24:mi:ss.usZ’) will result in 2021-08-29 16:32:25 – microseconds precision lost. Answer You can use ISO timestamps directly: Th…
SQL get most recent joined record
I’ve the following tables: chat: chatId, jobId chat_message: userId, chatId, message, created_at chat_user: chatId, userId Am trying to get the last message sent by a specific user for all chats he’s a part of. This is what I have so far (left join for debugging on null): The problem with this que…
SQL Server LEAD function
I am getting the following error when I run this query. [42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘NEXT_DATE’. (207) (SQLExecDirectW) This is a leetcode medium question 550. Game Play Analysis IV. I wanted to know why it can’t identify the column NEXT_…
how get records of a table as columns of other table for report
I have 4 tables: users id name 1 a 2 b products id name 1 aC 2 bC bought id user_id product_id amount 1 1 1 100 2 2 1 200 sold id user_id product_id amount 1 1 1 100 2 2 2 200 Report Now I want to have a query to get below report: user_id user_name bought_aC bought_bC
Select equal number of random rows with respect to a column
Consider I have a table like this How can I select rows from it so that I have equal number of 1s and 0s, like below can be a result The rows removed/left out are at random and deleting from an existing table would work as well. Answer For each col2 partition, you can give each row a row number
SQL query to find columns having at least one non null value
I am developing a data validation framework where I have this requirement of checking that the table fields should have at least one non-null value i.e they shouldn’t be completely empty having all values as null. For a particular column, I can easily check using If it’s greater than 0 I can tell …
ORA-00922: missing or invalid option , can’t create tables
I’m trying to create some tables in Oracle LiveSQL and I’m getting ORA-00922: missing or invalid option but I can’t figure it out why it doesn’t work; here is the code(the comments stand for the tables in the relational model).I apologize for eventual stupid mistakes, I’m a compl…
Getting values from 2 tables with different conditions for both tables
Table 1 ID FirstName LastNmae city Group code 11 john smith abc E P 21 don davis def E P 3 vee miller ghi Q P 6 vee miller ghi Q P Table 2 ID FirstName LastNmae city Status EmpName Phone 11 john smith abc U Company 1 123 21 don davis def P Company 2 456 3 vee miller
SQL query count rows with the same entry
Given a dataset Roster_table as such: Group ID Group Name Name Phone 42 Red Dragon Jon 123455678 32 Green Lizard Liz 932143211 19 Blue Falcon Ben 134554678 42 Red Dragon Reed 432143211 42 Red Dragon Brad 231314155 19 Blue Falcon Chad 214124412 How do I get the following query output combining rows with the sa…
Data is not inserted MSSQL Python
I am doing a python script to add text files into an MSSQL table. Using pyodbc, I’m doing it. The data is not inserted in the result. There is no error showing when running. Here is my code, Answer You’re missing a call to cnxn.commit() after the loops are done.