SQL Query question

[renzlo]

Hi, All,

I have a data table that looks like this:

USERNAME   ICOUNT   IAVERAGE    DATE_LOGGED
LAGX01     1245     1245        05-07-2012
LAGX02     2211     1422        05-07-2012
LAGX03     1698     1112        05-07-2012
LAGX04     4598     1555        05-07-2012
LAGX05     2589     3245        05-07-2012
LAGX06     3321     1155        05-07-2012
LAGX07     3698     3458        05-07-2012
LAGX08     2589     4587        05-07-2012
LAGX09     1598     1142        05-07-2012
LAGX10     3156     1987        05-07-2012
LAGX11     5547     2011        05-07-2012
LAGX12     9456     3459        05-07-2012

Now, I want to execute a sql query that will display the top 5 based on average, so i did this:

SELECT DISTINCT USERNAME,IAVERAGE FROM myTable WHERE IAVERAGE > 0 AND DATE_LOGGED='05-07-2012' ORDER BY IAVERAGE LIMIT 0,5

and the result is:

USERNAME  ICOUNT  IAVERAGE DATE_LOGGED
LAGX03    1698    1112     05-07-2012
LAGX09    1598    1142     05-07-2012
LAGX06    3321    1155     05-07-2012
LAGX01    1245    1245     05-07-2012
LAGX02    2211    1422     05-07-2012

Now, I want to numberize my query result, so it will look like this:

RANK USERNAME  ICOUNT  IAVERAGE DATE_LOGGED
1    LAGX03    1698    1112     05-07-2012
2    LAGX09    1598    1142     05-07-2012
3    LAGX06    3321    1155     05-07-2012
4    LAGX01    1245    1245     05-07-2012
5    LAGX02    2211    1422     05-07-2012

What should be my query? Thanks in advance.

[cyberdaemon]

http://support.microsoft.com/kb/186133

maybe this can solve your problem..

[poojavb]

what type of database are u using....coz the query is different in SQL and access

[Reverend Jim]

If you are using a recent version of SQL server (2005 or newer) you can use the new ranking functions. Your specific query to generate the above results would be

SELECT TOP 5 RANK() OVER (ORDER BY IAVERAGE) AS Rank,
       USERNAME,ICOUNT,IAVERAGE,DATE_LOGGED
  FROM MyTable   
 WHERE IAVERAGE > 0 AND DATE_LOGGED='05-07-2012'

RANK() will generate a ranking number which will repeat for equal averages (all IAVERAGE numbers that are equal will have the same ranking number). If you wanted to further subdivide the rankings, you could always include a PARTITION clause as follows (let's assume you have a field named CLASSNUM)

SELECT TOP 5 RANK() OVER (PARTITION BY CLASSNUM ORDER BY IAVERAGE) AS Rank,

For this result set, the ranking number would be reset to 1 for each class.

[renzlo]

thanks for the help cyberdaemon and Reverend Jim