SFMC SQL: Mastering TOP , WHERE & Like Clause

 

 SFMC SQL: Mastering TOP & WHERE Clause 

---

🔍 What is TOP?

Think of TOP as a VIP pass 🎟️

You’re saying:

“I don’t need the whole crowd — just give me the best N records.”

👉 TOP N = fetch first N rows from your dataset

---

🧠 Mental Picture

Imagine your Data Extension is a concert hall 🎤

• TOP 10 → VIP front row
• ORDER BY → who gets priority
• No ORDER BY → random chaos 😅

---

🛠 TOP Clause – Practical Tasks

Target Data Extension 

---

👉 Task 1: Basic TOP (No Priority)

SELECT TOP 5 *
FROM CustomersDE

💡 Returns first 5 rows (random order)

---

👉 Task 2: TOP with ORDER BY (Recommended 🔥)

SELECT TOP 5 FirstName, Email, CreatedDate
FROM CustomersDE
ORDER BY CreatedDate DESC



 

💡 Latest customers first (predictable)

---

👉 Task 3: TOP with Condition

SELECT TOP 3 FirstName, Email
FROM CustomersDE
WHERE Status = 'Active'
ORDER BY CreatedDate DESC

💡 Only active + newest customers

---

👉 Task 4: TOP Per Group (Advanced)

SELECT DeptID, EmpID, Salary
FROM (
    SELECT DeptID, EmpID, Salary,
           ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rn
    FROM EmployeesDE
) x
WHERE rn = 1


 

💡 Top employee per department

---

👉 Task 5: Pagination (Skip Rows)

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS rn
    FROM CustomersDE
) x
WHERE rn BETWEEN 6 AND 10

💡 Fetch next set of records

---

⚠️ TOP Clause Limitations

❌ Only works in T-SQL (SFMC)
❌ No OFFSET support
❌ No dynamic variables
❌ No ORDER BY = random output
❌ Not ideal for large pagination

---

🔎 SFMC SQL: Understanding WHERE Clause

The WHERE clause is used to filter data 🎯

👉 Instead of getting everything, you get only what you need

---

🛠 WHERE Clause – Practical Tasks

---

📍 1. Comparison Operators

Used to compare values inside Data Extensions

---

👉 Task 1: Find Active Customers

SELECT FirstName, LastName, EmailAddress
FROM Customers_DE
WHERE Status = 'Active'
 

---

👉 Task 2: Customers with More Than 5 Purchases

SELECT FirstName, LastName, Purchases
FROM Customers_DE
WHERE Purchases > 5

---

✅ Available Operators

Operator	Meaning	Example
=	Equal	Status = 'Active'
!= / <>	Not Equal	Country != 'India'
<	Less Than	Age < 30
>	Greater Than	Purchases > 5
<=	Less Than Equal	Discount <= 20
>=	Greater Than Equal	Revenue >= 1000

---

📍 2. Logical Operators (Combine Conditions)

---

👉 Task 3: Active Customers from India or USA

SELECT FirstName, LastName, EmailAddress
FROM Customers_DE
WHERE Status = 'Active'
AND (Country = 'India' OR Country = 'USA')

 

---

👉 Task 4: Inactive Customers with Purchases > 5

SELECT FirstName, LastName, Purchases
FROM Customers_DE
WHERE Status = 'Inactive'
AND Purchases > 5





 

---

✅ Logical Operators

Operator	Meaning
AND	Both conditions must be true
OR	Any one condition true
NOT	Negates condition

---

🧠 Combine TOP + WHERE (Real Power 🔥)

---

👉 Task: Get Top 5 Active Customers (Latest First)

SELECT TOP 5 FirstName, Email, CreatedDate
FROM CustomersDE
WHERE Status = 'Active'
ORDER BY CreatedDate DESC

 

💡 This is real-world usage:
✔ Filter data
✔ Then prioritize

---

 🔶  LIKE with "%" ONLY

---

👉 Query 1 :- Get all customers whose email starts with "a"

SELECT *
FROM Customers_Master_DE
WHERE Email LIKE 'a%'

💡 Returns all records where email begins with a

---

👉 Query 2 :- Get all customers with emails ending in example.com

SELECT *
FROM Customers_Master_DE
WHERE Email LIKE '%@example.com'

💡 Filters all users using example.com domain

---

👉 Query 3 :- Get customers whose first name contains "ar"

SELECT *
FROM Customers_Master_DE
WHERE FirstName LIKE '%ar%'

💡 Matches names like Aarav, Karan

---

👉 Query 4 :- Get customers where country contains letter "a"

SELECT *
FROM Customers_Master_DE
WHERE Country LIKE '%a%'

💡 Matches countries like India, Canada, Australia

---

👉 Query 5 :- Get customers whose name ends with "a"

SELECT *
FROM Customers_Master_DE
WHERE FirstName LIKE '%a'

💡 Matches names like Neha, Sara

---

👉 Query 6 :- Get customers whose revenue ends with "00"

SELECT *
FROM Customers_Master_DE
WHERE CAST(Revenue AS VARCHAR) LIKE '%00'

  

💡 Finds values like 1000, 2500, 9000

---

🔷 2. LIKE with "_ "ONLY

---

👉 Query 1 :- Get customers whose second letter in name is "a"

SELECT *
FROM Customers_Master_DE
WHERE FirstName LIKE '_a%'

💡 Matches names like Rahul, Sara

---

👉 Query 2 :- Get customers whose third letter is "i"

SELECT *
FROM Customers_Master_DE
WHERE FirstName LIKE '__i%'

💡 Matches names like Priya

---

👉 Query 3 :- Get customers with exactly 4-letter names

SELECT *
FROM Customers_Master_DE
WHERE FirstName LIKE '____'

💡 Matches names like John, Sara

---

👉 Query 4 :- Get customers with exactly 5-letter names

SELECT *
FROM Customers_Master_DE
WHERE FirstName LIKE '_____'

💡 Matches names like Aman, Rohit

---

👉 Query 5 :- Get customers from countries with exactly 3 letters

SELECT *
FROM Customers_Master_DE
WHERE Country LIKE '___'

💡 Matches USA

---

🔴 3. LIKE with "%" + "_"

---

👉 Query 1 :- Get names where second letter is "a" and ends with "a"

SELECT *
FROM Customers_Master_DE
WHERE FirstName LIKE '_a%a'

💡 Example: Sara

---

👉 Query 2 :- Get names where second letter is "r"

SELECT *
FROM Customers_Master_DE
WHERE FirstName LIKE '_r%'

💡 Matches names like Priya

---

👉 Query 3 :- Get emails with at least 2 characters before @

SELECT *
FROM Customers_Master_DE
WHERE Email LIKE '__%@example.com'

💡 Ensures minimum 2 characters before domain

---

👉 Query 4 :- Get names where second letter is "a" and ends with "l"

SELECT *
FROM Customers_Master_DE
WHERE FirstName LIKE '_a%l'

💡 Matches names like Rahul

---

🧠 Final Learning

• % → flexible matching
• _ → fixed position
• % + _ → precise + flexible combo

 Master Rules (Like a Pro)

✔ Always use ORDER BY with TOP
✔ Use WHERE to reduce data early
✔ Combine both for best performance
✔ Use ROW_NUMBER() for advanced cases

---

📌 Final Mental Model

Think of it like event management 🎟️

• WHERE → Who is allowed inside
• TOP → Who gets VIP access
• ORDER BY → Who gets best seats

---

💬 Final Thought

If you master TOP + WHERE, you control:

✔ Data filtering
✔ Data prioritization
✔ Performance optimization