[sql] Using a SELECT statement within a WHERE clause

SELECT * FROM ScoresTable WHERE Score = 
  (SELECT MAX(Score) FROM ScoresTable AS st WHERE st.Date = ScoresTable.Date)

Is there a name to describe using a SELECT statement within a WHERE clause? Is this good/bad practice?

Would this be a better alternative?

SELECT ScoresTable.* 
FROM ScoresTable INNER JOIN 
  (SELECT Date, MAX(Score) AS MaxScore 
  FROM ScoresTable GROUP BY Date) SubQuery 
  ON ScoresTable.Date = SubQuery.Date 
  AND ScoresTable.Score = SubQuery.MaxScore

It is far less elegant, but appears to run more quickly than my previous version. I dislike it because it is not displayed very clearly in the GUI (and it needs to be understood by SQL beginners). I could split it into two separate queries, but then things begin to get cluttered...

N.B. I need more than just Date and Score (e.g. name)

This question is related to sql select where-clause

The answer is


This is a correlated sub-query.

(It is a "nested" query - this is very non-technical term though)

The inner query takes values from the outer-query (WHERE st.Date = ScoresTable.Date) thus it is evaluated once for each row in the outer query.

There is also a non-correlated form in which the inner query is independent as as such is only executed once.

e.g.

 SELECT * FROM ScoresTable WHERE Score = 
   (SELECT MAX(Score) FROM Scores)

There is nothing wrong with using subqueries, except where they are not needed :)

Your statement may be rewritable as an aggregate function depending on what columns you require in your select statement.

SELECT Max(score), Date FROM ScoresTable 
Group By Date

There's a much better way to achieve your desired result, using SQL Server's analytic (or windowing) functions.

SELECT DISTINCT Date, MAX(Score) OVER(PARTITION BY Date) FROM ScoresTable

If you need more than just the date and max score combinations, you can use ranking functions, eg:

SELECT  *
FROM    ScoresTable t
JOIN (   
    SELECT 
        ScoreId,
        ROW_NUMBER() OVER (PARTITION BY Date ORDER BY Score DESC) AS [Rank] 
        FROM ScoresTable
) window ON window.ScoreId = p.ScoreId AND window.[Rank] = 1

You may want to use RANK() instead of ROW_NUMBER() if you want multiple records to be returned if they both share the same MAX(Score).


In your case scenario, Why not use GROUP BY and HAVING clause instead of JOINING table to itself. You may also use other useful function. see this link


Subquery is the name.

At times it's required, but good/bad depends on how it's applied.


The principle of subqueries is not at all bad, but I don't think that you should use it in your example. If I understand correctly you want to get the maximum score for each date. In this case you should use a GROUP BY.


It's not bad practice at all. They are usually referred as SUBQUERY, SUBSELECT or NESTED QUERY.

It's a relatively expensive operation, but it's quite common to encounter a lot of subqueries when dealing with databases since it's the only way to perform certain kind of operations on data.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to select

Warning: Use the 'defaultValue' or 'value' props on <select> instead of setting 'selected' on <option> SQL query to check if a name begins and ends with a vowel Angular2 *ngFor in select list, set active based on string from object SQL: Two select statements in one query How to get selected value of a dropdown menu in ReactJS DATEDIFF function in Oracle How to filter an array of objects based on values in an inner array with jq? Select unique values with 'select' function in 'dplyr' library how to set select element as readonly ('disabled' doesnt pass select value on server) Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Examples related to where-clause

Laravel where on relationship object MySQL Select last 7 days SQLSTATE[42S22]: Column not found: 1054 Unknown column - Laravel Conditional WHERE clause in SQL Server Conditional WHERE clause with CASE statement in Oracle How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? MySQL Multiple Where Clause How to use If Statement in Where Clause in SQL? MySQL direct INSERT INTO with WHERE clause MySql Inner Join with WHERE clause