SQL query to find out the single record by 3 conditions

sql filter by list of values
sql where multiple conditions
sql multiple conditions same column
sql multiple where clauses in one query
sql query to select a particular row from a table
sql case
sql join
sql group by

Can you please help me with the query to find out the single record for the each student based on the subject.(Id,Name,subs are primary key). Ideally I will have 2 records for each subject. One with school TTT and MCC.

Table Structure:

StudentID   StudentName Subject Marks   School
    1          AAA        ENG      80      TTT
    1          AAA        ENG      80      MCC
    1          AAA        TAM      90      TTT
    1          AAA        TAM      90      MCC
    2          BBB        TAM      90      TTT
    2          BBB        TAM      90      MCC
    3          CCC        ENG      40      MCC
    4          DDD        ENG      95      MCC
    5          EEE        ENG      85      TTT
    5          EEE        ENG      85      MCC

I want the results

StudentID   StudentName Subject Marks   School
    3           CCC       ENG      40      MCC
    4           DDD       ENG      95      MCC

Here is one method using window functions:

select t.*
from (select t.*,
             count(*) over (partition by studentid) as cnt
      from t
     ) t
where cnt = 1;

This returns students with only one record. If you want student/subject combination with only one record, another method uses not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.studentid = t.studentid and
                        t2.subject = t.subject and
                        t2.school <> t.school
                 );

How to Filter Query Results using WHERE, A simple example of a Select statement with a where clause is Select In order for a row to be returned the expression State = 'MI' must be true, that The condition of a where clause can be made up of one or more tests. Sometimes you want to know when a value falls within a range, such as quantities greater than 10. Get single records when duplicate records exist. If you have been doing SQL development for a while, you probably have come across this common scenario in your everyday job - Retrieving a single record from a table when there are multiple records exist for the same entity such as customer.

SELECT * FROM TABLEA A 
WHERE EXISTS (SELECT STUDENTid, COUNT(1) TOTAL FROM TABLEA B 
                  WHERE A.STUDENTID = B.STUDENTID      
                 GROUP BY STUDENTID 
                 HAVING COUNT(1) = 1)

This will give records to cases where there is no duplication of IDs

Use Queries to show records based on conditions – Documentation , Queries let you show only a specific subset of your data by using the Google to use three different Awesome Table apps to explain how Query clauses work. warning Text values (string literals) are enclosed in 'single quotes' or "double� Let's break the query and analyze what's going on in inner query. Here is the code of inner query : SQL Code: SELECT AVG(ord_amount) FROM orders WHERE ord_date='20-APR-08'; Output: AVG(ORD_AMOUNT) ----- 2500 The above query returns the average 'ord_amount' 2500, is used in the WHERE clause of the outer query shown earlier.

You can filter with a correlated subquery:

select t.*
from mytable t
where (select count(*) from mytable t1 where t1.studentid = t.studentid) = 1

SQL: Combining the AND and OR Conditions, The SQL AND condition and OR condition can be combined to test for This website would not exist without the advertisements we display and your kind how to use the AND condition and the OR condition together in a single query with The conditions that are evaluated to determine if the records will be selected. Deletes an existing SQL database: DROP DEFAULT: Deletes a DEFAULT constraint: DROP INDEX: Deletes an index in a table: DROP TABLE: Deletes an existing table in the database: DROP VIEW: Deletes a view: EXEC: Executes a stored procedure: EXISTS: Tests for the existence of any record in a subquery: FOREIGN KEY: A constraint that is a key used to

Concepts of Database Management, The WHERE clause shown in Figure 3-5 includes a simple condition. You must use the correct one for your version of SQL. 3-9. Only a single record appears in the query results because the CustomerNum field is the primary key for the� SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

SQL: WHERE Clause, The SQL WHERE clause is used to filter the results and apply conditions in a SELECT, INSERT, UPDATE, or DELETE statement. This website would not exist without the advertisements we display and your kind donations. to test multiple conditions where the record is returned if any one of the conditions are met. The SQL INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. Intersect Query

SQL Server SELECT, A) SQL Server SELECT – retrieve some columns of a table example. The following sql server select - select three columns To filter rows based on one or more conditions, you use a WHERE clause as shown in the following example: . In the previous step, our query returned a list of duplicates. Now, we want to return the entire record for each duplicate row. To accomplish this, we’ll need to select the entire table and join that to our duplicate rows. Our query looks like this:

Comments
  • @user3686599 if this answer is correct please do mark it as so. Here are the instructions on how to do it: stackoverflow.com/help/someone-answers Cheers!