Share on Facebook

Sunday, April 15, 2007

Tie in Max or Min Function - WITH TIES

If you have two employees with the same birth date - the youngest/oldest employee has a tie. Then use of WITH TIES clause will solve it.

If WITH TIES is also specified, all rows that contain the last value returned by the ORDER BY clause are returned, even if doing this exceeds the number specified by expression.

Top 1 could return two or even more rows in case of a Tie

Select TOP 1 WITH TIES BirthDate from HumanResources.Employee Order By 1 Desc

WITH TIES can be used to get the matching records of the last/first even if it is more than the given number or percent

WITH TIES requires an ORDER BY clause.

No comments:

Related Posts Plugin for WordPress, Blogger...