Top vs Top with ties

Compare top vs top with ties:

In SQL, TOP is used to retrieve a specified number of rows, while TOP WITH TIES returns the top rows along with any additional rows that have the same value as the last row in the top set, potentially returning more than the specified TOP number.

Example:

SELECT TOP 5 * FROM Employees ORDER BY Salary DESC will return the top 5 highest salaries.
SELECT TOP 5 WITH TIES * FROM Employees ORDER BY Salary DESC will return the top 5 highest salaries and any additional employees with the same salary as the 5th highest.

TOP (n):

• Returns the first n rows of the result set, based on the ORDER BY clause.
• If there are ties for the n^th row, only the first n rows are returned, and the tied rows are not included.

TOP WITH TIES:

• Returns the first n rows, as with TOP.
• Includes all rows that have the same value as the last row in the TOP n set, even if it exceeds n.

---

---

SQL Query output in graphical representation

SQL Query output in graphical representation:

In SQL query sample I am using some pre defined function to show output in graphical output. Here, we have two important tables Product and OrderDetail that returns sale quantity and sale amount of each product. and I need to rate the product based on the sale quantity.

Output SQL:

WITH salesgraphical AS (SELECT CONVERT(VARCHAR(10), productname) ProductName, Sum(quantity) ActualSale, ( Round(( Sum(quantity) / 25.00 ), 0) ) * 25 RoundedSaleBy25, Round(( ( Round(( Sum(quantity) / 25.00 ), 0) ) * 25 ) / 100, 0) fullRounds, CASE Round(( ( Round(( Sum(quantity) / 25.00 ), 0) ) * 25 ) / 100, 2) - Floor(( ( Round(( Sum(quantity) / 25.00 ), 0) ) * 25 ) / 100) WHEN 0.25 THEN 2 WHEN 0.5 THEN 3 WHEN 0.75 THEN 4 ELSE 1 END ExtraRound FROM orderdetail OD JOIN productmaster PM ON OD.productid = PM.productid GROUP BY productname) SELECT productname, actualsale, Concat(Replicate(N'🌑', fullrounds - 1), Choose(extraround, N'🌑', N'🌔', N'🌓', N'🌒'), Replicate(N'🌕', 5 - fullrounds)) graphicalOutput FROM salesgraphical;

You will learn the use of the following functions:

• How to use the WITH clause to create a Common Table Expression (CTE) named salesgraphical.
• Use of Round () function to rounded on 100's part or 25'th part of values.
• Use of Choose function to select a value based on the index provided.
• Use of Replicate function to repeat a value a specified number of times.
• Use of Concat function to concatenate multiple strings together.

---

---