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

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.

1 solution

Try this:

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

May not be the best way but should help. Include the columns you want in the first select line. :thumbsup:
Manas Bhardwaj 18-Jun-12 9:23am
Correct +5!
Ankur\m/ 18-Jun-12 9:27am
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