I need to select a list of managers from the managerNames table with the most recent effectiveDate. I can do this with the query below:
select mn.*
from (select m.mgrId, max(m.effectiveDate) as effDate from managerNames m
group by m.mgrId) d, managerNames mn
where mn.mgrId = d.mgrId
and mn.effectiveDate = d.effDate
Column Defintions:
Id (Pk, NotNull)
MgrId (Fk, NotNull)
Name (NotNull)
EffectiveDate (NotNull)
What I would like to do is rewrite this query as a join. I'm not totally SQL savvy, and am not sure if it is possible. Can anyone give me a hand?
3 1740
Actually what you have done is also a join,however we can re-write the querey as following. select mn.* from (select m.mgrId, max(m.effectiveDate) as effDate from managerNames m group by m.mgrId) d inner join managerNames mn
on mn.mgrId = d.mgrId and mn.effectiveDate = d.effDate
I need to select a list of managers from the managerNames table with the most recent effectiveDate. I can do this with the query below:
select mn.*
from (select m.mgrId, max(m.effectiveDate) as effDate from managerNames m
group by m.mgrId) d, managerNames mn
where mn.mgrId = d.mgrId
and mn.effectiveDate = d.effDate
Column Defintions:
Id (Pk, NotNull)
MgrId (Fk, NotNull)
Name (NotNull)
EffectiveDate (NotNull)
What I would like to do is rewrite this query as a join. I'm not totally SQL savvy, and am not sure if it is possible. Can anyone give me a hand?
Try this this may be help,i am sorry if it dosen't helps you select max(m2.effectiveDate),m1.mgrId,m1.effectivedate,m1 .othercolumns from managerNames m1 inner join managerNames m2 on m1.mgrId = m2.mgrId and m1.effectiveDate = m2.effectiveDate
group by m1.mgrId
Thanks for the reply.
The problem I'm running into is that I want all the columns from the table, but when I use the 'max' function i find I need to use 'group by'. This causes more rows to be returned. For example I could have one mgrId with two (or more) effectivedDates:
id - 110
mgrId - 1716
name - Thomas
effectiveDate - 07/07/2002
-and-
id - 127
mgrId - 1716
name - Tom
effectiveDate - 12/09/2007
-and-
id - 156
mgrId - 1716
name - Thom
effectiveDate - 07/31/2005
using the agregate causes all of these to be returned, but I only want the most recent date (id - 127).
What I have works for now, but I would like to lose the subselect. Ultimately I need to convert this to HQL, and from what I know subselects don't convert well.
Thanks again for the input.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: ColdCanuck |
last post by:
Help!
I'm trying to understand the new ANSI join syntax (after many years of
coding using the old style). I am now working with an application that only
understands ANSI syntax so I am...
|
by: Greg Stark |
last post by:
So I have a query in which some of the select values are subqueries. The
subqueries are aggregates so I don't want to turn this into a join, it would
become too complex and postgres would have...
|
by: James |
last post by:
I have a performance problem with the following query and variations on the
subselect. The EXISTS version of the first example will complete in ~10
minutes. The NOT logic in both the examples...
|
by: StaZ |
last post by:
Hello,
I would simply like to know if there's a way to disable the "feature"
of MS Access that rewrites your queries "correctly"...
This feature makes this : ...WHERE SomeBooleanField...;...
|
by: Marco Lazzeri |
last post by:
I'd like to reference values returned by a subselect in the same SELECT
query.
Example:
SELECT id,
( SELECT COUNT(*) FROM second ) AS value_to_reference,
( value_to_reference + 1 ) AS...
|
by: Morten K. Poulsen |
last post by:
(re-post)
Dear list,
Please let me know if this is not the list to ask this kind of question.
I am trying to optimize a query that joins two relatively large (750000 rows in
each) tables....
|
by: Sebastien |
last post by:
I have the following statement which I run successfully in... 1 hour 10
minutes.
SELECT
a.tsgicd as ACCT_ID,
a.tsa5cd as SEC_ID,
CASE
WHEN (SUBSTRING(a.tsgicd, 6, 1) = 'R'
or...
|
by: johnfaulkner |
last post by:
Hi, I am trying to perform a single select of data from 2 tables, table A and table B.
Table B may have none, one or many corresponding rows.
If table B has no corresponding rows then table B...
|
by: nemesisdan |
last post by:
This may seem simple to most, but for some reason i cannot get this to work!!
I have 2 tables, table_a & table_b
Both tables have a string reference number used to join. table_a will only ever...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |