Click here to Skip to main content
12,075,542 members (66,580 online)
Rate this:
 
Please Sign up or sign in to vote.
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: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
v2
Comments
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: bad
 
good
Please Sign up or sign in to vote.

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!
Smile | :)
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
v2

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160208.1 | Last Updated 11 Sep 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100