sql - How to get the last record from Ms Access table -

-

i have 2 tables named reefers , alerts. want join these 2 tables.

now, how result shows last record of each reeferno alerts table alerttype equal "temperatures"? 

table 1: reefers                             reeferno    transporter                      e-110       express1                         e-111       express1                         e-112       express1                         a-001       a-trucking                       a-002       a-trucking                        table 2: alerts                          alertdatetime   receiveddatetime    alerttype   reeferno    temperature location         5/2/15 9:53  5/2/15 9:58     arrival       e-110        5.2      warehouse         5/2/15 9:48  5/2/15 9:53     departure     e-111        5.4      warehouse         5/2/15 9:40  5/2/15 9:45     temperatures  a-001        11.37    warehouse         5/2/15 9:38  5/2/15 9:43     temperatures  a-001        10.06    store         5/2/15 9:35  5/2/15 9:40     temperatures  a-001        10.02    store         5/2/15 9:19  5/2/15 9:24     departure     a-001        10.02    store         5/2/15 9:12  5/2/15 9:17     temperatures  a-002        10.37    warehouse         5/2/15 9:06  5/2/15 9:11     temperatures  a-002        12.62    warehouse         5/2/15 9:04  5/2/15 9:09     arrival       a-002        12.62    warehouse

result must be: 

reeferno  transporter   alertdatetime   receiveddatetime    alerttype   temperature location e-110     express1 e-111     express1 e-112     express1 a-001     a-trucking   5/2/15 9:40   5/2/15 9:45    temperatures     11.37       warehouse a-002     a-trucking   5/2/15 9:12   5/2/15 9:17    temperatures     10.37       warehouse

i'm using below code it's not giving me result want. 

select reefers.reeferno, reefers.transporter, alerts.alerttype,      alerts.alertdatetime, alerts.temperature, alerts.location,  alerts.receiveddatetime reefers inner join (alerts inner join (select  alerts.reeferno,  max(alerts.receiveddatetime)  maxreceiveddatetime  alerts  group alerts.reeferno)  temptable on (alerts.reeferno =      temptable.reeferno) , (alerts.receiveddatetime =  temptable.maxreceiveddatetime)) on reefers.reeferno = alerts.reeferno (((alerts.alerttype)="temperatures"));

update:

i changed required result. instead of word null, leave blank reefers without temperatures alerttype.

start query pulls want reefers had @ least 1 temperature alert:

select     sub1.reeferno,     a2.alertdatetime,     a2.receiveddatetime,     a2.alerttype,     a2.temperature,     a2.location     (         select             a1.reeferno,             max(a1.alertdatetime) maxofalertdatetime         alerts a1         a1.alerttype='temperatures'         group a1.reeferno     ) sub1     inner join alerts a2     on             (sub1.maxofalertdatetime = a2.alertdatetime)         , (sub1.reeferno = a2.reeferno);

then left join reefers table subquery based on first query ...

select     r.reeferno,     r.transporter,     sub2.alertdatetime,     sub2.receiveddatetime,     sub2.alerttype,     sub2.temperature,     sub2.location     reefers r     left join     (         select             sub1.reeferno,             a2.alertdatetime,             a2.receiveddatetime,             a2.alerttype,             a2.temperature,             a2.location                     (                 select                     a1.reeferno,                     max(a1.alertdatetime) maxofalertdatetime                 alerts a1                 a1.alerttype='temperatures'                 group a1.reeferno             ) sub1             inner join alerts a2             on                     (sub1.maxofalertdatetime = a2.alertdatetime)                 , (sub1.reeferno = a2.reeferno)         ) sub2     on r.reeferno = sub2.reeferno;

Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more