We help IT Professionals succeed at work.

SQL Version of MS Access Query

hotkeys
hotkeys asked
on
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
Comment
Watch Question

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;
SELECT EERetention.Job, Min(EERetention.Application) AS LastOfApplication, Min(EERetention.Retention) AS LastOfRetention
FROM EERetention
GROUP BY EERetention.Job
ORDER BY EERetention.Job
Top Expert 2008

Commented:
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

Commented:
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.

Author

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.
Top Expert 2008
Commented:
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

Commented:
Try:

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

Author

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?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.