create a view with MAX and group by

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)

create a view with MAX and group by

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-26 : 07:33:26

dee writes "I need to create a view. Data looks like below.

YEAR UNITS ACCOUNT#
2001 200 abc
2005 100 dvf
2003 50 aaa

I need to find out the TOTAL for the most recent year only grouping by ACCOUN#.

when I create view ...

CREATE view vw_test
as select MAX(year), account#, units
FROM table_name
GROUP BY account#

it gives me an error telling that I need to group by UNITS as well. but it is not a want. Please help.."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-26 : 08:31:52

quote:
I need to find out the TOTAL for the most recent year only grouping by ACCOUN#.

Total what? Units?


select
    d.year,
    sum(d.units) as SumUnits,
    d.Account#
from
    dee d
where
    not exists (
        select 1
        from
            dee
        where
            year > d.year)
group by
     year,
     account#

Jay White
{0}

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-26 : 08:33:25

this should work - but I'm sure the brighter people will have a better way. In the meantime, tho ...

CREATE view vw_test
as year, account#, sum(units)
FROM table_name
where year = (select max(year) from table_name)
GROUP BY year,account#

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-26 : 08:45:09

Wanderer, your solution will return data from the most recent year across all accounts. So, with the given sample data, your solution will only return data for account# dvf. Mine, will return data for the most recent year for each account.

It is unclear from the requirements which is correct ...

Jay White
{0}

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-26 : 08:56:22

I did a quick "execution path" comparison, and was surprised to see that Page47's was ESTIMATED at 59% versus 40%. Page47 - do you think that would change with real volumes ? I only inserted 3 rows ...

Ciao

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-26 : 09:02:14

Hmmm ... I see that now - probably why execution path thinks mine is better ...

I *think* he/she wanted maximum year , but then again, who knows ... hopefully they'll post again ...

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources