Select SQL syntax question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Select SQL syntax question

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-06-04 : 11:24:01

I need to select a record by Max(SetDate) which requires me to use a group by. Problem is I want to get the Amount of the record selected by Max(SetDate) but don't want to aggregate or anything else to Amount. Table looks like this: Id, SetDate, Amount.

I need to setup a subqeury but not sure how to go about it. Here is what I have so far which gives me an error on Amount because it is not in Group By..

Select Id, Max(SetDate), Amount From tblDaysAmount Where Id = 1234 Group By Id

Your advice would be appreciated.
vmon

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-04 : 11:59:38

[Id] is not a very good column name as it's a reserved word...anyway how about:


SELECT [Id], SetDate, Amount 
  FROM tblDaysAmount 
 WHERE [Id] = 1234 
   AND SetDate = (SELECT Max(SetDate) 
		    FROM tblDaysAmount 
 		   WHERE [Id] = 1234)

Brett

8-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-04 : 12:02:02

Use a correlated subquery....


select
    Id,
    SetDate,
    Amount
from
    tblDaysAmount da
where
    Id = 1234 and
    not exists (
        select 1
        from
            tblDaysAmount
        where
            da.Id = id and
            da.SetDate < SetDate)

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-04 : 12:05:58

Wow, I didn't read that much in to it, but only s/he can say...certanly makes more sense than mine...

Brett

8-)

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-06-04 : 12:34:06

Brett,

Thanks a bunch. I shortened the column names for posting. What I needed most was the concept behind such a query.

Thanks again,
vmon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources