12,075,542 members (66,580 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.`

```id     Marks   Type

1        2       m
2        2       m

4        3       a
5        3       a
6        3       a

10       3       c```

Can any one have a idea, kindly share with me.
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 566 Richard MacCutchan 479 ProgramFOX 160 F-ES Sitecore 145 Dave Kreskowiak 125
 Dave Kreskowiak 2,796 OriginalGriff 2,690 Richard MacCutchan 1,834 CPallini 1,527 CHill60 1,379