Getting records where column value equals an aggregate value of that column

Question

I have a table with several columns such as "pcode", "completion in %" and "pstatus". For example:

pcode P_Name     completion  subordinate         pstatus
p123  inventory     10%         sanjay           progrssing  
p123  test          20%         komal            progrssing
p124  asd           20%         ritika           progrssing
p124  qwsdf         10%         asd              progrssing

I want to get result with different code with highest complete% with other column. All field are text. I'm using asp.net using c# and msaccess database.

What I'm looking for as a result is:

 pcode P_Name     completion  subordinate         pstatus
  p123  test          20%         komal           progrssing
  p124  asd           20%         ritika          progrssing

nulltoken · Answer 1 · 2011-12-07T15:19:07.783

1

On the database side, you'll have to filter your data. Following query should help you with that

SELECT t1.[pcode], t1.[P_Name], t.[completion], MAX(t1.[subordinate]) [subordinate], [pstatus]
FROM [YOUR_TABLE] t1
    JOIN 
    (SELECT [pcode], max([completion]) [completion]
    FROM [YOUR_TABLE]
    GROUP BY [pcode]) t
    on t.[pcode] = t1.[pcode] and t.[completion] = t1.[completion]
GROUP BY t1.[pcode], t1.[P_Name], t.[completion], t1.[pstatus]

Note: Concerning the binding part of your question, you'll have to provide more information if you're wiling to get (precise) answers.

edited Dec 07 '11 at 15:19

answered Dec 07 '11 at 09:30

nulltoken

60,369
19
132
128

thanks for reply but which type information u require pls tell me,when i execute ths query and it ask completion paraeter value name,in my tablecompletion is text type – user1085240 Dec 07 '11 at 09:44
i m using visual stdio 2008 with .net framwork 3.5.I m new i hav no more information in database – user1085240 Dec 07 '11 at 12:32
Are you developing a web application? A desktop application? Are you using an ORM or plain ADO.Net data access features? You'll have to help me a bit there... – nulltoken Dec 07 '11 at 12:35
web application and plain ado.net thanks budy – user1085240 Dec 07 '11 at 13:43
Last question: Web Forms or MVC? – nulltoken Dec 07 '11 at 13:50
Ask freely if u hav any query – user1085240 Dec 09 '11 at 11:38
@user1085240: I already did, but didn't get any answer: "Web Forms or MVC". – nulltoken Dec 09 '11 at 12:48

Getting records where column value equals an aggregate value of that column

1 Answers1