query help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-05-26 : 07:14:22

Hi Guys.

Can someone please help me out with a SQL query that I have.

I have a table QueryDetail.

This table has several rows in it.

Fields are as follows.
QueryDetailID
QueryID
UserName
QueryDetail
Proity
Status
CreateDate.

How can i retrieve all fields but only see the rows of each QueryID that has the greatest CreateDate.

Hope that makes sense.

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-26 : 07:22:46


select t1.* from QueryDetail as t1 inner join
(
	select QueryID,max(CreateDate) as CreateDate from QueryDetail
) as t2
on t1.QueryID=t2.QueryID and t1.CreateDate=t2.CreateDate

Madhivanan

Failing to plan is Planning to fail

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-05-26 : 07:35:03

quote:
Originally posted by madhivanan

select t1.* from QueryDetail as t1 inner join
(
select QueryID,max(CreateDate) as CreateDate from QueryDetail
) as t2
on t1.QueryID=t2.QueryID and t1.CreateDate=t2.CreateDate

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan.

But i should have mentioned that im pretty new at this.

I get the following error.

SELECT t1.QueryDetailID, t1.QueryID, t1.UserID, t1.Action, t1.AdviseType, t1.AdviseDevice, t1.AdviseProperty, t1.ReferrerID, t1.ReferrerComment,
t1.SolvedComment, t1.SolvedType, t1.ReferBackComment, t1.createdate
FROM QueryDetail AS t1 INNER JOIN
(SELECT QueryID, MAX(createdate) AS CreateDate
FROM QueryDetail) AS t2 ON t1.QueryID = t2.QueryID AND t1.createdate = t2.CreateDate

Msg 8120, Level 16, State 1, Line 1
Column 'QueryDetail.QueryID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 11:43:28

[code]SELECT t1.QueryDetailID, t1.QueryID, t1.UserID, t1.Action, t1.AdviseType, t1.AdviseDevice, t1.AdviseProperty, t1.ReferrerID, t1.ReferrerComment,
t1.SolvedComment, t1.SolvedType, t1.ReferBackComment, t1.createdate
FROM QueryDetail AS t1 INNER JOIN
(SELECT QueryID, MAX(createdate) AS CreateDate
FROM QueryDetail
GROUP BY QueryID) AS t2
ON t1.QueryID = t2.QueryID
AND t1.createdate = t2.CreateDate
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-27 : 02:39:45

quote:

Originally posted by visakh16

SELECT t1.QueryDetailID, t1.QueryID, t1.UserID, t1.Action, t1.AdviseType, t1.AdviseDevice, t1.AdviseProperty, t1.ReferrerID, t1.ReferrerComment, 
t1.SolvedComment, t1.SolvedType, t1.ReferBackComment, t1.createdate
FROM QueryDetail AS t1 INNER JOIN
(SELECT QueryID, MAX(createdate) AS CreateDate
FROM QueryDetail
GROUP BY QueryID) AS t2 
ON t1.QueryID = t2.QueryID 
AND t1.createdate = t2.CreateDate

Thanks. I missed it

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 09:49:31

No problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources