fetching top 12 records of each client

Question

0.00/5 (No votes)

See more:

I am having performance data of each client in a table

having fields

clientid(varchar(20)),date(datetime), performance (number)

now what I want is, to fetch top 12 twelve record of each client on the basis of their performance.

please help..

thanks in advance

Posted 4-Jun-12 1:30am

djrocks0101

Add a Solution

Comments

RDBurmon 8-Jun-12 8:27am

Thanks Everyone who replied to this thread , So OP, I think you have got enough response and you should be able to mark it as your answer and close the thread.

4 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad_Kulkarni · Answer 1 · 2012-06-04T01:48:00

Solution 2

Try this:

SQL

SELECT a.* FROM articles AS a
  LEFT JOIN articles AS a2
    ON a.section = a2.section AND a.article_date <= a2.article_date
GROUP BY a.article_id
HAVING COUNT(*) <= 12;

Posted 4-Jun-12 1:48am

Prasad_Kulkarni

thatraja · Answer 2 · 2012-06-04T09:36:00

Solution 3

Tested query

SQL

SELECT DISTINCT TOP 12(Clientid), MAX(Performance) AS MAX_PerformanceOfClient FROM [TableName]
GROUP BY Clientid
ORDER BY MAX(Performance) DESC

Posted 4-Jun-12 9:36am

thatraja

kodeLogic · Answer 3 · 2012-06-04T01:40:00

Solution 1

Try this statement

SQL

select distinct top 12 (clientid), performance from tableName
Order By performance desc

Regards,
T

Posted 4-Jun-12 1:40am

kodeLogic

Updated 4-Jun-12 1:52am

v3

Comments

Prasad_Kulkarni 4-Jun-12 7:48am

I think you miss'd table name..

kodeLogic 4-Jun-12 7:53am

Thank you, i've corrected it.

Prasad_Kulkarni 4-Jun-12 8:27am

Good!

djrocks0101 4-Jun-12 8:14am

its fetching 12 records of one client only..I need 12 records from each client..

damodara naidu betha · Answer 4 · 2012-06-08T00:47:00

Solution 4

Hi ,

Try this code

SQL

SELECT ClientId,Performance,Date  FROM
( SELECT ROW_NUMBER() OVER (PARTITION BY Performance ORDER BY ClientD DESC) AS 'RowNumber',
CleintId,Performance,Date FROM @tempContacts) dt WHERE
dt.RowNumber <=12 ORDER BY Performance,CleintId.

Regards

Damu

Posted 8-Jun-12 0:47am

damodara naidu betha

Updated 8-Jun-12 0:54am

v3

fetching top 12 records of each client

4 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0

fetching top 12 records of each client

4 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us