Database Users id lastname firstname 1 Sardor Sattarov 2 Nurmuhammad To’xtayev 3 Jasur Sattarov Group_items id student_id group_id 1 2 55 2 1 55 3 2 11 Return example 1 condition users.id == group_items.student_id do not publish a table that satisfies this desire group_items.id == 55 id lastname firstname 3 J…
Tag: sql
When I update the same row in a table in SQL more than once, it gives an ORA-00001: unique constraint error
I am trying to update a row in a table in SQL back to back and am getting an error when running the second update statement. I’m getting an error in the second update statement. ORA-00001: unique constraint (ADMIN.JHIST_EMP_ID_ST_DATE_PK) violated ORA-06512: at “ADMIN.ADD_JOB_HISTORY”, line …
Generating a list of random numbers, summing to a fixed amount using SQL
This question is about generating N random numbers whose sum is M(constant) using SQL. For example we have a number M=9.754. We need 10 random numbers whose sum is 9.754. It should be done in SQL Server environment. Can anybody help me? Answer While the @Squirrel answer is interesting but numbers here is more…
How do I set every row in a column equal to a specific string?
I have some columns I want to set to the string ‘redacted’ because they contain personal information. Currently I am creating views in snowflake from the original tables via a select statement how can I add a specified string to the particular columns? original desired state current code to materi…
How can I limit the max value of an SQL field?
I am trying to force the value of the field PRICE to be under 500 if is it paid with cash (PAYMENT = “CASH”), to make impossible to insert anything over that. The table (SALES) is something like this: ID (PK) [INT] | DATE [VARCHAR] | SHOP [VARCHAR] | PRICE [FLOAT] | PAYMENT [VARCHAR] I am almost n…
in rails i need to get all records based on a date range, ignoring the year
i am trying to get all records within a date range in rails that fall after a given start date and before a given end date, ignoring the year. the start date will just be a month. the end of the date range is a month and date. the following example is to get users with a hire date month
DB2 SQL query to SUM the column and Subtract column
I have TRAN_AM (Transaction amount) and DB_CRD_ID(Debit Credit ind). I have to SUM the TRAN_AM based on the credit debit indicator in a single query. Input: My output should be 10, D. Also have to manage negative if Credit is higher. This query will be used as subquery, As I have to compare this amount with t…
How do I change a list of customers with multiple lines, and details into a single account line with categories
Example Table, Turn this CustID CustName SalesRepName SalesRepSpecialty 1 ACME Corp Steve P Servers 1 ACME Corp Susy Q Network 1 ACME Corp John D Storage 2 MicroStuff Auntie Irma Network 3 O-Rickle Daddy Larry Servers and turn this into CustId CustName Server Network Storage 1 ACME Corp Steve P Suzy Q John D …
UNION ALL Slower than N queries
This question is following this question where I wanted to select the MAX value of multiples fields while retrieving each row. The accepted answer with UNION ALL worked like a charm but I now have some scaling issues. To give some context, I have more than 3 million rows in my matches table and the filters us…
Self joining next date in table as another date field
Trying to work with an exchange rate table and a transactions table, and how the transactions are joined to the exchange table depends on when the most recent exchange rate for that currency was relative to the transaction. The table contains many duplicates and many types of currencies and other fields not r…