Some T-SQL cool tricks

1. How to find second highest number in a column:

SELECT MAX(OrderQty) FROM Orders where OrderQty not in (select top 1 OrderQty FROM Orders ORDER BY OrderQty DESC)

2. Using COALESCE to concatenate Comma-Delimited string, this has been discussed in my previous post. This is another solution.

DECLARE @EmployeeList varchar(100) SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + CAST(Emp_UniqueID AS varchar(5)) FROM SalesCallsEmployees WHERE SalCal_UniqueID = 1 SELECT @EmployeeList


3. SELECT * FROM CUSTOMERS ORDER BY 1.
This trick is from my collegue. Note order by 1 works in SQL Server, not sure about other databases. It will order by the first column. Similarly you could order by 2, 3 ..