Questions tagged [where]
The `WHERE` clause is used to specify criterion for retrieval or modification of rows from objects in a database.
26
questions with no upvoted or accepted answers
2
votes
1
answer
757
views
Filter by list of values pairs
I have Postgresql 9.4 database and products table. For each product there must be list of pairs:
price; quantity for which this price is acutal
Products table may contain millions and billions of ...
- 21
2
votes
1
answer
2k
views
How do Locks Work in MySQL or MariaDB
There is a case which makes me a bit confused. As far as I understand deadlocks should appear if there are two query statements (including subqueries) in request (each transaction). So the ordering of ...
- 121
1
vote
0
answers
62
views
unique key on varchar is ignored for maria db
I have the following table:
| s3entry | CREATE TABLE `s3entry` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`S3KEY` varchar(1024) DEFAULT NULL,
`FETCHESSINCELASTS3FETCH` int(11) DEFAULT NULL,
`...
- 111
1
vote
0
answers
200
views
PostgreSQL - Dynamic filter expression
Given a simple function (SQL for the sake of example, can be plpgsql)
CREATE OR REPLACE FUNCTION dynamic_filter(
f INT[] DEFAULT NULL
) RETURN INT[] LANGUAGE SQL AS
$$
SELECT ARRAY_AGG(val)
...
- 183
1
vote
2
answers
50
views
Under what circumstances would these 3 queries return different number of results?
Hello I have the following question:
Query 1:
SELECT b.id,
(
SELECT SUM(cb.invoice_amount) AS sum
FROM payment_lines cb
WHERE cb.batch_id = b.id
) AS batch_total_invoice_amount
...
- 19
1
vote
1
answer
121
views
How to order by then start at first non null value and select a limit for number of values
I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to ...
- 11
0
votes
1
answer
57
views
Group by a column and select the group id on condition that applies to all the rows in it
I have the below table
CREATE TABLE result(
id INTEGER NOT NULL
,cid INTEGER NOT NULL
,aid INTEGER NOT NULL
,agid INTEGER NOT NULL
,ft INTEGER NOT NULL
,ct INTEGER ...
- 197
0
votes
2
answers
111
views
Selecting last value to be entered each month
I am looking to pull a closing balance from the database for each month.
I have tried
SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', DateTime
FROM Table1
WHERE MAX(DateTime)
Group By ...
- 43
0
votes
1
answer
81
views
Can I make a parameterized equality based condition in the where clause of a SQL statement always true
I have a series of queries which depend on a where clause which looks like the following:
where a = <a>
and b = <b>
and c = <c>
and d = <d>
The angular brackets ...
- 101
0
votes
1
answer
74
views
Sum of Total per Client
I want to know the total of each product that each customer has bought and I don't know how to add the total per customer.
The tables are as follows:
CREATE TABLE CLIENTES -- Customers
(
CODIGO ...
0
votes
0
answers
61
views
How does MySQL handle indexes and where clauses with joins
I am querying large tables with joins and where conditions.
An example query:
SELECT *
FROM forename
JOIN forenameLength ON forenameLength.nameRef = forename.nameRef
WHERE forename.placeRef = 18000 ...
- 155
0
votes
1
answer
69
views
Inconsistent ResultSet from Procedure with Variables
I'm working on building a procedure with two inputs, and the Syntax checks out, but the result-set is not consistent with the logic.
In this question, there are in actual fact two questions. Number 1 ...
- 111
0
votes
0
answers
100
views
Speed up query on large MySQL table
SELECT property, geo, stars
FROM table
WHERE SUBSTRING_INDEX(geo, '>', 3) LIKE '%$mylocation%'
...loop to show results
geo column has values like - Spain>Canary Islands>Lanzarote>Some ...
0
votes
2
answers
565
views
How to select Overdue Rows with Date Frequencies?
+------------------------+--------+
| Invoice_id | due_date | amount |
+-------------+----------+--------+
| 20 |2020-01-18| 1250 |
+-------------+----------+--------+
| 21 |2020-...
- 101
0
votes
1
answer
38
views
Time interval is returning all records or no records
I am trying to run a query to return records in the last 24 hours and following the instructions here: time interval tutorial
After running the query at 18:30 UTC, my query looks as follows:
RETURN ...
- 179
0
votes
0
answers
36
views
Sql, get data/rows for more selected ids in one condition, from file
I am using PostgreSQL 11.5 on Ubuntu
Question
How can I get data/rows for more selected ids in one where condition? Ideally from file with list of ids.
Example
|product_id |product_name ...
- 191
0
votes
0
answers
35
views
How to get the data using where_in (1,3 ) table in mysql?
We have 2 Tables
Table 1 is User
Table 1:
id | name | sub_code
-----------------------------
1 | name1 | 1
2 | name2 | 1,3
3 | name3 | 2,4
Table 2 ...
- 109
0
votes
0
answers
95
views
How performant is finding a substring in database?
To route requests on my website, I need to query the database to see if the first part of a URL matches a category for an item.
For example:
I have a request to /animals/dog/darktheme/. The ...
- 111
0
votes
2
answers
86
views
How space is compared in where condition
I've come across below query where it is column being compared the space character.
SELECT *
FROM company
WHERE emp_name > ' ' -- space char
AND emp_id = 123
AND active_flag = 'Y';
...
- 193
0
votes
0
answers
28
views
Get Schedule for the current day or first coming up
I am building a scheduling app, where I assign schedules to certain members but those schedules can change at a certain date or be assigned for the future.
My database structure is like so:
members ...
- 133
0
votes
2
answers
2k
views
How to speed up counting and filtering on table with a lot of columns and filter conditions?
I have TABLE_A in my database that has a lot of columns.
Let's say there are 100 columns: COLUMN_1, COLUMN_2, COLUMN_3, .. COLUMN_100
There are so many columns not because of denormalized design, ...
- 101
-1
votes
1
answer
2k
views
Need to filter by month
I am using SQL Server Management Studio.
I am trying to filter by month using dtm DATETIME as my format XX/XX/XXXX. I am very new to SQL, so I was trying to just filter by the month of January 01 with ...
- 11
-1
votes
2
answers
42
views
UPDATE with too much values at IN state takes too long
I have the next query:
UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND branch_id IN
(23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,...
-1
votes
1
answer
37
views
Select MySQL with 2 tables conditions, Is this right?
Tables:
agencia (nome_agencia, cidade_agencia, fundos)
cliente (nome_cliente, rua_cliente, cidade_cliente)
conta (nome_agencia, numero_conta, saldo)
emprestimo (nome_agencia, numero_emprestimo, ...
- 1
-2
votes
1
answer
22
views
Can't manage to work with comparing days
In a table with persons and their birthdays, the ones whose birthday is in less than 7 days need to be displayed.
I found some answers on the Internet but they all are at least 5 years ago with ...
- 101
-3
votes
1
answer
136
views
Poor query performance depending on the placement of `where` conditions (MySQL)
There is a query, in which changing the ordering of where conditions affects its performance drastically (5k vs 700k rows_examined).
The weird thing is that both versions of the query have the exact ...
- 1