Select Max from a table with group by clause

Question

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 18-Jun-12 2:31am

vipinsethumadhavan

Add a Solution

1 solution

Add a Solution

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

Ankur^\m/ · Accepted Answer · 2012-06-18T03:20:00

Solution 1

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:

Posted 18-Jun-12 3:20am

Ankur^\m/

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.

Select Max from a table with group by clause

1 solution

Solution 1

Add your solution here

Preview 0

Select Max from a table with group by clause

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us