SQL Server Inner Join Top 1

As part of this SQL Server tutorial, we will learn and understand how to use the SQL Server SELECT TOP 1 statement with the INNER JOIN clause. This will enable us to retrieve data from a table.

Recently, I got an update from the client while working on the Country’s Geography project. The SQL Server SELECT TOP 1 statement with an INNER JOIN on the tables is required. As a result, SELECT JOIN can be used to achieve the desired result.

Here is the list of topics that we are going to cover:

• Use of SQL Server Inner Join Top 1
• How to use SQL Server Inner Join Top 1 and Group By
• Use of SQL Server Inner Join Top 1 Count
• How to use SQL Server Inner Join Top 1 Distinct
• Working of SQL Server Inner Join Top 1 Greater Than
• How to use SQL Server Inner Join Top 1 In Where Clause
• Use of SQL Server Inner Join Top 1 Multiple Table
• Working of SQL Server Inner Join Top 1 Not Null
• Use of SQL Server Inner Join Top 1 Order By
• How to use SQL Server Inner Join Top 1 Row_Number?
• Use of SQL Server Inner Join Top 1 Year
• Working on SQL Server Inner Join Top 1 Yesterday

SQL Server Inner Join Top 1

The SQL Server INNER JOIN clause selects records that have matching records from both tables. If we want to retrieve the first record from the tables then we have to use the SELECT TOP 1 statement with the INNER JOIN clause on tables by the query.

EXAMPLE:

SELECT TOP 1 COUNTRIES.COUNTRY_NAME,
CITY.CITY_NAME 
FROM COUNTRIES
INNER JOIN CITY
ON COUNTRIES.COUNTRY_ID=CITY.COUNTRY_ID;

With the help of the INNER JOIN in the SELECT statement, we retrieve the first record of the country’s name and city’s name from both tables i.e., COUNTRIES and CITY.

We hope you understand how to use the SQL Server SELECT TOP 1 statement with the INNER JOIN on tables by the query.

Read: SQL Server SELF JOIN 

SQL Server Inner Join Top 1 and Group By

Here we will see an example of SQL Server SELECT TOP 1 statement with the INNER JOIN and GROUP BY clauses on tables by the query.

EXAMPLE:

SELECT TOP 1 COUNT(CITY.CITY_NAME) AS CITY_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM CITY
INNER JOIN COUNTRIES
ON CITY.COUNTRY_ID=COUNTRIES.COUNTRY_ID
GROUP BY COUNTRIES.COUNTRY_NAME;

In the INNER JOIN query example, we retrieve the first record and counted the city’s name based on the country’s name from both tables i.e.; CITY and COUNTRIES.

We hope you understood how to use the SQL Server SELECT TOP 1 statement with the INNER JOIN and GROUP BY clauses on tables by the query.

Read: SQL Server LEFT JOIN Tutorial

SQL Server Inner Join Top 1 Count

Let’s see an example of SQL Server SELECT TOP 1 statement with the COUNT function and INNER JOIN clause on tables by the query.

EXAMPLE:

SELECT TOP 1 COUNT(STATES.STATE_NAME) AS STATES_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM STATES
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
GROUP BY COUNTRIES.COUNTRY_NAME;

With the help of the INNER JOIN in the SELECT statement, we retrieve the first record and counted the state’s name based on the country’s name from both tables i.e.; STATES and COUNTRIES.

With the help of the example, we hope you understand how to use the SQL Server SELECT TOP 1 statement with the COUNT function and INNER JOIN clause on tables by the query.

Read: SQL Server Inner Join With Where Clause

SQL Server Inner Join Top 1 Distinct

In this SQL Server subtopic section, we will learn and understand how to use the SQL Server SELECT TOP 1 statement with the DISTINCT and INNER JOIN clauses on tables by the query.

EXAMPLE:

SELECT DISTINCT TOP 1 STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM STATES
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID;

With the help of the INNER JOIN in the SELECT statement, we retrieve the unique first record of the state’s name with the country’s name from both tables i.e.; STATES and COUNTRIES.

We hope you understood the example of SQL Server SELECT TOP 1 statement with the DISTINCT and INNER JOIN clauses on tables by the query.

Read: RIGHT JOIN in SQL Server

SQL Server Inner Join Top 1 Greater Than

Here we will see an example of SQL Server SELECT TOP 1 statement with the INNER JOIN and GREATER THAN operator on tables by the query.

EXAMPLE:

SELECT TOP 1 STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM STATES
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.TOTAL_POPULATION < 5000000;

With the help of the INNER JOIN query example, we retrieve the first record of the state’s name with the country’s name from both tables i.e.; STATES and COUNTRIES by the WHERE condition.

In the WHERE condition, we filtered the population of the country which carries a value of less than 500 million for the resultset.

We hope you understand how to use the SQL Server SELECT TOP 1 statement with INNER JOIN and GREATER THAN operator on tables by the query.

Read: SQL Server Count Join Group By

SQL Server Inner Join Top 1 In Where Clause

Let’s see an example of SQL Server SELECT TOP 1 statement with the INNER JOIN and the WHERE clause on tables by the query.

EXAMPLE:

SELECT TOP 1 STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM STATES
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.COUNTRY_NAME='Australia';

With the help of the INNER JOIN in the SELECT statement, we retrieve the first record of the state’s name with the country’s name from both tables i.e.; STATES and COUNTRIES by the WHERE condition.

In the WHERE condition, we filtered the country’s name as Australia so that we can get the state’s name for the resultset.

We hope you understand the subtopic “SQL Server Inner Join Top 1 in Where Clause” by using the SQL Server SELECT TOP 1 statement with the INNER JOIN and WHERE clause on tables by the query.

Read: Delete From With Join in SQL Server

SQL Server Inner Join Top 1 Multiple Tables

In this SQL Server subtopic section, we will learn and understand how to use the SQL Server SELECT TOP 1 statement with the INNER JOIN on multiple tables by the query.

EXAMPLE:

SELECT TOP 1 COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME,
CITY.CITY_NAME
FROM COUNTRIES
INNER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
INNER JOIN CITY
ON STATES.STATE_ID=CITY.STATE_ID;

With the help of the INNER JOIN query example, we retrieve the first record of the country’s name with the state’s name and city’s name from these tables i.e.; COUNTRIES, STATES, and CITY.

We hope you understand how to use the SQL Server SELECT TOP 1 statement with the INNER JOIN on multiple tables by the query.

Read: What is SQL Server Cross Join?

SQL Server Inner Join Top 1 Not Null

In this SQL Server subtopic section, we will learn and understand how to use the SQL Server SELECT TOP 1 statement with the INNER JOIN and IS NOT NULL condition on tables by the query.

EXAMPLE:

SELECT TOP 1 COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES
INNER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
WHERE STATES.STATE_NAME IS NOT NULL;

In the INNER JOIN query example, we retrieve the first record of the country’s name with the state’s name from both tables i.e.; COUNTRIES and STATES by the WHERE condition.

In the WHERE condition, we filtered the record of the state’s name by NOT NULL value so that we can retrieve the first record of the country’s name for the resultset.

We hope you understand how to use the SQL Server SELECT TOP 1 statement with the INNER JOIN and IS NOT NULL condition on tables by the query.

Read: SQL Server Left Join With Count

SQL Server Inner Join Top 1 Order By

Here we will learn and understand how to use the SQL Server SELECT TOP 1 statement with the INNER JOIN and ORDER BY clauses on tables by the query.

EXAMPLE:

SELECT TOP 1 COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES
INNER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
ORDER BY STATES.STATE_NAME DESC;

With the help of the INNER JOIN query example, we retrieved the first record of the country’s name and state’s name from both tables i.e.; COUNTRIES and STATES by the ORDER BY clause.

In the ORDER BY clause, we arrange the record of the state’s name in descending order so that we can get the first record of the country’s name with the state name for the resultset.

We hope you understood the concept of using the SQL Server SELECT TOP 1 statement with the INNER JOIN and ORDER BY clauses on tables by the query.

Read: SQL Server Outer Join With Count

SQL Server Inner Join Top 1 Row_Number

Let’s see an example of SQL Server SELECT TOP 1 statement with the ROW_NUMBER function and INNER JOIN clause on tables by the query.

EXAMPLE:

SELECT TOP 1 ROW_NUMBER()
OVER(PARTITION BY STATES.COUNTRY_ID
ORDER BY STATES.STATE_NAME) AS ARRANGEMENT_OFSTATES,
COUNTRIES.COUNTRY_NAME
FROM STATES
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID;

In the example of the INNER JOIN query example, we retrieve the first record and gave the row number of the states based on country ID and also retrieved the record of the country’s name from both tables i.e.; STATES and COUNTRIES.

We hope you understand how to use the SQL Server SELECT TOP 1 statement with the ROW_NUMBER function with the INNER JOIN on tables by the query.

Read: How to use SQL Server Left Join on Distinct

SQL Server Inner Join Top 1 Year

Here we will see an example of SQL Server SELECT TOP 1 statement with the YEAR function and INNER JOIN clause on tables by the query.

EXAMPLE:

SELECT TOP 1 YEAR(TIMESHEET.CHECK_OUT) AS YEAR_VALUE,
EMPLOYEES.FIRST_NAME
FROM TIMESHEET
INNER JOIN EMPLOYEES
ON TIMESHEET.EMP_ID=EMPLOYEES.EMP_ID;

As we see in the INNER JOIN query example, we retrieve the first record and extracted the year portion value of the employee’s check-out time and first name from both tables i.e.; TIMESHEET and EMPLOYEES.

We hope you understood the subtopic “SQL Server Inner Join Top 1 Year” by using the SQL Server SELECT TOP 1 statement with the YEAR function and INNER JOIN clause on tables by the query.

Read: How To Update Table Using JOIN in SQL Server

SQL Server Inner Join Top 1 Yesterday

If you want to calculate yesterday’s value then we will use the SQL Server DATEADD function. And there is no such YESTERDAY function in the SQL Server. This named function as YESTERDAY is available in the MariaDB.

Now, have an example of SQL Server SELECT TOP 1 statement with the DATEADD function and INNER JOIN clause on tables by the query.

EXAMPLE:

SELECT TOP 1 DATEADD(DAY,-1,TIMESHEET.CHECK_OUT) AS YESTERDAY_CHECKOUT_TIME,
EMPLOYEES.FIRST_NAME
FROM TIMESHEET
INNER JOIN EMPLOYEES
ON TIMESHEET.EMP_ID=EMPLOYEES.EMP_ID;

With the help of the INNER JOIN in the SELECT statement, we retrieve the first record and extracted the employee’s yesterday checkout time and also retrieved the employee’s first name from both tables i.e.; TIMESHEET and EMPLOYEES.

We hope you comprehend how the query uses the SQL Server SELECT TOP 1 statement on tables with the DATEADD function and INNER JOIN clause.

You may also like to read the following SQL Server tutorials.

• Introduction to SQL Server Trigger
• Instead of Trigger In SQL Server
• SQL Server Drop Trigger If Exists
• SQL Server Trigger Update with Examples

We now know about the post “SQL Server Inner Join Top 1 “ after reading this tutorial. To assist you in comprehending the idea, we explored the following subtopics in detail.

• Use of SQL Server Inner Join Top 1
• How to use SQL Server Inner Join Top 1 and Group By
• Use of SQL Server Inner Join Top 1 Count
• How to use SQL Server Inner Join Top 1 Distinct
• Working of SQL Server Inner Join Top 1 Greater Than
• How to use SQL Server Inner Join Top 1 In Where Clause
• Use of SQL Server Inner Join Top 1 Multiple Table
• Working of SQL Server Inner Join Top 1 Not Null
• Use of SQL Server Inner Join Top 1 Order By
• How to use SQL Server Inner Join Top 1 Row_Number?
• Use of SQL Server Inner Join Top 1 Year
• Working on SQL Server Inner Join Top 1 Yesterday