Click here to Skip to main content
15,353,432 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In my application , used a table. details as below
TblMaster

ID Name MasterTag Mark Desc
1 Name1 10 90 data1
2 Name2 10 80 data2
3 Name1 10 95 data3
4 Name2 11 55 data4
5 Name3 11 45 data5
6 Name1 11 98 data6
7 Name2 10 80 data7
8 Name2 10 45 data8
9 Name2 12 90 data9
10 Name1 12 85 data10


I required in my page highest mark rows of each mastertag . I used group by claues but not get full records only groups by column only.
Posted

1 solution

Try this:

SQL
SELECT t.ID, t.Mark FROM TblMaster t,
(SELECT MAX(Mark) MaxMarks, MasterTag FROM TestTable GROUP BY MasterTag) subTable
WHERE subTable.MasterTag = t.MasterTag
AND subTable.MaxMarks = t.Mark
ORDER BY ID


May not be the best way but should help. Include the columns you want in the first select line. :thumbsup:
   
Comments
Manas Bhardwaj 18-Jun-12 9:23am
   
Correct +5!
Ankur\m/ 18-Jun-12 9:27am
   
Thanks!
vipinsethumadhavan 19-Jun-12 0:44am
   
Thanks , its fine, I need to add inner join to this query, means TblTag has column MasterTag hold MasterTag FullName(varchar), so how can I get this column instead of TblMaster.MasterTag column(number)
Ankur\m/ 19-Jun-12 2:00am
   
The inner join will come before the where clause. I want you to try it yourself.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900