Questions tagged [where]

The `WHERE` clause is used to specify criterion for retrieval or modification of rows from objects in a database.

Filter by
Sorted by
Tagged with
53 votes
2 answers
132k 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 ((...
James's user avatar
  • 655
43 votes
4 answers
61k 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: ...
Victor's user avatar
  • 567
8 votes
3 answers
489 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 ...
Der Kommissar's user avatar
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] ...
Der Kommissar's user avatar
7 votes
2 answers
6k 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 ...
Reveille's user avatar
  • 179
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 ...
Vladimir Oselsky's user avatar
7 votes
1 answer
18k 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 | +------...
user3112092's user avatar
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: ...
dreamer61531012's user avatar
6 votes
2 answers
47k 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 ...
Tyler Durden's user avatar
5 votes
2 answers
809 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 ...
EyeSeeSharp's user avatar
5 votes
2 answers
504 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, ...
Ruben's user avatar
  • 51
5 votes
1 answer
4k 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 ...
giannis christofakis's user avatar
4 votes
2 answers
12k 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
Darko Milic's user avatar
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 | ...
User1974's user avatar
  • 1,457
4 votes
3 answers
928 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 | ...
Wildcard's user avatar
  • 588
4 votes
1 answer
380 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 ...
Valentin Nasraty's user avatar
4 votes
2 answers
10k 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 ...
Harshal Mahajan's user avatar
4 votes
3 answers
10k 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 ...
user1009013's user avatar
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 ( ...
user110735's user avatar
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-...
tsvaishnav's user avatar
3 votes
3 answers
8k 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 ...
Michael Nielsen's user avatar
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)"...
Fabrizio Mazzoni's user avatar
3 votes
3 answers
3k 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 ...
Bryan's user avatar
  • 417
3 votes
1 answer
195 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 ...
Shaun's user avatar
  • 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 ...
Adam Copley's user avatar
3 votes
1 answer
55 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 | +-----------+-------...
Cesar Alejandro Villegas Yepez's user avatar
3 votes
1 answer
184 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 |...
user3056158's user avatar
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 ...
Confused by Query's user avatar
3 votes
2 answers
18k 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 ...
Dustin Knight's user avatar
3 votes
2 answers
60 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 ...
mdahlman's user avatar
  • 347
3 votes
1 answer
248 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 ...
Kevin Neuenfeldt's user avatar
3 votes
1 answer
240 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) ...
Harisudhan's user avatar
2 votes
3 answers
10k 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 ...
Nathan Jones's user avatar
2 votes
2 answers
61 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 ...
TheLovelySausage's user avatar
2 votes
1 answer
3k 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 ...
Manny265's user avatar
  • 325
2 votes
2 answers
2k 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 ...
PanelMaker's user avatar
2 votes
2 answers
897 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(@...
Aishwarya Shiva's user avatar
2 votes
2 answers
11k 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 ...
user121168's user avatar
2 votes
5 answers
13k 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 ...
user3126458's user avatar
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 ...
verkter's user avatar
  • 133
2 votes
2 answers
4k 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 ...
mnemotronic's user avatar
2 votes
1 answer
54 views

How to use aggregate column in where clause when using group by

I've following table CREATE TABLE `books` ( `book_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) DEFAULT NULL, `author_fname` varchar(100) DEFAULT NULL, `author_lname` varchar(100) ...
Md. A. Apu's user avatar
2 votes
1 answer
139 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 ...
vrintle's user avatar
  • 123
2 votes
1 answer
151 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 ...
Wesley's user avatar
  • 51
2 votes
1 answer
3k views

MySQL / MariaDB : Many indexes vs. One multi-column index

I need to optimize my MariaDB tables index. I've read a lot of very interesting things on StackExchange, but I'm not sure to have the answer for my case. In my case, I have a table named TableA which ...
Joffrey's user avatar
  • 35
2 votes
2 answers
103 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)...
User1974's user avatar
  • 1,457
2 votes
2 answers
381 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 ...
kanenas's user avatar
  • 133
2 votes
1 answer
5k 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 ...
Andre Yonadam's user avatar
2 votes
1 answer
598 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 | |----------|---------|--------|---------------|--------------...
confusedab's user avatar
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, ...
Racer SQL's user avatar
  • 7,386