12,765,176 members (37,381 online)
Rate this:
See more:
`Hi All, I have a database with multiple records. See Table Structure Below :`

```id     Marks   Type

1	2	m
2	2	m
3	1	m
4	3	a
5	3	a
6	3	a
7	2	a
8	1	c
9	2	c
10	3	c```

`Now I needs a query which shows only highest marks of particular type, either they are repeated.`

```<pre>
id     Marks   Type

1        2       m
2        2       m

4        3       a
5        3       a
6        3       a

10       3       c</pre>```
Posted 10-Sep-12 20:40pm
Mas112.4K

Rate this:

Solution 1

Hi,
try this,

`SELECT id, marks, type FROM YourTable tb WHERE marks = (SELECT MAX(marks) FROM YourTable WHERE Type = tb.Type)`

Hope it helps
v2
Mas11 11-Sep-12 2:02am

wonderfull my bro !!.. Thanks a lot. It works & run me so fast. Thanks for your cooperation.
Karthik Harve 11-Sep-12 2:06am

Thanks. Happy coding
Rate this:

Solution 2

see this example
```select id,marks,type_ from
(
select dense_rank() over(partition by type_  order by type_,marks desc) as rank,* from
(
select 1 as id,100 as marks, 'm' as type_
union all
select 2 as id,99 as marks, 'm' as type_
union all
select 3 as id,100 as marks, 'm' as type_
union all
select 4 as id,100 as marks, 'E' as type_
union all
select 5 as id,99 as marks, 'E' as type_
) as a
) as a where rank=1```

Happy coding!
:)
Rate this:

Solution 3

```SELECT ZF.Id, Z.*
FROM
(
SELECT MAX(Marks) Marks, [Type]
FROM tbl_Marks
GROUP BY [Type]
)AS Z
LEFT OUTER JOIN
(
SELECT DISTINCT id, Marks, [Type]
FROM tbl_Marks
)ZF
ON Z.Marks = ZF.Marks
AND Z.[Type] = ZF.[Type]
ORDER BY ZF.Id
```
v2

Top Experts
Last 24hrsThis month
 OriginalGriff 280 Graeme_Grant 263 Jochen Arndt 160 Karthik Bangalore 155 CHill60 90
 OriginalGriff 5,057 Peter Leow 3,599 ppolymorphe 2,953 Karthik Bangalore 2,934 Graeme_Grant 2,824