If you are required to select TOP N records from a table being ordered by a column, you can use the following query: SELECT TOP N [COLUMNNAME] FROM TABLENAME ORDER BY [COLUMNNAME]
But there is one problem with the above statement is that if the table has multiple records having the same value as that of the selected list then all those records will not be selected. It will select only one record. But if we want to select all the rows, with same value as the one selected we have to include WITH TIES option in the query. So the query would be : SELECT TOP N WITH TIES [COLUMNNAME] FROM TABLENAME ORDER BY [COLUMNNAME] |
Example
SELECT TOP 1 CATEGORY_NAME, CATEGORY_VALUE FROM LK_CATEGORY ORDER BY CATEGORY_VALUE Result: CATEGORY_NAME CATEGORY_VALUE Work Idea 10
SELECT TOP 1 WITH TIES CATEGORY_NAME, CATEGORY_VALUE FROM LK_CATEGORY ORDER BY CATEGORY_VALUE Result: CATEGORY_NAME CATEGORY_VALUE Work Idea 10 IT 10 Conference 10 |