473,320 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need help rewriting a subselect as a join

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?
Aug 16 '07 #1
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?
Aug 17 '07 #2
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
Aug 17 '07 #3
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.
Aug 17 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

3
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...
6
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...
4
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...
2
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...;...
1
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...
2
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....
6
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...
4
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...
1
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...
0
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...
0
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...
1
isladogs
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...
0
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...
0
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...
1
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.