Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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 19:40pm
Mas112.2K
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 at 11-Sep-12 2:02am
   
wonderfull my bro !!.. Thanks a lot. It works & run me so fast. Thanks for your cooperation.
Karthik Harve at 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)



Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 11 Sep 2012
Copyright © CodeProject, 1999-2014
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