using GROUP and MAX with other fields

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

SQL Server Development (2000)

using GROUP and MAX with other fields

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-26 : 09:17:51

Andrew Mirsky writes "Given these entries in my table:

id date value
4 2002-1-1 zero
4 2002-2-26 first
4 2002-2-28 second

I am trying to write a SQL query to get the value at the maximum date. So I first tried this query and got:

select MAX(date),value from MY_TBL group by id

2002-2-28 first

When it does the aggregate function of group by and then chooses a maximum date, it disreguards other fields. It then selects the first entry to show
the value of.

So it is even worse than that, because what I would really like to do is, get the latest value on a given day.

If I do the query on this example, I get:

id date value
6 2002-2-2 zero
6 2002-2-16 first
6 2002-2-18 second
6 2002-3-2 third

select MAX(date),value from MY_TBL where date < '2002-3-2' group by id

This result doesn't return me anything! Which would lead me to the same
conclusion above. looks like it groups everything first, decides the maximum value
(in this case '2002-3-2' and then applies the where clause and since
'2002-3-2' is not less than the date i was looking for it doesn't report
anything!

Is their a way to change the order of operations so that is first evaluates the where clause and then does the group/max?

A way using a single query to get the value at a maximum date?

Thanks for the help. "

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-03-26 : 09:22:07

SELECT TOP 1 Value
FROM table
ORDER BY DateField DESC

Peter

Jay99

468 Posts

Posted - 2002-03-26 : 10:05:01


select
    t.[id],
    t.[date],
    t.value
from
    mytable t
    inner join (
        select
            [id],
            max([date]) as maxdate
        from
            mytable
        group by [id] ) d
        on ( t.[id] = d.[id] and
             t.[date] = d.[date] )
where
    t.[date] < '2002-3-2'

First, 'id' and 'date' are reserved words and 'value' is very nondescriptive. Maybe this is just and example DDL, but you should pay better attention to design . . .

Second, when you say...

quote:

...to get the value at the maximum date

...I am assuming you mean the maximum date for that ID (hence your attempted GROUP BY. If this incorrect, please refer to post by P. Dutchy . . .

Third, remember that when doing date comparisons, SQL Server compares date and time. . .that is assuming your [date] field is a (small)datetime datatype . . .

Finally . . .

quote:

Is their a way to change the order of operations so that is first evaluates the where clause and then does the group/max?

. . . no . . .

Jay
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources