Thanx a lot jeff. It worked.
Can u tell me some site/stuff to hv a better understanding of sql.

Regards,
Rohit

-----Original Message-----
From: Jeff Mason [mailto:je.mason@a...]
Sent: Thursday, December 05, 2002 5:26 PM
To: sql language
Subject: [sql_language] RE: GROUP BY CLAUSE COMPLEXITY


First off, it appears to me that this design isn't normalized.  The RESNAME
column will be duplicated for each distinct COSTDATE, so that's a dead
giveaway.  Also, you complain about the fact that in some queries you get
multiple names.  That's why.

This sort of design is one of the reasons why people can get into trouble
just slapping on an identity column as the primary key, without thinking
through what the *natural* key is for the data.

You would probably do better to split off the name and ID into one table,
then have another table, keyed by ID containing the date and cost.

But, try something like:

	select RESNAME, RESCOST, COSTDATE from RESOURCES R1
	where COSTDATE
		(select max(COSTDATE) from RESOURCES R2
		 where R1.RESNAME=R2.RESNAME);

--
Jeff Mason              Custom Apps, Inc.
Jeff@c...


-----Original Message-----
From: Rohit Arora [mailto:rohit_arora@i...]
Sent: Thursday, December 05, 2002 5:51 AM
To: sql language
Subject: [sql_language] GROUP BY CLAUSE COMPLEXITY


I HAVE A RESOURCE TABLE CONTAINING IDENTITY COLUMN(UNIQUENESS), RESOURCE
NAME, RESOURCE COST, COSTDATE.

NOW I WANT TO FIND A ROW WITH AN ENTRY FOR RESOURCE WITH MAX(COSTDATE) AND
THE COST ON THAT DATE.

I MADE A QUERY LIKE

SELECT RESNAME,RESCOST,MAX(COSTDATE) GROUP BY RESNAME

THEN IT GIVES ERROR THAT RESCOST MUS ALSO HAVE TO BE GROUPED.

IF I DO LIKE
SELECT RESNAME,RESCOST,MAX(COSTDATE) GROUP BY RESNAME,RESCOST
IT GIVES MULTIPLE ROWS FOR SAME RESNAME(OR CAN SAY ERRONEOUS RESULT)


IF I MAKE A QUERY

SELECT RESNAME,MAX(COSTDATE) GROUP BY RESNAME
IT GIVES THE ROW WHICH I NEEDED BUT NOW DONT HAVE COST FIELD.



Regards,
Rohit