Group By 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

General SQL Server Forums

New to SQL Server Programming

Group By question

Author

Topic

Passacaglia
Starting Member

3 Posts

Posted - 2011-06-17 : 12:34:09

Hi!

I have data that looks like:

Polnum Date Status
1 1/1/2010 Open
1 4/8/2010 Closed

and so on.

I want to group by policy number, get the most recent date (which I can do using max), but also get the status that goes with the most recent date. This has been easy to do for me in Access with subqueries, but I'm having trouble in SQL. Any help would be appreciated. Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-17 : 12:57:38

select polnum, date, status
from
(
select *, seq = row_number() over (partition by polnum order by date desc)
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-17 : 13:06:42

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

CREATE TABLE PolicyHistory
(policy_nbr INTEGER NT NULL,
posting_date DATE NOT NULL,
PRIMARY KEY (policy_nbr, posting_date),
policy_status CHAR (5) DEFAULT 'Open' NOT NULL
CHECK (policy_status IN('Open', 'Close'));

SELECT X.policy_nbr, X.last_posting_date, X.policy_status
FROM (SELECT policy_nbr, posting_date, policy_status,
MAX(posting_date)
OVER (PARTITION BY policy_nbr) AS last_posting_date
FROM PolicyHistory)
AS X
WHERE X.posting_date = X.last_posting_date;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Passacaglia
Starting Member

3 Posts

Posted - 2011-06-17 : 15:14:06

quote:
select polnum, date, status
from
(
select *, seq = row_number() over (partition by polnum order by date desc)
) a
where seq = 1

Thanks, this is perfect! If I had another field that I wanted to sum up for both rows here, how would I be able to do that?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-17 : 15:19:44

The PARTITION BY part of the row_number() function is what determines the groups to order. So if you did partition by polnum,anotherColumn order by date desc

You'd get sequences that start at 1 and then increment for each PolNum,anotherColumn group. You can add an entirely new row_number() expression also, if you wish.

Jim

Everyday I learn something that somebody else already knew

Passacaglia
Starting Member

3 Posts

Posted - 2011-06-17 : 15:35:49

Sorry, that probably wasn't clear.

I have another field in my data, so it looks like:

Polnum Date Status Amount
1 1/1/2010 Open 100
1 4/8/2010 Closed 200

So I want to select the status where seq=1, but I want to sum the amount for all rows.

Maybe that's what you were answering and I didn't understand!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-17 : 21:23:27

SELECT X.policy_nbr, X.last_posting_date, X.policy_status, X.posting_amt_tot
FROM (SELECT policy_nbr, posting_date, policy_status,
MAX(posting_date)
OVER (PARTITION BY policy_nbr) AS last_posting_date,
SUM(posting_amt) OVER (posting_amt_totpolicy_nbr) AS posting_amt_tot
FROM PolicyHistory)
AS X
WHERE X.posting_date = X.last_posting_date;

See why I like to use the new "<aggregate>(<expr>) OVER (PARTITION BY ..)" syntax? It is easy to add more things at different aggregations to a query.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources