Using the DISTINCT command eliminates duplicate records from your SQL queries. The following query will return all rows from the SESSION table. Assume that the SCENARIO_ID of 1, which is the ClassicalMusic scenario, exists multiple times. In that case all rows are returned, in addition to all other SCENARIO_IDs.
SELECT [SCENARIO_ID] FROM [dbo].[SESSION]
If you wanted to return only a single occurrence of each SCENARIO_ID, you’d use the following syntax:
SELECT DISTINCT [SCENARIO_ID] FROM [dbo].[SESSION]
Do this if you want to check whether sessions exist for all scenarios, quickly and easily.
EXCEPT and INTERSECT
These commands are useful for querying data from two different tables and either excluding or including the matches. This first query, which uses EXCEPT, results in distinct values from the first query, which are not found in the query after EXCEPT.
SELECT [SESSION_ID]FROM [dbo].[READING]EXCEPTSELECT [SESSION_ID]FROM [dbo].[SESSION]
To use INTERSECT, you can execute the following. The query returns distinct values, which are found in the results of both queries.
SELECT [SESSION_ID]FROM [dbo].[READING]INTERSECTSELECT [SESSION_ID]FROM [dbo].[SESSION]ORDER BY [SESSION_ID]
You could also include an ORDER BY to make the result render in ascending order.
EXISTS and IN
Use either of these predicate functions, which are semantically equivalent, as part of a WHERE clause. Here is an example of a query using the EXISTS predicate:
SELECT m.MODE FROM MODE AS mWHERE EXISTS(SELECT * FROM [SESSION] AS s WHERE m.MODE_ID = s.MODE_ID)
Here is an example using the IN predicate function:
SELECT m.MODE FROM MODE AS mWHERE m.MODE IN(SELECT m.MODE FROM [SESSION] AS s WHERE m.MODE_ID = s.MODE_ID)
Both the EXISTS and IN predicate functions return the same results in this example. Both CONTAINS and LIKE are considered predicate functions as well and are useful in similar scenarios where you want to filter data.
FIRST_VALUE and LAST_VALUE
Use the FIRST_VALUE to return the first value in an ordered set of values. The following SQL query returns the first READING_DATETIME value for a given brainwave VALUE. The query is projected to return only brainwaves collected from the AF3 electrode.
SELECT READING_DATETIME, [VALUE],FIRST_VALUE(READING_DATETIME) OVER (ORDER BY [VALUE] ASC) AS AF3DATEFROM READINGWHERE ELECTRODE_ID = 1
For example, if VALUE has two rows with a reading of 0.141, the READING_DATETIME of the first occurrence of 0.141 will be placed into the AF3DATE column. Here is an example of using LAST_VALUE, followed by Figure 2.27, which compares the output of the two queries.
SELECT READING_DATETIME, [VALUE],LAST_VALUE(READING_DATETIME) OVER (ORDER BY [VALUE] ASC) AS AF3DATEFROM READINGWHERE ELECTRODE_ID = 1
FIGURE 2.27 SQL query output from FIRST_VALUE and LAST_VALUE
HAVING
Use this command to apply a search condition to a GROUP BY clause:
SELECT SCENARIO_ID, MODE_ID FROM [SESSION]
GROUP BY SCENARIO_ID, MODE_ID
HAVING COUNT(SCENARIO_ID)> 5
The query results in rendering rows for scenarios with more than five sessions.
