55 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 ...
Erwin Brandstetter's user avatar
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 ...
Martin Smith's user avatar
  • 84.7k
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 ...
ypercubeᵀᴹ's user avatar
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<&...
Charlieface's user avatar
  • 12.8k
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 ...
Joe Obbish's user avatar
  • 32.2k
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 > ...
SQLDevDBA's user avatar
  • 2,254
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 ...
András Váczi's user avatar
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 ...
Lennart - Slava Ukraini's user avatar
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....
dbilid's user avatar
  • 318
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....
Balazs Papp's user avatar
  • 40.7k
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'...
Evan Carroll's user avatar
  • 63.1k
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/...
markp-fuso's user avatar
  • 2,569
6 votes

Using column alias in a WHERE clause doesn't work

The answer was sort of posted in a comment but I had a similar problem and was directed here, figured I would share as an answer. Your alias should appear in the inner select, not the outer. select t....
darrahts's user avatar
  • 161
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 (...
Dan Guzman's user avatar
  • 28.2k
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....
Paul White's user avatar
  • 84k
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 ...
Steven Hibble's user avatar
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 ...
ypercubeᵀᴹ's user avatar
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 ...
Cody Konior's user avatar
  • 3,508
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 ...
RDFozz's user avatar
  • 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.
miracle173's user avatar
  • 7,662
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 "...
Player One's user avatar
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 ...
giannis christofakis's user avatar
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 ... ...
Lennart - Slava Ukraini's user avatar
5 votes
Accepted

SQL Server COUNT() seems to be ignoring WHERE

You need to add the same where clause in your union select. SELECT SiteTour, COUNT(SiteTour) AS count FROM tblConference WHERE (RegistrationType NOT LIKE 'cancellation') GROUP BY ...
Wes's user avatar
  • 1,126
5 votes

SQL Server COUNT() seems to be ignoring WHERE

You should avoid doing the COUNT twice. ;WITH Counts AS ( SELECT SiteTour, COUNT(SiteTour) AS count FROM tblConference WHERE RegistrationType <> 'cancellation' AND ...
EzLo's user avatar
  • 3,306
5 votes

More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows

An IN clause gets "rewritten" to this form, essentially: WHERE TableID = 260905384 OR TableID = 260915601 OR TableID = 260929877 ... I have heard, and observed, that SQL Server has ...
Josh Darnell's user avatar
  • 29.2k
5 votes
Accepted

Concurrent update on two different tables fail

Related question Inconsistent data when combining (LEFT) JOIN with SELECT FOR UPDATE Problem Your summation of the process is correct, until step 4. The query does not restart after the locks are ...
dwhitemv's user avatar
  • 1,567
4 votes
Accepted

UPDATE from subquery updates rows that shouldn't be updated

Use a FROM clause to join to more tables: UPDATE table_1 t1 -- alias optional SET column_x = t2.rename FROM table_2 t2 WHERE t2.rename IS NOT NULL -- assuming you actually meant t1 AND ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible