Sql Server 2008: TOP 10 and distinct together

Optimizations and accuracy

When blending distinct records and TOP in SQL Server, the sequence of operations is crucial. Position DISTINCT inside a subquery or CTE and follow it up with ROW_NUMBER to generate a unique count.

Keep subqueries lean

For broader tables, keep your subqueries slim by excluding non-essential columns:

SELECT TOP 10 pk.id
FROM (
    SELECT DISTINCT pk.id
    FROM big_table pk
) AS DistinctRows
ORDER BY pk.id;


Reduced columns = less baggage = improved performance. Think of it as SQL dieting advice.

Distinct aggregates with Grouping

Let’s group the aggregates first if you’re seeking distinct ones. It's like sorting Lego blocks before building something awesome!

SELECT TOP 10 p.id, COUNT(*)
FROM huge_table p
GROUP BY p.id
ORDER BY COUNT(*) DESC;


Sharp Shooting with Analytical functions

For precision ranked values, analytical functions like RANK() are worth exploring:

SELECT id, score
FROM (
    SELECT id, score,
           RANK() OVER (ORDER BY score DESC) as ranking
    FROM leaderboard
) Ranks
WHERE ranking <= 10;


This will get top scores ‐ perfect for revealing your online game’s hall of fame winners!

Selective Querying Techniques

Commanding the WHERE and ORDER BY clause

Handle ordering and filtering like a pro:

SELECT TOP 10 p.id
FROM warehouse p
WHERE p.quality = 'A1'
ORDER BY p.quantity DESC;


Hey, who doesn't like premium quantity?

Unleash the power of Subqueries

Subqueries can let you have distinct fun before applying TOP magic:

SELECT TOP 10 *
FROM (
    SELECT DISTINCT p.id, p.price
    FROM shop p
) AliasedSubquery
ORDER BY p.price DESC;


The Art: Test & Tweak

Test and adjust your queries as needed. SQL code is not written in stone:

-- SELECT p.someOtherColumn,
SELECT TOP 10 p.id
FROM table1 p
ORDER BY p.importantMetric DESC;


As SQL gurus say, the mute fields shall remain silent!

Practical Scenarios

Cater to complex ordering

When ordering gets complex for uniqueness:

WITH RankedEntries AS (
    SELECT *,
           DENSE_RANK() OVER (PARTITION BY Category ORDER BY Score DESC) AS Rank
    FROM Entries
)
SELECT TOP 10 *
FROM RankedEntries
WHERE Rank = 1;


DENSE_RANK fetches distinct top-rankers per category. Variety is the spice of life, ain't it?

Clarity with Aliases

Clear, readable queries are happy queries. Use aliases especially with multiple tables or complex joins:

SELECT TOP 10 e.employeeID, e.employeeName
FROM employees AS e
JOIN departments AS d ON e.departmentID = d.deptID
ORDER BY e.salary DESC;


Maybe it’s time for a raise negotiation?

Precise column selection

Check and double-check column names and WHERE conditions. In SQL, precision is key:

SELECT TOP 10 p.productId, p.productName
FROM products p
WHERE p.productLine = 'Consumer Electronics'
ORDER BY p.sales DESC;


All categories and aggregates should make sense. Because there's a big difference between a 'mouse' and a 'Mouse'!