need help on a sql query: :)

[silent-kill]

---

Hi, All i hope someone can help me out

so heres the situation...

i got a table lets call it 'history', and freight_bill

table:freight_bill
------------------------------------------------
ID     | FreightBill     |
------------------------------------------------
1      | frght0001     |
2      | frght0002     |
3      | frght0003     |
4      | frght0004     |
5      | frght0005     |
6      | frght0006     |
------------------------------------------------

table: history
------------------------------------------------
ID     | FreightBill     | TimeStamp     | status     |
------------------------------------------------
1      | frght0001     | 5pm              | pending    |
2      | frght0001     | 6pm              | analysis    |
3      | frght0001     | 7pm              | analysis    |
4      | frght0002     | 5pm              | requeue    |
5      | frght0003     | 5pm              | NULL        |
6      | frght0004     | 8pm              | analysis    |
7      | frght0004     | 8:30pm         | requeue    |
------------------------------------------------

so my concern is, how do i get all the freight bill with its latest time stamps or information?
so end result would be :

------------------------------------------------
ID     | FreightBill     | TimeStamp     | status     |
------------------------------------------------
1      | frght0001     | 7pm              | analysis     |
2      | frght0002     | 5pm              | requeue    |
3      | frght0003     | 5pm              | null    |
4      | frght0004     | 8:30pm         | requeue    |
5      | frght0005     | NULL             | NULL        |
6      | frght0006     | NULL             | NULL        |

[javapenguin]

Try this:

SELECT FreightBill,max(TimeStamp),status FROM history GROUP BY FreightBill ORDER BY FreightBill

[ChaosOrb]

^^ from your query this was the result:

+-------------+----------------+----------+
| FreightBill | MAX(TimeStamp) | STATUS   |
+-------------+----------------+----------+
| frght0001   | 7pm            | pending  |
| frght0002   | 5pm            | requeue  |
| frght0003   | 5pm            | null     |
| frght0004   | 9pm            | analysis |
+-------------+----------------+----------+

it didn't display the intended output silent-kill required.

[spikes]

Try this:

SELECT FreightBill,max(TimeStamp),status FROM history GROUP BY FreightBill ORDER BY FreightBill

I think ok na na cya. pero I refine nalang para pag left outer join. Try lang. dugay nako wla SQL.

SELECT
freight_bill.ID,
freight_bill.FreightBill,
max(history.TimeStamp),
history.status
FROM history
LEFT OUTER JOIN freight_bill ON history.FreightBill=freight_bill.FreightBill
GROUP BY freight_bill.ID, freight_bill.FreightBill,history.status
ORDER BY freight_bill.ID

[moz_k2]

pwede nimo i modify ang suggestion ni javapenguin
e union lang na nimo sa records with null timestamp

[ChaosOrb]

I think ok na na cya. pero I refine nalang para pag left outer join. Try lang. dugay nako wla SQL.

SELECT
freight_bill.ID,
freight_bill.FreightBill,
max(history.TimeStamp),
history.status
FROM history
LEFT OUTER JOIN freight_bill ON history.FreightBill=freight_bill.FreightBill
GROUP BY freight_bill.ID, freight_bill.FreightBill,history.status
ORDER BY freight_bill.ID

from your query, this is the output:

+------+-------------+------------------------+----------+
| ID   | FreightBill | max(history.TimeStamp) | status   |
+------+-------------+------------------------+----------+
|    1 | frght0001   | 7pm                    | analysis |
|    1 | frght0001   | 5pm                    | pending  |
|    2 | frght0002   | 5pm                    | requeue  |
|    3 | frght0003   | 5pm                    | null     |
|    4 | frght0004   | 8pm                    | analysis |
|    4 | frght0004   | 9pm                    | requeue  |
+------+-------------+------------------------+----------+

[cold_fusion]

With mysql you have to use a temporary table and user variables.
Try this:

create temporary table temp (FreightBill, TimeStamp, status)
	select if(@fb=FreightBill, "xRepeatedx", @fb:=FreightBill), TimeStamp, status 
	from history order by FreightBill, TimeStamp desc;

select f.ID, f.FreightBill, h.TimeStamp, h.Status
	from freight_bill as f 
	left join temp as h on f.FreightBill = h.Freightbill
	order by f.ID

There maybe syntax errors for this is not tested on actual sql server but the principle here is shown.

[javapenguin]

^^ from your query this was the result:

+-------------+----------------+----------+
| FreightBill | MAX(TimeStamp) | STATUS   |
+-------------+----------------+----------+
| frght0001   | 7pm            | pending  |
| frght0002   | 5pm            | requeue  |
| frght0003   | 5pm            | null     |
| frght0004   | 9pm            | analysis |
+-------------+----------------+----------+

it didn't display the intended output silent-kill required.

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)

[javapenguin]

ooops! ni sulod man c smily, 8 ) (eight then close parenthesis)

[ChaosOrb]

what say you silent-kill??