Hi - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Author

Topic

bb_anand
Starting Member

13 Posts

Posted - 2004-05-25 : 14:18:49

I'm pulling data from two tables based on the field which should be the most recent & exists on both the tables? Any clue abt sql query???

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-25 : 14:20:24

Got an IDENTITY Column or a datetime column with a default of GETADATE()?

How about posting some DDL...that should help....

Brett

8-)

bb_anand
Starting Member

13 Posts

Posted - 2004-05-25 : 14:59:10

The common field which I have in both the tables is 'population_info_id' and condition is that while pulling the data from both tables the population_info_id should be the most recent one.

Any clue on how SQL shall look???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 15:01:12

If that column is an identity column, then you could pull the MAX to get the most recent one.

SELECT MAX(population_info_id)
FROM Table1

If that isn't what you want, please post an example.

Tara

bb_anand
Starting Member

13 Posts

Posted - 2004-05-25 : 15:12:38

Hi Tara,
My query is somewhat different, here is what it looks like:
a) Have two tables ABX & XYZ
b) Both have common field 'population_info_id' (datatype - integer)
c) data to be filtered only on the most recent population_info_id (which shall be maximum in this case)of both tables.

what shall be the sql

Bharat

bb_anand
Starting Member

13 Posts

Posted - 2004-05-25 : 15:30:12

Can anybody help???? I need to complete & handover t'day!!!

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-25 : 15:43:41

quote:
Originally posted by X002548

Got an IDENTITY Column or a datetime column with a default of GETADATE()?

How about posting some DDL...that should help....

Brett

8-)

How about giving what we asked for so we can help you...

Do you know how to script the tables in enterprise manager?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 15:46:39

You haven't provided us with an example yet. Please provide the needed information so we can help you.

Tara

bb_anand
Starting Member

13 Posts

Posted - 2004-05-25 : 15:54:47

Here is the DDL:

SELECT OPPORTUNITY.TYPE, OPPORTUNITY.STAGE, OPPORTUNITY.STATUS, OPPORTUNITY.CREATEDATE, HRZNOPPSTAGES.LAST_OUTCOME_DATE
FROM
OPPORTUNITY, HRZNOPPSTAGES
WHERE
OPPORTUNITY.OPPORTUNITYID = HRZNOPPSTAGES.OPPORTUNITYID
AND
OPPORTUNITY.POPULATION_INFO_ID = (select max(population_info_id) from HRZNOPPSTAGES)

which is working, however, I need to filter where both OPPORTUNITY.POPULATION_INFO_ID & HRZNOPPSTAGES.POPULATION_INFO_ID should be max.
Not sure of how to make the query???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 15:56:42

That is not DDL. DDL is CREATE TABLE statements for your tables, in your case two tables. We also need to see a data example. Some sample rows from the two tables, plus what you want the result set to look like.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-25 : 16:02:30

Well that's DML...

DDL is CREATE TABLE myTable99...

And if you get the MAX ID from both tables, what if one is greater than the other...they won't JOIN

I don't think you want MAX Id

Maybe Max ID out of one, the MAx date out of the other..that's why the DDL would be helpful...

Brett

8-)

bb_anand
Starting Member

13 Posts

Posted - 2004-05-25 : 16:05:04

Well I don't have any example to give as the tables are empty, however, I can tell that I need the respective columns to populate based on the conditions:
a) OPPORTUNITY.OPPORTUNITYID = HRZNOPPSTAGES.OPPORTUNITYID
b) most recent OPPORTUNITY.POPULATION_INFO_ID & HRZNOPPSTAGES.POPULATION_INFO_ID

in the above scenarion POPULATION_INFO_ID is an integer & I need the maximum value (same as the latest value as it shall be updated after completion of each load).

Does it make sense or do u need any more details.

bb_anand
Starting Member

13 Posts

Posted - 2004-05-25 : 16:06:50

No both the Ids shall be same as they are being picked from the common source.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-25 : 16:26:41

Unless we can have a conversation like:


USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))
CREATE TABLE myTable00(Col1 int, Col2 datetime DEFAULT GetDate(), Col3 char(1))
GO

INSERT INTO myTable99(Col3)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT 'd'

INSERT INTO myTable00(Col1,Col3)
SELECT 1, 'a' UNION ALL
SELECT 1, 'b' UNION ALL
SELECT 1, 'c' UNION ALL
SELECT 1, 'd' UNION ALL
SELECT 2, 'z' UNION ALL
SELECT 3, 'y' UNION ALL
SELECT 4, 'x' UNION ALL
SELECT 5, 'w'

    SELECT * 
      FROM myTable99 t9 
INNER JOIN myTable00 t0 
	ON t9.Col1 = t0.Col1
     WHERE t9.Col1 = (SELECT MAX(Col1) FROM myTable99)
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

I'm not going to be able to help....

Brett

8-)

bb_anand
Starting Member

13 Posts

Posted - 2004-05-25 : 16:35:11

Brett,
fyi,
I don't have the DDL & neither I have the permissions to do that. It's with the DBAs. i'm into data movement. Hence wud not be able to provide DDl to you as asked for.

Is it possible if u can just provide the SQL query based on my i/p above???

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-25 : 16:39:10

OK, in business terms...what do you need to do

step 1. I need to find the set of data
step 2. In the related table I need to find all of x for y
step 3. Based on the relationship I then need to gather such and such information...

You don't have access to a db at all?

And what is data movement?

Brett

8-)

bb_anand
Starting Member

13 Posts

Posted - 2004-05-25 : 16:43:59

Data movement is to move data from large mainframes to presentable forms using different tools like Infoirmatica.

in business terms:
a) I have two tables Opportunity & HZZPPSTAGE.
b) I have to pull date from both the tables for certain fields as per my earlier message.
c) However, forllowing condition should be met that population_info_id (field present in both the tables) should be maximum. It shall be same as it's being pulled into the tables from the same source.

Will this help???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 16:56:00

Even if your tables are empty, please provide a made up example. We need something in order to help you.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources