Adding a number id to a table

SELECT top 5
day_description,
count(numberofdays) as number

FROM mytable

where department = 'departmentname'

group by day_description

my select brings something like this back:

day description           number           
holiday                     32               
working day                 212              
training day                5                
meeting day                 26               
admin day                   66   

Is there a way of adding an new column where it asigns either 1-5, 1 being the highest value row, and 5 being the lowest value row. And looks like this:

day description           number           top5ID
holiday                     32               3
working day                 212              1
training day                5                5
meeting day                 26               4
admin day                   66               2

Answers


Maybe something like this:

;WITH CTE
AS
(
    SELECT top 5
        day_description,
        count(numberofdays) as number
    FROM mytable
    where department = 'departmentname'
    group by day_description
)
SELECT
    ROW_NUMBER() OVER(ORDER BY CTE.number DESC) AS Top5ID,
    CTE.*
FROM
    CTE

Or without CTE:

SELECT
    ROW_NUMBER() OVER(ORDER BY t.number DESC) AS Top5ID,
    t.*
FROM
    (
        SELECT top 5
        day_description,
        count(numberofdays) as number
        FROM mytable
        where department = 'departmentname'
        group by day_description
    ) AS t

So, you can use it:

SELECT TOP 5 day_description, COUNT(numberofdays) AS number, 
ROW_NUMBER() OVER(ORDER BY COUNT(numberofdays) DESC) AS Top5ID
FROM mytable
WHERE department = 'departmentname'
GROUP BY day_description
ORDER BY COUNT(numberofdays) DESC

Need Your Help

iText PDF Text Extraction with fonts and styles

java android pdf itext

I am using iText to extract text from PDF to a String but I have encountered a problem

.htaccess url rewrite rules problems

php apache .htaccess mod-rewrite url-rewriting

I need some help to write a rewrite rule : I tested many many things but i guess i'm doing something wrong.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.