need help on a sql query: :)

[abloyboat]

---

naa tingali ni shortcut but di ko kahibalo, longcut nalang (wa ni nako gi check ang sql). assuming wala nay significance and ID.

select freightbill, max(timestamp) as 'col_timestamp'
into #tbl_timestamp
from history
group by freightbill

select tbl_tmp.freightbill, tbl_tmp.col_timestamp, tbl_hist.status
into #tbl_status
from #tbl_timestamp tbl_tmp,
history tbl_hist
where (tbl_tmp.freightbill = tbl_hist.freightbill) and
(tbl_tmp.col_timestamp = tbl_hist.timestamp)

select tbl_master.freightbill, tbl_stat.col_timestamp, tbl_stat.status
from freight_bill tbl_master,
#tbl_status tbl_stat
where tbl_master.freightbill = tbl_stat.freightbill

[spikes]

karon pa gyod ko katarong og simulate.

select 	x.id, 
	x.freightbill, 
	decode(y.timestamp,NULL,'NULL',to_char(y.timestamp,'HH:MI AM')) timestamp,
	decode(y.status,NULL,'NULL',y.status) status
from freight_bill x
left outer join (
	select 	b.id id, 
		b.freightbill freightbill,
		b.timestamp timestamp, 
		b.status status
	from  history b
	where b.timestamp=(Select max(a.timestamp) 
			   from history a
	   		   where a.freightbill=b.freightbill)) y
on x.freightbill=y.freightbill

tested on oracle db with the ff output

        ID FREIGHTBILL                    TIMESTAM STATUS
---------- ------------------------------ -------- ------------------
         1 frght0001                      07:00 PM analysis
         2 frght0002                      05:00 PM requeue
         3 frght0003                      05:00 PM NULL
         4 frght0004                      08:30 PM requeue
         5 frght0005                      NULL     NULL
         6 frght0006                      NULL     NULL

6 rows selected.

mas limpyo kong hinoon kong i view ni cya nga subquery

create or replace view history_vw 
as
select 	b.id id, 
		b.freightbill freightbill,
		b.timestamp timestamp, 
		b.status status
	from  history b
	where b.timestamp=(Select max(a.timestamp) 
			   from history a
	   		   where a.freightbill=b.freightbill)

and instead of putting the subquery for the outer join,
use the view

select 	x.id, 
	x.freightbill, 
	decode(y.timestamp,NULL,'NULL',to_char(y.timestamp,'HH:MI AM')) timestamp,
	decode(y.status,NULL,'NULL',y.status) status
from freight_bill x
left outer join history_vw y
on x.freightbill=y.freightbill

mas refine cya, then dali sabton

[silent-kill]

karon pa gyod ko katarong og simulate.

select 	x.id, 
	x.freightbill, 
	decode(y.timestamp,NULL,'NULL',to_char(y.timestamp,'HH:MI AM')) timestamp,
	decode(y.status,NULL,'NULL',y.status) status
from freight_bill x
left outer join (
	select 	b.id id, 
		b.freightbill freightbill,
		b.timestamp timestamp, 
		b.status status
	from  history b
	where b.timestamp=(Select max(a.timestamp) 
			   from history a
	   		   where a.freightbill=b.freightbill)) y
on x.freightbill=y.freightbill

tested on oracle db with the ff output

        ID FREIGHTBILL                    TIMESTAM STATUS
---------- ------------------------------ -------- ------------------
         1 frght0001                      07:00 PM analysis
         2 frght0002                      05:00 PM requeue
         3 frght0003                      05:00 PM NULL
         4 frght0004                      08:30 PM requeue
         5 frght0005                      NULL     NULL
         6 frght0006                      NULL     NULL

6 rows selected.

mas limpyo kong hinoon kong i view ni cya nga subquery

create or replace view history_vw 
as
select 	b.id id, 
		b.freightbill freightbill,
		b.timestamp timestamp, 
		b.status status
	from  history b
	where b.timestamp=(Select max(a.timestamp) 
			   from history a
	   		   where a.freightbill=b.freightbill)

and instead of putting the subquery for the outer join,
use the view

select 	x.id, 
	x.freightbill, 
	decode(y.timestamp,NULL,'NULL',to_char(y.timestamp,'HH:MI AM')) timestamp,
	decode(y.status,NULL,'NULL',y.status) status
from freight_bill x
left outer join history_vw y
on x.freightbill=y.freightbill

mas refine cya, then dali sabton

thanks sa inyo mga inputs naka tug kos ka lisod heehehe

my friend told me mas hinay daw mag query og naai sub query sa solod. compared sa i temp table nimo ang imong sub query...

i forgot to mention i has to perform well with milions2x of rows XD

[silent-kill]

naa tingali ni shortcut but di ko kahibalo, longcut nalang (wa ni nako gi check ang sql). assuming wala nay significance and ID.

select freightbill, max(timestamp) as 'col_timestamp'
into #tbl_timestamp
from history
group by freightbill

select tbl_tmp.freightbill, tbl_tmp.col_timestamp, tbl_hist.status
into #tbl_status
from #tbl_timestamp tbl_tmp,
history tbl_hist
where (tbl_tmp.freightbill = tbl_hist.freightbill) and
(tbl_tmp.col_timestamp = tbl_hist.timestamp)

select tbl_master.freightbill, tbl_stat.col_timestamp, tbl_stat.status
from freight_bill tbl_master,
#tbl_status tbl_stat
where tbl_master.freightbill = tbl_stat.freightbill

thanks sa imo input.. i think you need to consider the null values of time stamps pud?
igon ani ako style karon eheheh pro gi gakoy ko himo sa ika 2 nga temp table...

[silent-kill]

hehehe, klaro jud wala nako gi test akong sql, karon ako na jud gi testing sa mysql, it's a "dirty" solution but the output is right (I think).

try this:

SELECT b.FreightBill,time_format(substring(max(concat(a.T imeStamp,a.status is not null)),1,,'%r') as time,substring(max(concat(a.TimeStamp,a.status)),9 ) as status FROM freight_bill as b left join history as a on b.FreightBill = a.FreightBill group by b.FreightBill;

+-------------+-------------+----------+
| FreightBill | time | status |
+-------------+-------------+----------+
| frght0001 | 07:00:00 PM | analysis |
| frght0002 | 05:00:00 PM | requeue |
| frght0003 | 05:00:00 PM | NULL |
| frght0004 | 08:30:00 PM | requeue |
| frght0005 | NULL | NULL |
| frght0006 | NULL | NULL |
+-------------+-------------+----------+
6 rows in set (0.00 sec)

heheeh thanks for this other work around ill try to test this, and iya performance...

[spikes]

thanks sa inyo mga inputs naka tug kos ka lisod heehehe

my friend told me mas hinay daw mag query og naai sub query sa solod. compared sa i temp table nimo ang imong sub query...

i forgot to mention i has to perform well with milions2x of rows XD

maka effect gyod subquery sa performance, pero if you used if properly ok pud hinoon. like proper use of indexes and views.


for temp tables, wla pa ko kagamit ani nga mananap, more on views hinoon ako gamit.

[abloyboat]

thanks sa imo input.. i think you need to consider the null values of time stamps pud?
igon ani ako style karon eheheh pro gi gakoy ko himo sa ika 2 nga temp table...

yap, i forgot. dapat mao ni ang last line sa akong sql ....

where tbl_master.freightbill *= tbl_stat.freightbill

[split_nutz]

create fir a temp query for the latest time stamp n evry FreightBill..
note: the field name 'MaxOfTimeStamp' is to emphasize the objectve of the field. replace it in any field name u like.

create qryLTSTemp - temporary lastest time stamp
fields ( FreightBill, MaxOfTimeStamp )
sql statement - SELECT DISTINCT history.FreightBill, Max(history.TimeStamp) AS MaxOfTimeStamp
FROM history GROUP BY history.FreightBill;

then create qryLTS - ur goal output
fields ( ID, FreightBill, MaxOfTimeStamp, Status )
sql statement - SELECT DISTINCT freight_bill.ID, freight_bill.FreightBill, qryLTSTemp.MaxOfTimeStamp, history.Status
FROM freight_bill LEFT JOIN (qryLTSTemp LEFT JOIN history ON (qryLTSTemp.FreightBill = history.FreightBill) AND (qryLTSTemp.MaxOfTimeStamp = history.TimeStamp)) ON freight_bill.FreightBill = qryLTSTemp.FreightBill
GROUP BY freight_bill.ID, freight_bill.FreightBill, qryLTSTemp.MaxOfTimeStamp, history.Status;


--------------------------------------------
maau untag nkatabang ni sa imong problema

[moz_k2]

is that ID in your history table a field?
and is it direct directly related to timestamp?

if yes pwede na nimo gamiton to get the max so dli nka magproblem if null nga value ang timestamp

[split_nutz]

here is the idea.. first, u wil create a query from table 'history' where u will get the latest TimeStamp [max(history.TimeStamp)] of every history.FreightBill. the lastest timestamps r retrievd n the query 'qryLTSTemp'. then create another query 'qryLTS' for ur desired output where u use LEFT JOIN ( to include all records frm table 'freightbill' and only those recrds from 'qryLTSTemp' where the joind fields r equal) w/c means all freight_bill.ID would appear regardless if my records na cya sa 'qryLTSTemp' o wla(NULL)..