• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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
0
hotkeys
Asked:
hotkeys
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now