Distinct & Max in one Select statement

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)

Distinct & Max in one Select statement

Author

Topic

bharatsql
Starting Member

22 Posts

Posted - 2004-10-07 : 14:34:59

Hi,
I have to make a SELECT statement wherein I need to have the records which has max start date for a given entity. Both START DATE & ENTITY are column names in two different tables which are joined by a key common to both the tables.

Pls. help.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 14:36:36

Can you provide DDL, DML and sample data for your question?

bharatsql
Starting Member

22 Posts

Posted - 2004-10-07 : 14:43:54

Hi ehorn...
Pls. find the DDL for both tables:
CREATE TABLE dbo.entity
(
entity_key int NOT NULL,
type varchar(10) NOT NULL,
last_name varchar(25) NULL,
middle_name varchar(25) NULL,
first_name varchar(25) NULL,
prefix varchar(7) NULL,
suffix varchar(40) NULL,
nickname varchar(25) NULL,
firm varchar(50) NULL,
alias varchar(8) NULL,
maiden_name varchar(25) NULL,
refno varchar(20) NULL,
ssno varchar(12) NULL,
taxid varchar(12) NULL,
medid varchar(16) NULL,
title varchar(30) NULL,
sex varchar(1) NULL
)

CREATE TABLE dbo.Primary_Contract
(
Prmy_Contract_Key int NOT NULL,
Entity_Key int NOT NULL,
Mstr_Contract_Key int NOT NULL,
Start_Date datetime NOT NULL,
End_Date datetime NULL,
Term_Reason varchar(20) DEFAULT ' ' NOT NULL,
last_change_date datetime NOT NULL,
last_change_user varchar(10) NOT NULL,
creation_date datetime NULL
)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 14:45:06

Thanks! can you also provide sample data and an expected resultset?

bharatsql
Starting Member

22 Posts

Posted - 2004-10-07 : 14:50:24

I'm unable to send the data....Can you help me with the DDL & the requirement which I have sent?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 14:51:16

Can you make some sample data up. Just enough to demonstrate your question??

bharatsql
Starting Member

22 Posts

Posted - 2004-10-07 : 14:55:45

My query is very simple & straightforward:
I need to pull all the records from the two tables (whose DDL I've already given) & which are joined over a key (ENTITY_KEY) wherein for a given ENTITY_KEY I should have the max START_DATE.

Fyi, as of now there are number of START_DATEs associated with a single ENTITY_KEY. We just need to pull a record where for a given ENTITY_KEY the STRAT_DATE is max.

Can that help? Cooking data is really difficult if not impossible.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-07 : 15:00:10

SELECT e.ENTITY_KEY, MAX(p.START_DATE)
FROM Entity e
INNER JOIN Primary_Contact p
ON e.ENTITY_KEY = p.ENTITY_KEY
GROUP BY e.ENTITY_KEY

Is that what you are looking for? It's hard to understand your problem without sample data (only 5 rows would be needed) and the expected result set using that sample data.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 15:00:25

[code]select e.*,pc.*
from dbo.entity e
join dbo.Primary_Contract pc on pc.Entity_Key = e.Entity_Key
join
(
select e.entity_key, max(pc.Start_Date) max_start_date
from dbo.entity e
join dbo.Primary_Contract pc on pc.Entity_Key = e.Entity_Key
group by e.entity_key
) d on d.entity_key = e.entity_key
[/code]

bharatsql
Starting Member

22 Posts

Posted - 2004-10-07 : 15:04:59

Thanks Tara & ehorn your replies make sense but do we do group by on Entity.Entity_Key or PC.START_DATE? Should it not be on PC.START_DATE which we anyway need the max value?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-07 : 15:06:30

Well it's hard to tell from what you've given us.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 15:12:52

based on what you have said: