50
votes
Accepted
Using column alias in a WHERE clause doesn't work
The manual clarifies here:
An output column's name can be used to refer to the column's value in
ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses;
there you must write out the ...
- 167k
12
votes
Accepted
Does using inequality (<>) or NOT in where clause lead to SQL ignoring the index?
A <> X predicate is sargable.
SQL Server can convert it to two range seeks (on < X or > X).
Unless the index is covering for the query however you may well not see this. The tipping point ...
- 81.3k
11
votes
Count with where clause
No, the syntax you have is not valid, it can be corrected by the use of a CASE expression.
(and I guess you have a GROUP BY a, b as you'd get an error otherwise).
select
a
b,
count(case when ...
- 96.2k
9
votes
Accepted
SQL Return Table with where clause and occurrences of value of total table
You can use a windowed COUNT inside a derived table (subquery)
SELECT *
FROM (
SELECT *,
Total = COUNT(*) OVER (PARTITION BY t.RouteId)
FROM MyTable t
) t
WHERE t.Name = 'Bob';
db<&...
- 10.2k
8
votes
Locate the smallest missing element based on a specific formula
There are a few challenges with this question. Indexes in SQL Server can do the following very efficiently with just a few logical read each:
check that a row exists
check that a row doesn't exist
...
- 31.7k
8
votes
Accepted
SQL beginner, need help with WHERE syntax error
Strings are denoted with Single Quotes.
WHERE titles.price > '13'
If your price column is an int/decimal/float (or any numeric) datatype, you don't have to use a string.
WHERE titles.price > ...
- 2,234
7
votes
Updating a WHERE clause to check if a value is NOT in a separate table
CREATE TABLE dbo.CustomerExclusions
(
CustomerNumber VARCHAR(32) PRIMARY KEY -- Is CustomerNumber *really* a string?
);
INSERT dbo.CustomerExclusions(CustomerNumber) VALUES('1234'),('5678');
Now ...
- 179k
7
votes
Accepted
Use specific array column in the where clause
You put the cast outside the array constructor - use ::varchar[] instead. The current query will return you the array literal (which is of type varchar) instead of an actual array - see the example ...
- 30.4k
7
votes
Does MySQL run WHERE clause before ORDER BY?
The logical order in which a query is evaluated is:
FROM / JOIN clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
so from a logical point of view, yes the WHERE should ...
- 22.8k
7
votes
LEFT JOIN with WHERE (Microsoft Access SQL)
In your query left join is performed only using only the condition a.ID=b.ID and then results are filtered based on condition b.lang='de'. You need to add both conditions on the left join:
SELECT a....
- 318
6
votes
Accepted
Do WHERE IN queries reindex the table after each set within the query?
No SELECT causes reindexing. Only ALTER, OPTIMIZE, and a few other commands, may cause reindexing.
The construct (a,b) IN ((1,2), (3,4)) is not well optimized.
So,... Make it two DELETEs, such as
...
- 74.9k
6
votes
Accepted
Multiple WHERE conditions on same column =val1 but doesnt=val2 or val3
Your WHERE clause does not make much sense, because it is applied to each row individually and it is pointless to check if the same value is equal to a and at the same time not equal to b or c – of ...
- 22.5k
6
votes
Accepted
Search multiple values in multiple columns
Personally I would create your table
id
result_id
value
But with a single value in the value column. Now you can index on the value column and that should dramatically speed things up. Your query ...
- 23.9k
6
votes
Accepted
Select encompassing rows
You could just simply get the previous and next measure values with LAG and LEAD, and filter based on them:
select road_id, vertex_index, measure_val from (
select
r.road_id, r.vertex_index, r....
- 39.5k
6
votes
Accepted
How do I query PostgreSQL enums like in MySQL?
I think you have a misconception about ENUM because of MySQL breaking the spec and being goofy. They're different in both MySQL and PostgreSQL, but they're more apparently different in PostgreSQL. You'...
- 60.4k
6
votes
Accepted
Locate the smallest missing element based on a specific formula
Joe's already hit on most of the points I just spent an hour typing up, in summary:
highly doubtful you'll every run out of KeyCol values < bigint max (9.2e18), so conversions (if necessary) to/...
- 2,560
6
votes
Perform TABLESAMPLE with WHERE clause in PostgreSQL
tablesample is an "attribute" of a table, not a query. So you need to write it write after the table name:
select *
from customers tablesample system (1)
where last_name = 'powell';
Note that the ...
- 78.4k
6
votes
Given a table with date_time column, how to run a query using only the date
A good practice with date range queries is to specify and inclusive start date and exclusive end date like below. This will effectively ignore the time component when the range values are exact dates (...
- 26.7k
6
votes
SQL Return Table with where clause and occurrences of value of total table
Three more alternatives:
Subquery
SELECT
MT.*,
Total =
(
SELECT COUNT_BIG(*)
FROM dbo.MyTable AS MT2
WHERE
MT2.RouteId = MT....
- 80.3k
5
votes
Accepted
In Postgres, is it possible to get all rows from a table where a column can be cast to a different type?
You can create a function for this:
create function is_valid_ip(p_data text)
returns boolean
as
$$
begin
return p_data::inet is not null;
exception
when others then return false;
end;
$$
...
- 78.4k
5
votes
Accepted
Updating a WHERE clause to check if a value is NOT in a separate table
Create a table to hold the customer numbers to be excluded, then exclude those rows using a NOT EXISTS in the WHERE clause.
CREATE TABLE dbo.ExcludedCustomers
(
CustomerNumber VARCHAR(255) NOT ...
- 69.1k
5
votes
SQL Server -- More efficient alternatives to repeating an (almost) identical subquery
Hard to say for sure without seeing the full schema and queries, to work on the execution plans. But you could try:
Where
(Select Count(Distinct fld1) From t1 ...etc... where ...etc... and fld1 In ...
- 3,498
5
votes
Accepted
How do I use the result of a CASE statement in a WHERE clause?
The problem is that Oracle evaluates the SELECT after the WHERE clause.
You can either put your query in a subselect:
SELECT gpaScore
FROM (SELECT ( CASE grade
WHEN 'A+' THEN 4
...
- 720
5
votes
Building where clause based upon parameters
The output of a CASE expression has to be a single value not a list of values.
The statement did not give a syntax error for cases 3 and 4 because '''Value1'',''Value2''' is evaluated as a single ...
- 96.2k
5
votes
Accepted
Complex joins in multiple subqueries
Seems like the most difficult part if finding out which products are sold in all stores. As with most things, there's more than one way to do this, but the following query should return all product ...
- 11.6k
5
votes
Accepted
mysql query only works when value is compared with primary key
Look at the output of your select statement: it seems you have a leading blank in your values.
- 7,646
5
votes
SQL beginner, need help with WHERE syntax error
You are doing a cartesian join between the authors and titles tables (i.e. joining every row in authors to every row in titles), which is almost never what you want to do.
You need to specify how to "...
- 151
5
votes
Accepted
Condition in WHERE vs condition in CONNECT BY
After reading the documentation, I came to the following conclusions.
One difference between the two queries is that the first query has two condition in the connect by , in order to identify a ...
5
votes
SQL column=ALL(query): Equivalent meaning
If not mistaken you can use a not exists predicate and negate the operator for ALL in the subquery. Something like:
SELECT ...
FROM T
WHERE T.x = ALL ( select y from U )
corresponds to
SELECT ...
...
- 22.8k
Only top scored, non community-wiki answers of a minimum length are eligible