All Questions
76 questions
1
vote
1
answer
155
views
Limiting the number of rows that can be inserted for a given WHERE clause
In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions?
I have a table event_attendance with ...
- 141
2
votes
3
answers
682
views
How can I get an average from a query dataset in SQL Server?
I am building a parts inventory system, and have a transaction table that stores how often an item in used.
Let's say Item A is used 10 times in 2021, 20 in 2022, and 30 in 2023. Of course each ...
- 23
1
vote
2
answers
67
views
Query to return latest record between 2 tables
I'm trying to pull the most current date (cusPurDt) and corresponding amount (subAmt) by customer (cusID/subID). Here is some sample data:
create table customer (
cusID char(5),
cusPurDt ...
- 75
2
votes
1
answer
64
views
Query for single return meeting multiple criteria and values
I'm trying to figure out a way to count an ID once when multiple criteria are met in one column while displaying a total of the same criteria in another column. The criteria for the first column can ...
- 75
1
vote
1
answer
20
views
In MS Studio how to SELECT from an existing SQL file
I have a complicated query that select parts from an MRP table using several different criteria. The output of the query is a list of parts which includes the key field [PartNum]. I have saved this ...
- 11
0
votes
1
answer
57
views
Efficient PostgreSQL query syntax for duplicating the result
Assuming I have the following PostgreSQL query:
SELECT (
SELECT SUM (t.customers)
FILTER (
WHERE id = 1
AND date
BETWEEN %(start)s AND %(stop)s
...
- 133
1
vote
0
answers
25
views
query customer retention over range
I am trying to find the best way to accomplish the following.
Get the beginning customer count, which carries from the previous day
Get New Customer count
Get the number of Customers who have not ...
- 111
1
vote
1
answer
162
views
Combining Multiple queries from the same table inrto 1 query
I am looking to find a way to achieve the below...
I have tried using Joins and SubQueries but no joy as yet...
I have a table that keeps track of wins/loses/draws as so
Date
WLD
01/01/23
Win
05/01/...
- 43
0
votes
1
answer
436
views
Join result of crosstab to main query
I'm trying to get this column structure:
| id | name | main_image | rating | shop_names | prices | urls | 2 | 3 |
My table structure looks like this
CREATE TABLE products (
id SERIAL PRIMARY KEY,
...
0
votes
2
answers
57
views
I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments
I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both
This ...
2
votes
2
answers
96
views
DB2 query not working
Customer (parent table)
Id
Charge Amt
C1
10
C2
20
C3
30
C4
40
Payment (child table)
Id
pymt amt
C1
10
C2
5
C4
40
Select Id and pending balance from customer and payment tables
Pending ...
- 21
1
vote
1
answer
162
views
DIV and GROUP by issues
I have one query, made it in MySQL.
And now try to run on PostgreSQL, but get syntax error twice.
Once on the DIV command, and then on the GROUP BY.
Here is my code:
UPDATE status_table
SET Status_CG ...
0
votes
1
answer
891
views
Why cannot I reference the second alias from a subquery containing a UNION?
Why cannot I use Qty2 as an alias in my query? I receive this error:
Error Code: 1054. Unknown column 'qty2' in 'field list'
SELECT mat, qty, qty2, SUM(qty), (ifnull(SUM(qty - qty2),0)) as TotalQty
...
0
votes
0
answers
363
views
MySQL query: multiple select with different conditions and LIMIT for each, but also LIMIT whole query + paginator friendly
I would like to write SQL query (for MySQL) that is able to SELECT
x rows by condition1 from table1
y rows by condition2 from table1+join1
z rows by condition3 from table1+join2
But on every page (...
- 1
0
votes
1
answer
480
views
MySQL query: Detect different rows between two tables and show differences in result table
I have two tables in a MySQL database.
Table1
a_id
1
3
4
5
6
Table2
b_id
1
2
3
I would like to spot the missing row from Table1 a_id 2, and missing rows from Table2 b_id 4,5,6, which should be shown ...
- 157
1
vote
0
answers
457
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
3
votes
2
answers
2k
views
How to query through foreign keys while only having the value in the the same row as the value used as foreign key in another column
db admins, I require your help!
My experience working with databases directly using SQL is rather limited, since I've mostly been using ORMs to cover all my needs as a frontend developer, but this ...
- 133
0
votes
1
answer
58
views
How to get the highest (max) cost, lowest (min) cost, and the date that each occurred?
I need to find the highest cost & lowest cost for each part in a table (There are many parts) in a given date range. And output the date that each cost was recorded.
Example Data:
CREATE TABLE ...
- 13
0
votes
0
answers
41
views
Finding duplicate records in a subset
I have 3 tables:
users
orders
order_items
I am trying to get a list of all orders by the same user that have the exact same order_items.item_id within a 24 hour period representing the user having ...
- 399
6
votes
1
answer
780
views
Unused columns in WHERE clause
I found this query in our codebase:
DELETE FROM "Foo"
WHERE ("Foo"."Id", "Foo"."CreatedAt")
IN (SELECT "f"."Id", "f".&...
- 205
0
votes
1
answer
41
views
What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?
ERD for my simple db design:
I want to order children based on their parent's last name. I am trying to use the following query:
SELECT *
FROM children
WHERE parent_id IN (SELECT parent_id
...
- 127
-1
votes
1
answer
4k
views
SQL query to join on maximum value on a per-row basis
I'm stuck writing a query for the following scenario: I have a table of things, they have a metric called number in the examples below. I have another table called offers which is a list of offers ...
0
votes
1
answer
687
views
how to use distinct on to avoid aggregating column in this query?
what I am trying to do is I want to calculate the quantity for the inventory the formula for that is receive_order_entries.quantity+ the sum of all inventory_transactions.quantity for that inventory
...
- 13
0
votes
1
answer
424
views
Create a select query to get employee name, total salary of employee, hobby name(comma-separated - you need to use subquery for hobby name)
see the below query table.
Create an “employee” database and 4 tables (hobby, employee, employee_salary, employee_hobby).
hobby: id, name
employee: id, first_name, last_name, age, mobile_number, ...
0
votes
1
answer
798
views
SQL Insert by subquery inside update query [closed]
I want to Write a single query that updates columns in one table and insert rows in another table.
something like this
update
applications
set
applications....
- 13
0
votes
2
answers
857
views
MySQL multiple subqueries - slow ordering
Any idea how to optimise this query?
It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds.
Problem is, that I need to been ...
0
votes
1
answer
663
views
Group by bit column
I want Group by rows with active column like result
I tray with this code and I Do not get result
WITH CTE as (
SELECT
RN = ROW_NUMBER() OVER (ORDER BY id),
*
FROM test
)
SELECT
...
1
vote
1
answer
2k
views
subquery must return onl one column in postgresql
I want to get the XML response after the stored procedure call in PostgreSQL. In the below query I was trying to get the output, but it throws an Error: subquery must return only one column. Can ...
4
votes
2
answers
2k
views
Select rows with the highest value for one ID, after grouping by two IDs
It is given in the problem that occurrences has to happen more than once, but the person (PID) should only be selected with the (agent) AID having the highest BustCount.
SELECT I.AgentID, I.PersonID, ...
0
votes
2
answers
1k
views
MariaDB: Insert records (list of tuples) into table if not exists
Currently I have several sql queries that look like this:
INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
SELECT * FROM `test`
WHERE col1='1' AND col2='...
0
votes
1
answer
25
views
Having problems joining table
I have the following tables and I want to know Jack's dob, salary and the total score of that employee who is also a player.
The relational schemas are:
Emp_Players (eid,pid,team) composite key is (...
- 45
0
votes
1
answer
1k
views
Debit Credit Balance calculating from previous records with Pagination MYSQL
I have table with the following schema and demo data-
id date particulars debit credit
104 29-12-2020 Crediting NULL 1000
105 29-12-2020 Purchased Item:A ...
- 3
-3
votes
1
answer
297
views
Change Consecutive Row Value [closed]
I am trying to change value of Consecutive AA to first AA as AA1 and second one as AA2, if only one AA then it should be as AA1, its possible to change all AA to AA1 and then search for 2nd ...
1
vote
1
answer
103
views
Query to find the "Number of days" a customer has an active card between a specified interval
I have a table with below structure :
create table test_table
(
customer_num NUMBER,
card_number char(2),
card_issue_date DATE,
card_expire_date DATE
)
Example data is:
...
- 1,486
0
votes
2
answers
937
views
How to retrieve missing values of an id from a list?
have a data structure that is value/tag inside a table on Oracle sql
Lets say I have this structore
exTable
ID
TAG
VALUE
1
1
x
1
4
y
2
2
x
2
3
y
2
4
z
I want to retrieve the missing tags for a ...
0
votes
3
answers
71
views
Query to fine "Order_num" that has no two equal "product_name"
I have a table with below structure:
create table A_TEST
(
order_num int,
product_name varchar(50),
product_amount int,
product_price int
);
Sample data :
insert into a_test (...
- 1,486
8
votes
1
answer
6k
views
Why is SQL Server spliting it's (JSON) response into multiple rows?
I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that ...
- 311
0
votes
1
answer
74
views
How to delete Specific row
http://sqlfiddle.com/#!9/df6245
How to delete all rows Where brand not equal to 107
for example in above fiddle GRPCD 44111 have 107 so it should not delete both rows which have GRPCD 44111 but it ...
- 23
2
votes
1
answer
47
views
SQL daily value (today value - yesterday value)
I have a database where there are sales .
+----+-----------+------------+
| ID | Quantity | DATE |
+----+-----------+------------+
| 1 | 40 | 2020/10/10 |
| 2 | 0 | 2020/10/01 |...
- 23
-1
votes
1
answer
784
views
MYSQL Error: Incorrect usage of UNION and ORDER BY?
Am trying to randomize my first query but ran into an error "Incorrect usage of UNION and ORDER BY" Kindly find below my queries:
SELECT *
FROM(
SELECT deal_page.id,
...
- 19
0
votes
1
answer
27
views
Return average using same table data
I've got this query I'm testing with:
select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average
from ...
- 141
0
votes
1
answer
17
views
Query finds no values when sharppend
I am trying to join two datasets and find the most recent certification date
I the following 2 datasets
Certifications
(1, 'Judge Level 1', 'Judge', 10, 2),
(2, 'Judge Level 2', 'Judge', 20, 2),
(3, '...
- 103
1
vote
0
answers
20
views
Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition?
(I'm asking this because I was reviewing this older question I asked and because I'm currently writing some queries and getting annoyed at how much I need to repeat myself).
In ISO-compliant SQL ...
- 603
0
votes
2
answers
10k
views
Selecting distinct rows from two tables, using multiple conditions
I'm trying to figure out a query. I have an offices and meetings table in my DB. Offices can have multiple meetings (office ID is a FK to the offices table). Some meetings allow lunch, some do not. ...
- 113
1
vote
1
answer
995
views
T-SQL query - how to AVG() the SUM() of columns' rows to a new row column?
What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.
How should the query look like to achieve this, since the AVG function is an ...
- 151
2
votes
1
answer
261
views
How to fetch related rows on multiple condition with ranking
I have following table for storing item information in PostgreSQL.
id | name | category | seller | m_age | status | type_id | region |
----------------------------------------------------------...
- 21
2
votes
1
answer
5k
views
Sql Server string concat with order by and subquery
While working on some dynamic sql production code we encountered code that's concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a ...
0
votes
1
answer
642
views
Select rows from table having more than two specific values
I have a table having details such that
Userpin product Time1 contains
4556 23 7:30 A
4557 23 7:30 B
4558 23 7:30 C
...
0
votes
1
answer
263
views
Select rows from table having specific values depending on another values in the same table
I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore 7 and 8 example:
Userpin TrDate Time1 Tr_type
4555 12-02-2020 ...
- 3
0
votes
1
answer
635
views
How to show multiple record if has top 1
Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-...
- 29