Is this query possible?

Hi folks.

So I have a table which stores the following information:

a recordID (PK)
a regionID
a month (in text; such as "January 2005")
and other fields which probrably aren't important to know for this question.

The table can have more than one record for each region and month in question. The problem is for the report I want to pull up ONLY the last record for each region for the month. I got around this by doing a seperate query for each region (5 total). Now I have to do the totals for all regions combined. I could do this math using the array but I was wondering if I was able to do a query to do it for me.

So, I want to query the database and retreive 1 record. That record would contain aggragate data from each of the regions (the last record for each region and the month in question).

I might not be asking the right question, which aludes that I might not understand the problem or capabilities of SQL (newbie).

Thanks,
Douglas.If I am following you correctly, I'd try something like this:

SELECT
 myTable.regionID,
 myTable.month
FROM
 myTable
INNER JOIN
 (
 SELECT
 regionID,
 max(recordID) AS recordID
 FROM
 myTable
 GROUP BY
 regionID
 ) AS SQ ON myTable.recordID = SQ.recordID

Terri|||That was perfect. Thank you.
I forgot to say thanks. Actually, I didn't even know that was an option. I use this sort of stuff a ton now.
Douglas.

is this possible

hey all,
i have a transaction table i need to add a record to. the primary key is a 2
field column. RecordID and then the SequenceID.
How i understand it is:
To add a new record i have to
1. Find the last sequence number used
2. Then add the new record
can i do this in one stored procedure?
thanks,
rodcharWhy not use an IDENTITY column?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||Again provide DDL...
Create Table (RecordId Int, SequenceId Int
, Constraint Primary Key (RecordId, SequenceId))
Insert Table(RecordId, SequenceId)
Select RecordId, Max(SequenceId) + 1
From Table
Group By RecordId
BTW, this does have issues in a multi-user environment. If two people were t
o
execute this function at exactly the same time, they'll get the same answer
and
thus a collision. A better way would be to make a small table that stores th
e
last value used.
Thomas
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same
answer and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
..which would have the same issue -- what would stop two readers from
getting the value simultaneously?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||The problem is that this is an existing table in production.
"Adam Machanic" wrote:

> Why not use an IDENTITY column?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> 2
>
>|||Just increase Isolation Leel to Repeatable Read (or Serializeable) to preven
t
this issue from arising...
"Thomas" wrote:

> Again provide DDL...
> Create Table (RecordId Int, SequenceId Int
> , Constraint Primary Key (RecordId, SequenceId))
> Insert Table(RecordId, SequenceId)
> Select RecordId, Max(SequenceId) + 1
> From Table
> Group By RecordId
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same answe
r and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
>
> Thomas
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
>
>|||If you can withstand the entire table being locked during the insert process
,
this would also be a viable choice.
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to prev
ent
> this issue from arising...
> "Thomas" wrote:
>|||If you use a small table that stores the next value, you can lock the table
and
increment the "next" value. In essence, serializing the retrieval of the nex
t id
value. However, it does mean you may get gaps.
Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OI6wTkcQFHA.1884@.TK2MSFTNGP15.phx.gbl...
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> to
> answer and
> the
> ...which would have the same issue -- what would stop two readers from
> getting the value simultaneously?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to
prevent
> this issue from arising...
How would that prevent issues?
QA Window 1:
--
use tempdb
go
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
QA Window 2:
--
use tempdb
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
Serializable blocks only if writes have taken place.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
> "Thomas" wrote:
>
were to
answer and
stores the
is a 2|||Adam,
Yes with just a read, but if you use the relation from t he select as
Insert values, you are doing more than just a read, and qry window 2 will
block..
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Wait here while you run Qry WIndow 2 --
Commit Tran
-- ******************************
--Query Window 2
--
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Now go back and commit Query Window 1
-- ---
"Adam Machanic" wrote:

> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> prevent
> How would that prevent issues?
> QA Window 1:
> --
> use tempdb
> go
> create table x(id int)
> go
> insert x values (1)
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> QA Window 2:
> --
> use tempdb
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> Serializable blocks only if writes have taken place.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> were to
> answer and
> stores the
> is a 2
>
>