Using DISTINCT and MAX - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Using DISTINCT and MAX

Author

Topic

bellboy
Starting Member

6 Posts

Posted - 2007-06-29 : 14:21:32

I have a table called Reps as follows:

-----------------------------------------
RepNumber Repname RepManager Yr Mth
-----------------------------------------

I want to be able to grab all distinct Reps(by Number Name and Manager), reps can have multiple Numbers and Managers ofter change and Repnames are sometimes misspelled so all 3 must be distinct.

But From that I want to be able to only get the record that is most recent, ie Max Yr and Max Month.

I tried:

SELECT PEIN, Representative, Manager, Yr, mth=max(mth), 'Internal', 'OR'
FROM Rep_V_Prod_Ont
group by PEIN,Representative, Manager, Yr, mth

I know this is silly and simple but for some reason, I can't get it down.

Thanks
Mike matta

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-29 : 14:28:41

posting some sample and expected data can help.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bellboy
Starting Member

6 Posts

Posted - 2007-06-29 : 14:32:36

Not sure what you mean,,, but I finally got it working:

SELECT PEIN, Representative, Manager, Yr, mth=max(mth), 'Internal', 'OR'
FROM Rep_V_Prod_Ont
group by PEIN,Representative, Manager, Yr

I deffinitely feel like an idiot :)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-29 : 14:35:35

good you got it working. I meant to say post some sample data so we can see what you are trying to do. Sometimes (actually mostly) its easy to understand data and numbers than the explanation

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 23:41:28

quote:
Originally posted by bellboy

Not sure what you mean,,, but I finally got it working:

SELECT PEIN, Representative, Manager, Yr, mth=max(mth), 'Internal', 'OR'
FROM Rep_V_Prod_Ont
group by PEIN,Representative, Manager, Yr

I deffinitely feel like an idiot :)

Is this different from your original query?

This is how to post a question
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources