Filter and Sort Data in Azure Synapse SQL | WHERE and ORDER BY with OPENROWSET | Azure Synapse Analytics Tutorial

🔎 Filter and Sort Data in Azure Synapse SQL

In Azure Synapse SQL, the WHERE clause is used to filter rows based on specific conditions, and the ORDER BY clause is used to sort the result set based on one or more columns. These clauses are fundamental for querying structured datasets efficiently.

📘 Key Concepts

• WHERE: Filters rows based on a condition
• ORDER BY: Sorts the result by one or more columns in ascending (ASC) or descending (DESC) order

🧪 Example SQL Code

-- Filtering Data with WHERE Clause in Azure Synapse Analytics ( Serverless SQL Pool)
-- ==================================================================================

-- 🔹 Definition:
-- The WHERE clause is used to filter records and retrieve only the rows that satisfy a specified condition.

-- ✅ Example: Filtering data from External Table
SELECT
    id,
    fname,
    lname,
    salary
FROM dbo.users
WHERE fname='Aamir' OR  salary>5000

-- ✅ Example: Filtering data from CSV File using OPENROWSET
SELECT
    id,
    fname,
    lname,salary
FROM OPENROWSET(
    BULK 'https://techbrotherssynapsestg.dfs.core.windows.net/synpasecontainer/input/customer_20250310_090501_US.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) WITH (
    id NVARCHAR(4000),
    fname NVARCHAR(4000),
    lname NVARCHAR(4000),
    salary FLOAT
) AS customer_data
WHERE Salary > 4000 and id>1


-- ======================================================
-- Sorting and Ordering Data in Azure Synapse Analytics
-- ======================================================

-- 🔹 Definition:
-- The ORDER BY clause is used to sort the result-set in ascending (ASC) or descending (DESC) order based on one or more columns.

-- ✅ Example: Sorting data from External Table
SELECT
    id,
    fname,
    lname,
    salary
FROM dbo.users
ORDER BY salary;

-- ✅ Example: Sorting data from CSV File using OPENROWSET
SELECT
    id,
    fname,
    lname,
    salary
FROM OPENROWSET(
    BULK 'https://techbrotherssynapsestg.dfs.core.windows.net/synpasecontainer/input/customer_20250310_090501_US.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) WITH (
    id NVARCHAR(4000),
    fname NVARCHAR(4000),
    lname NVARCHAR(4000),
    salary NVARCHAR(4000)
) AS customer_data
ORDER BY lname, CAST(salary AS FLOAT) ASC

📺 Watch the Tutorial

Credit: This blog post was assisted by ChatGPT and Gemini.