display the latest data

hi i have table which got same data but from diffirent database in difiirent time stamp, i what to view which data is the latest,in my table i got folowing column

empno_db1,emp_no_db2,trans_date_db1,trans_date_db2,receipt_date_db1,receipt_date_db2

my goal is to see which record is the latest one and from which database, how can i approch this,sql

chalie001Asked: 2015-09-10

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 80

slightwv (䄆 Netminder) Commented: 2015-09-10

Use the MAX in the SQL

select empno,max(trans_date), max(receipt_date) from table@db1 group by empno

same for the db2 query.

chalie001Author Commented: 2015-09-10

but i what to compare the date from diffirent database column and display the latest record and what to know from which database

LVL 80

slightwv (䄆 Netminder) Commented: 2015-09-10

I understand that from your previous question. Myself and others suggested MINUS. That is how I would do it.

It appears you went with a join option. Using that will require additional logic because the columns will be side-by-side so I'm not sure what you are thinking the next step is...

Anyway, just use inline views with the MAX

instead of something like:
select * from table@db1;

you would use:
select * from (select empno,max(trans_date) trans_date, max(receipt_date) receipt_date from table@db1 group by empno)

Then use the joins as before.

LVL 33

awking00Information Technology SpecialistCommented: 2015-09-10

Which date field determines the latest, trans_date or receipt_date?

LVL 36

Mark GeerlingsDatabase AdministratorCommented: 2015-09-10

Trying to display and compare these values side-by-side as multiple columns in the same record will be very complex. It is much simpler to display the data from the two different system by using two queries, one from each database, joined by the MINUS operator. That will highlight the differences very quickly and simply. (This assumes that you have a database link defined between the two systems.)

chalie001Author Commented: 2015-09-10

i what something like this

   select empno_db1, trans_date_db1, trans_date_db2
          greatest(receipt_date_db1, receipt_date_db2) last_receipt,
          case when receipt_date_db1 = receipt_date_db2
                  then 'Equal'
                when receipt_date_db1 > receipt_date_db2
                  then 'Db1'
                else 'Db2' end last_source
    from your_table

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 80

slightwv (䄆 Netminder) Commented: 2015-09-10

>>i what something like this

Then use that.

You'll need to join the columns something like:
select empno_db1,emp_no_db2
from
(select pk_col, empno as empno_db1 from table@db1) db1
join
(select pk_col, empno as empno_db2 from table@db1) db2
on(db1.pk_col=db2.pk_col)
/

If you want something different please provide a test case with sample data and expected results.

something like:
create table db1_table(pk_col number, empno number);
create table db2_table(pk_col number, empno number);

insert into db1_table values(1,1);
insert into db2_table values(1,2);
commit;

Expected results:
<insert what you want back here>

LVL 51

PortletPaulEE Topic AdvisorCommented: 2015-09-10

Provide:

a. "sample data" (from each table involved)
b. the "expected result" (derived from the sample)

from these items we get a much better understanding of what you are seeking.

chalie001Author Commented: 2015-09-14

select empno_db1,emp_no_db2
from
(select pk_col, empno as empno_db1 from table@db1) db1
join -- what must i write hear must i put minus
(select pk_col, empno as empno_db2 from table@db1) db2
on(db1.pk_col=db2.pk_col)

LVL 51

PortletPaulEE Topic AdvisorCommented: 2015-09-14

Please provide sample data, from each table, and the expected result you want from that sample. When we have those we can propose definite queries. Without those items we can watch this question rotate a few more times and still not know for sure what it is you are asking for.

awking00 has also asked a pertinent question:

"Which date field determines the latest, trans_date or receipt_date?"

LVL 80

slightwv (䄆 Netminder) Commented: 2015-09-14

>>join -- what must i write hear must i put minus

I cannot tell you what you need to put there since you have not provided sample data and expected results.

From your previous question, I have an idea about what you are wanting and I'm not sure it is possible with the path you have chosen but we'll continue that discussion over there.