Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
younas02
Starting Member
28 Posts |
 Posted - 2012-09-10 : 05:24:14
|
| i have a table which include fields, subjectname,title,marksa subjectname can have more than one row with different marks , i have to select row with maximum marks.for example i have 5 rows with record.subjectname | title| marksmaths | basic| 66maths | basic| 89maths | basic | 73computer | basic | 39computer | basic | 78the result i need ismaths | basic| 89computer|basic |78 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-09-10 : 05:30:47
|
[code]select *from( select *, row_no = row_number() over (partition by subjectname order by marks desc) from atable) dwhere d.row_no = 1[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-10 : 05:31:03
|
selectsubjectname,title,max(marks) as marksfrom YourTablegroup by subjectname, title
Too old to Rock'n'Roll too young to die. |
|
|
|
younas02
Starting Member
28 Posts |
Posted - 2012-09-10 : 06:53:20
|
| this is not working for me.any other solution plz. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-10 : 07:25:11
|
1. select subjectname, title, marks from YourTable2. send the output to a printer3. take a textmarker and mark the lines that you want manuallywill this work for you 
Too old to Rock'n'Roll too young to die. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-09-10 : 09:05:58
|
| cant stop laughing, webfred :)--------------------------http://connectsql.blogspot.com/ |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-09-10 : 09:13:53
|
| Using CTEDECLARE @MarkSheet TABLE (subjectname VARCHAR(50),title VARCHAR(50),marks INT)INSERT INTO @MarkSheet VALUES('maths' , 'basic', 66),('maths' , 'basic', 89),('maths' , 'basic' , 73),('computer' , 'basic' , 39),('computer' , 'basic' , 78);WITH CTEAS (SELECT subjectname,title,marks, ROW_NUMBER() OVER (PARTITION BY subjectname ORDER BY marks DESC) AS Toper FROM @MarkSheet)SELECT subjectname,title,marks FROM CTE WHERE Toper = 1--------------------------http://connectsql.blogspot.com/ |
|
|
|
|
|
|
|
|
select max filed from table