Select max value for given value 1

[ethorn10]

Ok, maybe the subject isn't the most descriptive in the world, but my issue is this:

I have a table with several fields, but the only fields we care about are an autonumber primary key and an integer foreign key. I want to get the latest (maximum) primary key for any foreign key. Sample data might be:

pk_ID     fk_ID
---------------
  1         10
  2         11
  3         12
  4         11
  5         11
  6         13
  7         10

So I would want a result set of:

max_pk_ID    fk_ID
------------------
  7           10
  5           11
  3           12
  6           13

Please tell me this is easy as I've completely gone brain-dead. I look forward to your help...

 

[jbenson001]

Try This:

Select Max(pk_ID), fk_ID
From <table>
Group By fk_ID
[code]

 

[ethorn10]

I think I have tried that one before and got unexpected results but I'll give it another go. Any other ideas while I'm at it?

 

[jbenson001]

No other ideas, I made a table with your data, ran my query and got your expected output. If you are getting different results, it could be a data problem. But, using the test data you posted, it worked fine.

 

[ethorn10]

It's amazing how wrong I can be when I just glance over something. Your solution worked! I was doing a GROUP BY pk_ID because that's how I had it in my head and just assumed that's what you typed, but after looking again...you have fixed my issue.

Thanks..a star for you.

 

[jbenson001]

Hey.. glad it worked you and understand it now..
Jim..

P.S.. Thanks for the star...