Sql question 2

[kaminari]

I have this query
select max (last_update) from trnsfr_main where user_id='706'
union
select max (last_update) from ship_main where user_id='706'
union
select max (last_update) from return_main where user_id='706'
union
select max (date(dl_date)) from file_main where user_id='706'

When I run the query I get two different dates results back. I only want the latest date of the two back how do I do this?

 

[sunila7]

Hi,

Try this....

Select top 1 dt From
(select max (last_update) dt from trnsfr_main where user_id='706'
union
select max (last_update) dt from ship_main where user_id='706'
union
select max (last_update) dt from return_main where user_id='706'
union
select max (date(dl_date)) dt from file_main where user_id='706') TBL
order by dt desc

Sunil

 

[swampBoogie]

select max(x) from (
select max (last_update) x from trnsfr_main where user_id='706'
union
select max (last_update) from ship_main where user_id='706'
union
select max (last_update) from return_main where user_id='706'
union
select max (date(dl_date)) from file_main where user_id='706')

 

[swampBoogie]

forgot the correlation name for the derived table

select max(x) from (
select max (last_update) x from trnsfr_main where user_id='706'
union
select max (last_update) from ship_main where user_id='706'
union
select max (last_update) from return_main where user_id='706'
union
select max (date(dl_date)) from file_main where user_id='706') dt

 

[kaminari]

I tried both of the suggestions and neither one of these helped I just get general sql errors. I don't know if it makes any difference but I am on an informix database.

 

[sunila7]

Hi,
This forum is for Microsoft SQL Server....

Try Asking ur question in Informix forum forum876


Sunil

 

[kaminari]

thanks

 

[kaminari]

I didn't know there was a difference.