|
Home > Archive > MS SQL Server New Users > March 2005 > Max() question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| I have a table with the following fields:
ID
SalesAmt
SalesMonth
Every month the table gets updated with sales values from the previous
month. If a particular ID doesn't have sales, there is no record.
I need a query/view showing ONLY the records for the latest month posted for
each ID. So for ID1 it might be the January record (shown as "01" in the
SalesMonth column) or for ID2 it might be March.
Anyone keeping tabs on who is posting what knows I'm really new to "true"
SQL and only have Access experience under my belt. It's all been pretty
ugly, actually. ; ( Anyway, I've tried using some groupings and MAX, but
because the Sales amount varies, I can't seem to pull that value in without
pulling in ALL the records (due to grouping issues).
Do I have to do this in two steps somehow? For example, create a list of
the IDs and their latest month, and use THAT in a secondary query to pull
the rest of the values I need?
As always, I appreciate the help.
Just to be clear, here's an example of the full list:
ID $$ MO
1 5.00 01
2 8.00 01
3 2.00 01
1 2.00 02
3 6.00 02
My resulting query would show only the LAST/MAX record for each ID, AND show
the $$/Amount field:
ID $$ MO
2 8.00 01
1 2.00 02
3 6.00 02
| |
| Tom Moreau 2005-03-30, 7:04 pm |
| Try:
select
*
from
MyTable o
where
o.SalesMonth =
(
select
max (i.SalesMonth)
from
MyTable i
where
i.ID = o.ID
)
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"r" <r@r.com> wrote in message news:uJcjzkYNFHA.3076@TK2MSFTNGP14.phx.gbl...
I have a table with the following fields:
ID
SalesAmt
SalesMonth
Every month the table gets updated with sales values from the previous
month. If a particular ID doesn't have sales, there is no record.
I need a query/view showing ONLY the records for the latest month posted for
each ID. So for ID1 it might be the January record (shown as "01" in the
SalesMonth column) or for ID2 it might be March.
Anyone keeping tabs on who is posting what knows I'm really new to "true"
SQL and only have Access experience under my belt. It's all been pretty
ugly, actually. ; ( Anyway, I've tried using some groupings and MAX, but
because the Sales amount varies, I can't seem to pull that value in without
pulling in ALL the records (due to grouping issues).
Do I have to do this in two steps somehow? For example, create a list of
the IDs and their latest month, and use THAT in a secondary query to pull
the rest of the values I need?
As always, I appreciate the help.
Just to be clear, here's an example of the full list:
ID $$ MO
1 5.00 01
2 8.00 01
3 2.00 01
1 2.00 02
3 6.00 02
My resulting query would show only the LAST/MAX record for each ID, AND show
the $$/Amount field:
ID $$ MO
2 8.00 01
1 2.00 02
3 6.00 02
| |
|
| Hi Tom,
I don't understand what the "o" and "i" would be ... after MyTable.
??
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:#D1yuwYNFHA.568@TK2MSFTNGP09.phx.gbl...
> Try:
>
> select
> *
> from
> MyTable o
> where
> o.SalesMonth =
> (
> select
> max (i.SalesMonth)
> from
> MyTable i
> where
> i.ID = o.ID
> )
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "r" <r@r.com> wrote in message
news:uJcjzkYNFHA.3076@TK2MSFTNGP14.phx.gbl...
> I have a table with the following fields:
>
> ID
> SalesAmt
> SalesMonth
>
> Every month the table gets updated with sales values from the previous
> month. If a particular ID doesn't have sales, there is no record.
>
> I need a query/view showing ONLY the records for the latest month posted
for
> each ID. So for ID1 it might be the January record (shown as "01" in the
> SalesMonth column) or for ID2 it might be March.
>
> Anyone keeping tabs on who is posting what knows I'm really new to "true"
> SQL and only have Access experience under my belt. It's all been pretty
> ugly, actually. ; ( Anyway, I've tried using some groupings and MAX,
but
> because the Sales amount varies, I can't seem to pull that value in
without
> pulling in ALL the records (due to grouping issues).
>
> Do I have to do this in two steps somehow? For example, create a list of
> the IDs and their latest month, and use THAT in a secondary query to pull
> the rest of the values I need?
>
> As always, I appreciate the help.
>
> Just to be clear, here's an example of the full list:
>
> ID $$ MO
> 1 5.00 01
> 2 8.00 01
> 3 2.00 01
> 1 2.00 02
> 3 6.00 02
>
>
> My resulting query would show only the LAST/MAX record for each ID, AND
show
> the $$/Amount field:
>
>
> ID $$ MO
> 2 8.00 01
> 1 2.00 02
> 3 6.00 02
>
>
| |
|
| Oh, I see... they're the same. It almost works. It shows ALL of the cases
where the latest month is "02", but if anyone's latest month is "01" they
are not included in the results.
Also, what is the i.ID = o.ID for ? I mean, how do I specify the difference
between the first occurance of the table ID and the second? Don't they just
appear the same?
Thanks for the help.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:#D1yuwYNFHA.568@TK2MSFTNGP09.phx.gbl...
> Try:
>
> select
> *
> from
> MyTable o
> where
> o.SalesMonth =
> (
> select
> max (i.SalesMonth)
> from
> MyTable i
> where
> i.ID = o.ID
> )
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "r" <r@r.com> wrote in message
news:uJcjzkYNFHA.3076@TK2MSFTNGP14.phx.gbl...
> I have a table with the following fields:
>
> ID
> SalesAmt
> SalesMonth
>
> Every month the table gets updated with sales values from the previous
> month. If a particular ID doesn't have sales, there is no record.
>
> I need a query/view showing ONLY the records for the latest month posted
for
> each ID. So for ID1 it might be the January record (shown as "01" in the
> SalesMonth column) or for ID2 it might be March.
>
> Anyone keeping tabs on who is posting what knows I'm really new to "true"
> SQL and only have Access experience under my belt. It's all been pretty
> ugly, actually. ; ( Anyway, I've tried using some groupings and MAX,
but
> because the Sales amount varies, I can't seem to pull that value in
without
> pulling in ALL the records (due to grouping issues).
>
> Do I have to do this in two steps somehow? For example, create a list of
> the IDs and their latest month, and use THAT in a secondary query to pull
> the rest of the values I need?
>
> As always, I appreciate the help.
>
> Just to be clear, here's an example of the full list:
>
> ID $$ MO
> 1 5.00 01
> 2 8.00 01
> 3 2.00 01
> 1 2.00 02
> 3 6.00 02
>
>
> My resulting query would show only the LAST/MAX record for each ID, AND
show
> the $$/Amount field:
>
>
> ID $$ MO
> 2 8.00 01
> 1 2.00 02
> 3 6.00 02
>
>
| |
| Tom Moreau 2005-03-30, 7:35 pm |
| The o and the i are aliases so you can tell the two "copies" of the table
apart. I don't understand why you are getting "incorrect" results. I have
created a table and filled it with your data as follows. The code gives the
correct results.
create table MyTable
(
ID int not null
, salesMonth char (2) not null
, SalesAmt money not null
, primary key (ID, SalesMonth)
)
go
insert MyTable values (1, '01', $5.00)
insert MyTable values (2, '01', $8.00)
insert MyTable values (3, '01', $2.00)
insert MyTable values (1, '02', $2.00)
insert MyTable values (3, '02', $6.00)
go
select
*
from
MyTable o
where
o.SalesMonth =
(
select
max (i.SalesMonth)
from
MyTable i
where
i.ID = o.ID
)
go
drop table MyTable
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"r" <r@r.com> wrote in message news:ObD3iAZNFHA.1308@TK2MSFTNGP15.phx.gbl...
Oh, I see... they're the same. It almost works. It shows ALL of the cases
where the latest month is "02", but if anyone's latest month is "01" they
are not included in the results.
Also, what is the i.ID = o.ID for ? I mean, how do I specify the difference
between the first occurance of the table ID and the second? Don't they just
appear the same?
Thanks for the help.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:#D1yuwYNFHA.568@TK2MSFTNGP09.phx.gbl...
> Try:
>
> select
> *
> from
> MyTable o
> where
> o.SalesMonth =
> (
> select
> max (i.SalesMonth)
> from
> MyTable i
> where
> i.ID = o.ID
> )
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "r" <r@r.com> wrote in message
news:uJcjzkYNFHA.3076@TK2MSFTNGP14.phx.gbl...
> I have a table with the following fields:
>
> ID
> SalesAmt
> SalesMonth
>
> Every month the table gets updated with sales values from the previous
> month. If a particular ID doesn't have sales, there is no record.
>
> I need a query/view showing ONLY the records for the latest month posted
for
> each ID. So for ID1 it might be the January record (shown as "01" in the
> SalesMonth column) or for ID2 it might be March.
>
> Anyone keeping tabs on who is posting what knows I'm really new to "true"
> SQL and only have Access experience under my belt. It's all been pretty
> ugly, actually. ; ( Anyway, I've tried using some groupings and MAX,
but
> because the Sales amount varies, I can't seem to pull that value in
without
> pulling in ALL the records (due to grouping issues).
>
> Do I have to do this in two steps somehow? For example, create a list of
> the IDs and their latest month, and use THAT in a secondary query to pull
> the rest of the values I need?
>
> As always, I appreciate the help.
>
> Just to be clear, here's an example of the full list:
>
> ID $$ MO
> 1 5.00 01
> 2 8.00 01
> 3 2.00 01
> 1 2.00 02
> 3 6.00 02
>
>
> My resulting query would show only the LAST/MAX record for each ID, AND
show
> the $$/Amount field:
>
>
> ID $$ MO
> 2 8.00 01
> 1 2.00 02
> 3 6.00 02
>
>
| |
|
| I'm guessing the problem is that i didn't understand the o and i bit. let
me add that.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:eCYQxGZNFHA.3844@TK2MSFTNGP14.phx.gbl...
> The o and the i are aliases so you can tell the two "copies" of the table
> apart. I don't understand why you are getting "incorrect" results. I
have
> created a table and filled it with your data as follows. The code gives
the
> correct results.
>
> create table MyTable
> (
> ID int not null
> , salesMonth char (2) not null
> , SalesAmt money not null
> , primary key (ID, SalesMonth)
> )
> go
>
> insert MyTable values (1, '01', $5.00)
> insert MyTable values (2, '01', $8.00)
> insert MyTable values (3, '01', $2.00)
> insert MyTable values (1, '02', $2.00)
> insert MyTable values (3, '02', $6.00)
> go
>
> select
> *
> from
> MyTable o
> where
> o.SalesMonth =
> (
> select
> max (i.SalesMonth)
> from
> MyTable i
> where
> i.ID = o.ID
> )
>
> go
>
> drop table MyTable
>
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "r" <r@r.com> wrote in message
news:ObD3iAZNFHA.1308@TK2MSFTNGP15.phx.gbl...
> Oh, I see... they're the same. It almost works. It shows ALL of the
cases
> where the latest month is "02", but if anyone's latest month is "01" they
> are not included in the results.
>
> Also, what is the i.ID = o.ID for ? I mean, how do I specify the
difference
> between the first occurance of the table ID and the second? Don't they
just
> appear the same?
>
> Thanks for the help.
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:#D1yuwYNFHA.568@TK2MSFTNGP09.phx.gbl...
> news:uJcjzkYNFHA.3076@TK2MSFTNGP14.phx.gbl...
> for
the[color=darkred]
"true"[color=darkred]
> but
> without
of[color=darkred]
pull[color=darkred]
> show
>
>
| |
|
| Yep, that was it.
Thanks SO much, Tom. I've been paining over this stuff (not this specific
problem) for 2 days - it's really wonderful to have some help & guidance.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:eCYQxGZNFHA.3844@TK2MSFTNGP14.phx.gbl...
> The o and the i are aliases so you can tell the two "copies" of the table
> apart. I don't understand why you are getting "incorrect" results. I
have
> created a table and filled it with your data as follows. The code gives
the
> correct results.
>
> create table MyTable
> (
> ID int not null
> , salesMonth char (2) not null
> , SalesAmt money not null
> , primary key (ID, SalesMonth)
> )
> go
>
> insert MyTable values (1, '01', $5.00)
> insert MyTable values (2, '01', $8.00)
> insert MyTable values (3, '01', $2.00)
> insert MyTable values (1, '02', $2.00)
> insert MyTable values (3, '02', $6.00)
> go
>
> select
> *
> from
> MyTable o
> where
> o.SalesMonth =
> (
> select
> max (i.SalesMonth)
> from
> MyTable i
> where
> i.ID = o.ID
> )
>
> go
>
> drop table MyTable
>
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "r" <r@r.com> wrote in message
news:ObD3iAZNFHA.1308@TK2MSFTNGP15.phx.gbl...
> Oh, I see... they're the same. It almost works. It shows ALL of the
cases
> where the latest month is "02", but if anyone's latest month is "01" they
> are not included in the results.
>
> Also, what is the i.ID = o.ID for ? I mean, how do I specify the
difference
> between the first occurance of the table ID and the second? Don't they
just
> appear the same?
>
> Thanks for the help.
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:#D1yuwYNFHA.568@TK2MSFTNGP09.phx.gbl...
> news:uJcjzkYNFHA.3076@TK2MSFTNGP14.phx.gbl...
> for
the[color=darkred]
"true"[color=darkred]
> but
> without
of[color=darkred]
pull[color=darkred]
> show
>
>
| |
| Tom Moreau 2005-03-30, 8:01 pm |
| That's what we're here for. :-)
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"r" <r@r.com> wrote in message
news:%238PmJOZNFHA.2132@TK2MSFTNGP14.phx.gbl...
Yep, that was it.
Thanks SO much, Tom. I've been paining over this stuff (not this specific
problem) for 2 days - it's really wonderful to have some help & guidance.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:eCYQxGZNFHA.3844@TK2MSFTNGP14.phx.gbl...
> The o and the i are aliases so you can tell the two "copies" of the table
> apart. I don't understand why you are getting "incorrect" results. I
have
> created a table and filled it with your data as follows. The code gives
the
> correct results.
>
> create table MyTable
> (
> ID int not null
> , salesMonth char (2) not null
> , SalesAmt money not null
> , primary key (ID, SalesMonth)
> )
> go
>
> insert MyTable values (1, '01', $5.00)
> insert MyTable values (2, '01', $8.00)
> insert MyTable values (3, '01', $2.00)
> insert MyTable values (1, '02', $2.00)
> insert MyTable values (3, '02', $6.00)
> go
>
> select
> *
> from
> MyTable o
> where
> o.SalesMonth =
> (
> select
> max (i.SalesMonth)
> from
> MyTable i
> where
> i.ID = o.ID
> )
>
> go
>
> drop table MyTable
>
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "r" <r@r.com> wrote in message
news:ObD3iAZNFHA.1308@TK2MSFTNGP15.phx.gbl...
> Oh, I see... they're the same. It almost works. It shows ALL of the
cases
> where the latest month is "02", but if anyone's latest month is "01" they
> are not included in the results.
>
> Also, what is the i.ID = o.ID for ? I mean, how do I specify the
difference
> between the first occurance of the table ID and the second? Don't they
just
> appear the same?
>
> Thanks for the help.
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:#D1yuwYNFHA.568@TK2MSFTNGP09.phx.gbl...
> news:uJcjzkYNFHA.3076@TK2MSFTNGP14.phx.gbl...
> for
the[color=darkred]
"true"[color=darkred]
> but
> without
of[color=darkred]
pull[color=darkred]
> show
>
>
|
|
|
|
|