As of May 31, 2023, we have updated our Code of Conduct.

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
0 votes
1 answer
31 views

How to query in the given order

select * from table where options=(10,223,43,1,23,54,323,32) But the result is not coming in the given order of options. Options need to be changed frequently.
sh3hz's user avatar
  • 1
0 votes
2 answers
41 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 ...
Paulmcf1987's user avatar
0 votes
1 answer
351 views

How do I query only rows where multiple instances with the same ID exist with Postgres?

I have a table where I want to gather some statistics, but only for items that are repeated two or more times. To simply, here is how the table looks like: CREATE TABLE test ( id bigint, ...
Alexis Wilke's user avatar
0 votes
1 answer
182 views

Which database scheme will be better for dating app performance?

I'm creating dating app like Tinder with Spring Boot and want to make sure that even with huge amount of data it will be working with best possible performance. My tables looks like above. All tables ...
Kamil Zdr's user avatar
-3 votes
1 answer
55 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 ...
Mazen's user avatar
  • 1
4 votes
1 answer
182 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
0 votes
1 answer
59 views

mySQL INNER JOIN and Conditions

I wrote a mySQL query that is just works as I need. SELECT COUNT(wp_posts.ID) as count, wp_terms.term_id FROM wp_terms INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id AND ...
techn9ne's user avatar
5 votes
2 answers
586 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
0 votes
1 answer
34 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 ...
Dargscisyhp's user avatar
0 votes
1 answer
266 views

Why my simple searching query is too slow?

I don't know why searching in my "cities" table is so slow. My query looking for a table "cities" located about 25km from the city. I use this simple query and the database takes ...
Sahasrar's user avatar
0 votes
2 answers
232 views

Filter by field, and if zero results then filter by other field

How should a WHERE clause be constructed, such that: It filters by a certain field If there are zero results for filtering by the aforementioned field, then it filters by another field? For example, ...
HeyJude's user avatar
  • 425
0 votes
1 answer
59 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 ...
Jose Luis Bravo Gonzalez's user avatar
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 ...
PanelMaker's user avatar
0 votes
1 answer
34 views

Return all archive records when any one record meets criteria

I want to get all archive records related a transfer ID where at least one archive record has a date in the report month (in this case September). The Archive Table SELECT PTA.PlnndTrnsfrID, PTA....
Kim's user avatar
  • 11
1 vote
2 answers
404 views

SQL having clause

I was studying about the having clause when I came across this website. The last two lines of this article have made me puzzled. Similar to the WHERE clause, the HAVING clause requires that the ...
Sudhir Sharma's user avatar
-2 votes
1 answer
127 views

Having trouble with where subquery count

Assume all of the table and field names are correct. I've tried this a few different ways but this is the only that makes sense needing the join in both. This is the question: SQL query to get the ...
BFrank117's user avatar
0 votes
2 answers
97 views

What are the more important columns to include in NONCLUSTERED INDEX - from Group by or Where?

I am thinking about adding a NONCLUSTERED INDEX to a table in SQL Server 2016. I found that most queries refers to WHERE clause like: WHERE varcharColumn <> '' and varcharColumn2 = '2' and ...
grochu's user avatar
  • 45
-4 votes
1 answer
57 views

How to join two tables in sql server? [closed]

I need some help i have a query, struggling to use join and database is sql server. Below is my table definition. // sql server query SELECT TOP 1000 [Station Code] ,[Transaction Code] ...
Gcobza's user avatar
  • 95
0 votes
1 answer
282 views

Given a table with date_time column, how to run a query using only the date

I have a DB with a table which has a column datetime Date_Time. The format of this is for example: 2021-09-01 13:15:16. I would like to set a query that is indipendent of the time but just consider ...
Dark2018's user avatar
  • 101
0 votes
2 answers
845 views

Is it possible to nest CASE-statement into WHERE clause?

I am developing an application with FastAPI. It contains a function that brings two variables into a SQL query using the sqlalchemy.sql.text as shown here. SELECT ATTR3 FROM TABLE WHERE ATTR1 = ...
Taras's user avatar
  • 177
0 votes
1 answer
283 views

Adding new user info to current old user accounts

Hey all so I have a database that has been up and running for a few months now. I had a request to add the users First and Last name as well as their organization. This was easy enough to do for NEW ...
StealthRT's user avatar
  • 115
0 votes
0 answers
51 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 ...
Kohjah Breese's user avatar
0 votes
2 answers
58 views

Complex `where` dramatically affects query performance

There are much more complex queries running on these tables that are much faster but this one takes 90+ seconds to run. What am I doing wrong here? How can this be improved? SELECT r.id ...
I wrestled a bear once.'s user avatar
1 vote
0 answers
140 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) ...
geozelot's user avatar
  • 183
1 vote
2 answers
49 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 ...
Georgieva's user avatar
0 votes
1 answer
110 views

Subquery with variable in where

I don't know why but if I do the subquery where in the first LEFT JOIN with the value of the variable @id it does not return any results, it is as if the value of @id was always null. But why not do ...
Woton Sampaio's user avatar
0 votes
2 answers
40 views

Update field with the longest varchar field

I need to update on of the varchar fields in a table, but only the record that is the longest varchar (has the most data in it.). I could easily find it with this query: SELECT TOP(1) options FROM ...
66Gramms's user avatar
  • 103
1 vote
2 answers
8k views

Unknown column in 'where clause' when using an alias for a subquery in MySQL

This simple query: select exam.id as exam_id, (select count(*) from dataset where dataset.id = exam.id) as n_dataset from exam where n_dataset = 0 returns the following error: ERROR 1054 (42S22) at ...
arthur.sw's user avatar
  • 113
0 votes
1 answer
629 views

Is there any better way to list all the databases in Postgres except database named postgres?

SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true And datname NOT IN ('postgres') Using the code above, I get all the databases except postgres as shown in the image ...
hunterex's user avatar
  • 127
0 votes
1 answer
291 views

Materialized view logs — with condition?

I've created materialized view logs on a WORKORDER table in an Oracle 19c database. create materialized view log on my_workorder_system.workorder with rowid; The plan is to integrate the WORKORDER ...
User1974's user avatar
  • 1,427
2 votes
1 answer
94 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
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 ...
TheLovelySausage's user avatar
0 votes
2 answers
31 views

(Beginner) MYSQL: What can I do about this where statement?

Suppose I have some table named tableA and column1 in tableA has some null values and strings. Also suppose I had created some temporary table named tempTable that has only 1 column. And that 1 column ...
Tokzeras's user avatar
-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 ...
Roadkill'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
1 vote
1 answer
574 views

Function that loops through array parameter values to build multiple WHERE clauses (postgres 11.4)

I have been iterating on an idea presented by Rob Conery in his excellent post to generate monthly reports using PostgreSQL Views. My version required the View to be refactored into a Function as I ...
James Randall's user avatar
1 vote
1 answer
56 views

CASE in WHERE clause - retuns a single-row result instead of 4 rows?

Everyone, I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear - I need to show all projects that are related to ...
query_question's user avatar
0 votes
1 answer
36 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 ...
Johan Brink's user avatar
0 votes
1 answer
729 views

How does adding LIMIT work with Where conditions in mysql?

In my database, there is only one record which satisfies the condition iv_title = 'ball skills'. Now I'm executing the following query : SELECT * FROM `imageAndVideo` WHERE `iv_title` = '...
rahim.nagori's user avatar
5 votes
2 answers
307 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
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 ...
Andre Yonadam's user avatar
0 votes
1 answer
57 views

How to use WHERE condition when using ON clause [closed]

I have looked at similar posts and have tried them but dint seem to work. In the below query on a MSSQL 2015 database, I need to filter out inactive users. That is, only return results where 'Active' =...
skalazer's user avatar
0 votes
1 answer
27 views

Performance of query from this schema

Essentially all comments are stored in this schema (dbml) table comments { commentID BINARY(16) [not null] //UUID post_date timestamp postID BINARY(16) [not null] //UUID has_reply bool [...
evan's user avatar
  • 3
0 votes
0 answers
90 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 ...
Featherws's user avatar
-1 votes
2 answers
39 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,...
Fernando Santiago's user avatar
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 ...
Reveille's user avatar
  • 149
0 votes
2 answers
371 views

How to select Overdue Rows with Date Frequencies?

+------------------------+--------+ | Invoice_id | due_date | amount | +-------------+----------+--------+ | 20 |2020-01-18| 1250 | +-------------+----------+--------+ | 21 |2020-...
Dev Matee's user avatar
  • 101
-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 ...
Com's user avatar
  • 101
-1 votes
2 answers
2k views

Select Rows From One Table, Where Matching Rows in Another Table Has Empty Column

I have multiple Postgres tables that look like the following. They are generated on a daily basis, so each just represents the data that was processed for that day: data_20200113 +-----------+-------...
Drew's user avatar
  • 1
0 votes
1 answer
897 views

bridge table joined table have both the keys equal to both the keys

SELECT DISTINCT i0.id, i0.name FROM main_table AS i0 RIGHT OUTER JOIN "bridge_table" AS i3 ON i3."main_table_id" = i0."id" RIGHT OUTER JOIN "other_table" AS r4 ON i3."other_table_id" ...
Radio Active's user avatar