Help needed in returing results

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help needed in returing results

Author

Topic

abdou_kadri
Starting Member

13 Posts

Posted - 2009-07-02 : 09:59:58

Hi all,

I have the following scenario that is the key of my big problem
I have for example the following table:


CREATE TABLE [classes_grade](
	[CLASS_NAME] [nvarchar](10) NOT NULL,
	[STUDENT_NAME] [nvarchar](10) NOT NULL,
        [COURSE_NAME] [nvarchar](10) NULL,
	[GRADE] [int] NULL,
	[DATE] [datetime] NULL,
        )

I want to get the highest grade in each class in the results and i have to return the class_name, student_name, grade and date cloumns

I did the following query but seems not working. It returns more than one record for each class.


SELECT lq.CLASS_NAME, 
	lq.STUDENT_NAME,
	lq.GRADE, 
	lq.DATE 
      FROM classes_grade lq WITH(NOLOCK)  
      WHERE lq.GRADE  in(
                SELECT  Max(t.watch_timestamp) 
		FROM classes_grade as t WITH(NOLOCK)
		group by t.CLASS_NAME
           )

I'll be gratefull for any reply and suggestion!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 10:02:35

[code]
SELECT lq.CLASS_NAME,
lq.STUDENT_NAME,
lq.GRADE,
lq.DATE
FROM
(
SELECT lq.CLASS_NAME,
lq.STUDENT_NAME,
lq.GRADE,
lq.DATE,
row_no = row_number over (partition by CLASS_NAME order by GRADE desc)
FROM classes_grade lq WITH(NOLOCK)
) lq
where lq.row_no = 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-02 : 10:16:56

you got a readymade solution, but please read about SQL basics

WHERE lq.GRADE in(
SELECT Max(t.watch_timestamp)

what are you trying to do here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 11:23:18

also see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources