SQL Version of MS Access Query

Hello, I am slowy moving away from MS Access towards SQL server but I am struggling with this.

I want to be able to selcect the LAST value when using a GROUP BY/HAVING statement. e.g.

Job      Application      Retention
A      1      1000
B      1      200
C      1      500
A      2      2000
B      2      300
C      2      750
A      3      750

Using MS Access SQL I would issuse the following command:

SELECT EERetention.Job, Last(EERetention.Application) AS LastOfApplication, Last(EERetention.Retention) AS LastOfRetention
FROM EERetention
GROUP BY EERetention.Job
ORDER BY EERetention.Job, Last(EERetention.Application);

To Give me:

Job      LastOfApplication      LastOfRetention
A      3      750
B      2      300
C      2      750

How would this translate to SLQ?

Thank you
hotkeysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bradleys40Commented:
you could use the
select top 1
in your order  by specify DESC

SELECT top 1EERetention.Job, Last(EERetention.Application) AS LastOfApplication, Last(EERetention.Retention) AS LastOfRetention
FROM EERetention
GROUP BY EERetention.Job
ORDER BY EERetention.Job, Last(EERetention.Application) DESC;
0
Ashish PatelCommented:
SELECT EERetention.Job, Min(EERetention.Application) AS LastOfApplication, Min(EERetention.Retention) AS LastOfRetention
FROM EERetention
GROUP BY EERetention.Job
ORDER BY EERetention.Job
0
ee_rleeCommented:
try this
SELECT EERetention.Job, EERetention2.MinApp AS LastOfApplication, EERetention.Retention AS LastOfRetention
 
FROM EERetention LEFT JOIN (SELECT X.Job, MIN(X.Application) AS MinApp FROM EERetention X GROUP BY X.JOB) EERetention2 ON (EERetention.Job=EERetention2.Job)
 
WHERE EERetention.Application=EERetention2.MinApp
 
ORDER BY EERetention.Job;

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

dportasCommented:
The LAST function in Access returns the values from some random row in each set of rows being grouped. I suspect that many people who use this function in Access don't realise that fact even though it's clearly spelt out in the documentation.

With good reason SQL makes it a little harder to generate random results. Are you sure that's what you want or did you intend something a bit more specific? If so, please explain what you mean by "last" in this instance. Rows in a table have no intrinsic ordering so the concepts of first, last or Nth are meaningless unless you can define them in terms of data in columns.
0
hotkeysAuthor Commented:
Thanks you all for your suggestions, as 'dportas' suggested I need to clarify what I am after as I do not want a random result.
However I must add that the query in Access using the LAST expression works perfectly.

I need to return the Retention held for each job for the last/greatest application.

Apologies if my original question was not clear.
0
ee_rleeCommented:
try this code
SELECT EERetention.Job, EERetention2.MaxApp AS LastOfApplication, EERetention.Retention AS LastOfRetention
 
FROM EERetention LEFT JOIN (SELECT X.Job, MAX(X.Application) AS MaxApp FROM EERetention X GROUP BY X.JOB) EERetention2 ON (EERetention.Job=EERetention2.Job)
 
WHERE EERetention.Application=EERetention2.MaxApp
 
ORDER BY EERetention.Job;

Open in new window

0
dportasCommented:
Try:

SELECT Job, Application, Retention
FROM EERetention e
WHERE Application =
(SELECT MAX(Application)
 FROM EERetention
 WHERE Job = e.Job);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hotkeysAuthor Commented:
Thanks everyone for you help, I have got 'dportas' solution working on our life SQL data now. A little more auditing required but looking very good.

Not sure what the protocol is now for assigning point?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.