Questions tagged [where]
The `WHERE` clause is used to specify criterion for retrieval or modification of rows from objects in a database.
161
questions
50
votes
2
answers
120k
views
selecting where two columns are in a set
This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following:
SELECT whatever WHERE col1,col2 IN ((...
- 625
39
votes
3
answers
47k
views
Using column alias in a WHERE clause doesn't work
Given a table users with two fields: id and email.
select id, email as electronic_mail
from (
select id, email
from users
) t
where electronic_mail = ''
Postgres complains that:
...
- 527
8
votes
3
answers
483
views
Locate the smallest missing element based on a specific formula
I need to be able to locate a missing element from a table with tens-of-millions of rows, and has a primary key of a BINARY(64) column (which is the input value to calculate from). These values are ...
- 1,503
8
votes
3
answers
1k
views
Updating a WHERE clause to check if a value is NOT in a separate table
I have a query which uses a WHERE clause, and I happen to use the exact same WHERE clause in many queries on this table (et al).
The query is:
SELECT
DATENAME(DW, [AtDateTime]) AS [Day of Week]
...
- 1,503
7
votes
4
answers
27k
views
Building Dynamic Oracle Where Clause
I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored ...
- 839
7
votes
1
answer
15k
views
How do I query PostgreSQL enums like in MySQL?
With the following table in MySQL:
CREATE TABLE bob(foo ENUM('a','b','c'));
INSERT INTO bob (foo) VALUES ('a'),('b'),('c'),('a'),('a');
SELECT * FROM bob WHERE foo >= 2;
+------+
| foo |
+------...
- 265
6
votes
1
answer
3k
views
single query using OR operator vs multiple queries using equals operator
Case 1 - Single query using OR operator in where clause:
select * from users where name='smith' or nick='smith' (using index_merge )
Case 2 - multiple queries using equals operator in where clause:
...
6
votes
2
answers
45k
views
Search multiple values in multiple columns
I have a table results with this fields:
id
results_id
number_one (int)
number_two (int)
number_three (int)
number_four (int)
number_five (int)
Each number_* column can contain a number from 01 to ...
- 163
5
votes
2
answers
591
views
SQL Return Table with where clause and occurrences of value of total table
I am stumped trying to figure out how to achieve this. I know how I would achieve it in C#, but not SQL.
Say I have the following table:
ID
Name
RouteId
1
Bob
1001
2
Bob
1002
3
Ana
1001
4
Jim
...
- 173
5
votes
2
answers
311
views
More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows
I have some queries where I need to get more than 64 specific rows, like this example with 65 IDs.
TableID is primary key, type BigInt.
SELECT * FROM TableA
WHERE TableID IN (260905384, ...
- 51
5
votes
1
answer
3k
views
Condition in WHERE vs condition in CONNECT BY
Can anyone explain to me the difference between the following two queries? Even though they seem the same the results are different.
select concept.concept_id, concept.PARENT_ID
from ...
4
votes
2
answers
11k
views
Count with where clause
Is there a way to have WHERE clause in COUNT ? Or how to count with some clause ?
select
a
b,
count(c where t1.u='UAE') as c1
from t1
I am using MS SQL Server 2014
- 454
4
votes
2
answers
5k
views
Perform TABLESAMPLE with WHERE clause in PostgreSQL
I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL.
This runs fine:
select * from customers tablesample bernoulli (1);
But I cannot figure out how ...
- 149
4
votes
2
answers
114
views
Select encompassing rows
I have a ROAD table that has a row for each vertex of each line:
+---------+--------------+-------------+
| ROAD_ID | VERTEX_INDEX | MEASURE_VAL |
+---------+--------------+-------------+
| 1 | ...
- 1,427
4
votes
3
answers
639
views
Omit max entry from each group in postgres?
I have a query which produces data similar to the following:
a_key | a_timestamp | a_value
---------------------------------
345223452 | 2016-03-07 | 1
029370982 | 2017-01-12 | 1
984656834 | ...
- 578
4
votes
1
answer
185
views
Concurrent update on two different tables fail
I'm using PostgreSQL with the following database schema:
CREATE TABLE plans (
slug VARCHAR(500) PRIMARY KEY
);
CREATE TABLE users (
id VARCHAR(16) PRIMARY KEY,
org_id VARCHAR(16) NOT NULL
...
4
votes
2
answers
8k
views
Use of ANY function for multiple values
I am trying to use the ANY function of PostgreSQL to search the value from array interger type column.
My SQL:
SELECT
*
FROM
company_employee_contacts
WHERE
corporate_complaint_type_ids ...
- 185
4
votes
3
answers
9k
views
Search across all columns in a table
As the title says, I want to create a query that searches a keyword across all columns in that table.
So I want to simplify this query:
SELECT * FROM tblCustomers
WHERE (col1 LIKE '%keyword%' OR ...
- 151
3
votes
4
answers
3k
views
Building where clause based upon parameters
In PostgreSQL I am trying to build a where clause in a function that uses an inbound parameter to determine the contents of the IN
For example:
select fld1, count(fld1)
from xyz
where fld1 in (
...
- 33
3
votes
1
answer
1k
views
Does using inequality (<>) or NOT in where clause lead to SQL ignoring the index?
I found this article that talks about avoiding using <> operator in where clause because optimizer ignores the index.
https://www.mssqltips.com/sqlservertutorial/3203/avoid-using-not-equal-in-...
- 41
3
votes
3
answers
7k
views
Does MySQL run WHERE clause before ORDER BY?
I have this query:
SELECT my_code
FROM ranges
WHERE 123456789 BETWEEN first_number AND last_number
ORDER BY type_of_code ASC LIMIT 1 ;
My question is, will it run the ORDER BY after it has ...
- 133
3
votes
1
answer
2k
views
Use specific array column in the where clause
Having a function that returns an array and in a query we use the function to return results such as:
{"TUNDUMA GOING (BORDER)","ARRIVAL DATE",09/01/2016,22,"AT CHECKPOINT"}
{"TUNDUMA RETURN (BORDER)"...
- 1,940
3
votes
3
answers
2k
views
In Postgres, is it possible to get all rows from a table where a column can be cast to a different type?
I have a table in a Postgres DB that has a TEXT column, named data, that I use to store both IP addresses and domain names. I'm wondering if it's possible to select all the rows in the table whose ...
- 397
3
votes
1
answer
191
views
Do WHERE IN queries reindex the table after each set within the query?
I have a basic WHERE IN query that takes too long (30+ secs) to process.
DELETE FROM `table1`
WHERE ( djID, localID )
IN (("128","26"),("110","32"))
The table I'm querying on has about 20 million ...
- 155
3
votes
2
answers
19k
views
Multiple WHERE conditions on same column =val1 but doesnt=val2 or val3
I am trying to filter a table which has NON-UNIQUE transaction id's, joined to a products table on 1 common column. The filter needs to be as follows:
equal to :group1 AND
NOT equal to :group2 AND
...
- 304
3
votes
1
answer
54
views
mysql query only works when value is compared with primary key [closed]
I'm working with this table:
mysql> describe dispositivos;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------...
3
votes
1
answer
182
views
How to fetch data that is not similar
I have this table:
| id | user_id | status |
| a | 1 | p |
| a | 1 | p |
| a | 1 | np |
| a | 1 | pp |
| b | 2 | np |
| b | 2 | np |...
- 131
3
votes
2
answers
10k
views
Different where clause based on SSRS parameter
I have an SSRS report where a parameter can take 3 values. The select query is the same in all cases but the where clause needs to check different date fields based on the parameter value. Is this ...
3
votes
2
answers
17k
views
Updating a table from a temporary table where clause error in MySql Community
I am trying to update an existing table named "residential_summary" from a temporary table called "a". I can query the temporary table a but when I try to use it in an update statement I get
Error ...
3
votes
2
answers
50
views
Optimizing PostgreSQL where clause with OR
I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate ...
- 349
3
votes
1
answer
226
views
How to select columns in SQL server 2008 using WHERE and IN, having 2 columns in the where clause [duplicate]
How to select columns in SQL Server 2008 using WHERE and IN, having 2 columns in the where clause.
Here is what I have, however I am getting some error message.
select * from tbl
where (col1, col2) ...
- 31
2
votes
3
answers
9k
views
How do I use the result of a CASE statement in a WHERE clause?
When I try to run this SELECT statement, I get this error: ORA-00904: "GPASCORE": invalid identifier. How do I use the result of the gpaScore CASE statement in the WHERE clause?
SELECT
(CASE grade
...
- 123
2
votes
2
answers
60
views
Where Clause for Results Appearing after Occurence
I'm wondering if it's possible to find records that only appear after the first occurrence of a where clause. I'm currently using PostgreSQL for this example but something not PostgreSQL specific ...
- 207
2
votes
1
answer
2k
views
Using DATE part of datetime column in WHERE clause
I'm building a POS system, where I collect the date & time that the event took place. I would like to pull some reports by only using the DATE part of the datetime column in my SQL queries.
My ...
- 325
2
votes
2
answers
1k
views
Why does a MySQL 'where' clause with Index make a SUM query slower?
I have a table with 5 million records. Table columns are id, customer_id, status, and amount. When I run a query without Index(status),
SELECT SUM(amount) FROM `game_results` WHERE `status` = 1
the ...
- 23
2
votes
2
answers
640
views
In what direction SQL Server WHERE clause conditions are checked?
I have this query:
SELECT RCodeID as 'ListItemID',
RCode as 'ListItemName'
FROM RCode
WHERE RCode not like '%,%'
AND ISNUMERIC(RCode) = 1
AND CAST(RCode as int) >= CAST(@...
- 123
2
votes
2
answers
10k
views
Complex joins in multiple subqueries
Sorry to keep bugging you guys, but SQL is still new to me. I have these tables:
Store_location, Product, Sizes, Sells, Available_in, Offers, and Currency.
The goal is to be able to run one query with ...
- 75
2
votes
5
answers
11k
views
IN clause too slow
Ive been searching for about 1 hour and i have not found anything.
I have this problem:
When I do this query:
select order_id
from or_order@smartflex
where order_id in ( select distinct numeroot ...
- 21
2
votes
3
answers
55
views
Counting NULLs as zeroes and filtering with the WHERE statement
I have a SQL puzzle:
How can we get a list of customers who have purchased 3 or fewer socks (including those who haven't purchased any socks) as well how many socks those customers have purchased?
I ...
- 133
2
votes
2
answers
3k
views
SQL Server -- More efficient alternatives to repeating an (almost) identical subquery
I need to improve the efficiency of multiple subqueries within a WHERE clause. The context is a report generator on an existing product. The only input I can provide is the WHERE clause. I have 3 ...
- 203
2
votes
1
answer
95
views
Check the result of a math expression in mysql
Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here.
a b oper c
-------------
2 3 + 5
4 2 / 3
...
- 123
2
votes
1
answer
145
views
where clause after limiting results
I have a question about getting results after limiting the rows.
I don't have much experience with MySQL, but I'm trying to get results from a database where I need to do the where clause after ...
- 51
2
votes
2
answers
102
views
Select roads that are between FROM and TO
I have a road table:
create table road
(road_id int,
name_ nvarchar2(15),
from_ nvarchar2(15),
to_ nvarchar2(15),
sort int
);
insert into road (road_id,name_,from_,to_,sort)...
- 1,427
2
votes
2
answers
338
views
Query with WHERE NOT and NOT IN with subquery
MySQL Version: 5.6.27
I am using the following two tables
TABLE: oc_product
product_id int(11)
model varchar(64)
tax_class_id int(11)
and
TABLE: oc_product_to_category
product_id ...
- 133
2
votes
1
answer
4k
views
How to check other table for value during insert
In PostgreSQL is it possible to check another table's data to see if there is data that fits a certain condition during an INSERT statement?
I have attempted to use WHERE NOT EXISTS as I want to not ...
- 123
2
votes
1
answer
591
views
Matching multiple rows in a MySQL database
A custom built eCommerce store has the following two tables:
orders
| order_id | user_id | amount | shipping_info | date_time |
|----------|---------|--------|---------------|--------------...
- 21
2
votes
1
answer
135
views
Why is my query not using "Missing index tip"? ( SQL Server 2008 R2 )
I know there's a lot of questions like this one, but I think this is different, and none of them could help me.
When I run a query with execution plan, I get this:
But, even with the index created, ...
- 7,284
2
votes
1
answer
198
views
Why does MySQL optimize query with "WHERE ... OR" so badly?
Hey there,
this is my first question so please be patient if I miss to add something in here.
My question is why the OR in the WHERE clause causes the query to be executed the way it is (which is ...
2
votes
2
answers
110
views
SQL column=ALL(query): Equivalent meaning
I know that the expression:
WHERE column <> ALL(subquery)
could have been written as:
WHERE column NOT IN(subquery)
What would be the equivalent for
WHERE column = ALL(subquery)
I don’t ...
- 2,579
2
votes
1
answer
676
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