inline query

Hi,

 

 

You can do that by using a SQL statement in the SQL creator or the SQL executor.

 

 

Itay

Thank you for the immediate reply.

 

My question was what would be the sql query?

 

 

Wopuld I group?

 

Thank you

try the link...

Hi Rick,

 

 

Just to elaborate on Itay's answer: it is very hard (i.e. impossible) to write a SQL statement for an unknown (to us) table structure containing unknown data.

 

 

So you'll either have to read up a bit on SQL or give us a detailed example of what you have and what you want to achieve, so that we can try to help you :-)

 

 

David

Hi Rick,

 

 

Just as a simplified example, assuming that the table has 2 columns named 'segment_no' and 'revision_date': select * from <table name> where (segment_no, revision_date) in (select segment_no, max(revision_date)

 

    from <table name> group by segment_no);   This is not tested and also there may be more efficient ways, please learn about SQL before applying.

 

Takashi

You guys are great ! Thank you so much for your help.

 

I do apoligize for my i ignorance with SQL.

 

I'm just a dumb surveyor

 

The original table contains over 900,000 records.

 

Im attempting you show  a sample of this table.

 

 

ill now try  to eplain my problem.

 

Pipelines are defined by unique segment numbers.

 

Pipelines are drawn by  xy coordinates (LAT,LONG).

 

This table has mutiple revision dates for each SEMNT .I was wanting to rertieve only the the latest revions records.

 

 

Thanks again

 

 

 

 

Seg_No ASBUILT_SEQ_NUM ASBUILT_LAT ASBUILT_LONG NAD_YEAR_CD PROJ_CODE SURF_X_COORD_LOC SURF_Y_COORD_LOC LAST_REV_DATE VERSION_DATE ASBUILT_FLAG PPL_APURT_TYPE PPL_APURT_NAME VERSION_DATE00 6 1 28.50645 -91.1015 27       20091027 20091027 Y   SS 219 PLATFORM "A" RISER 10/27/2009 6 1 28.50645 -91.1015 27 72 2074497 -58193.4 19960318 19970424 Y     4/24/1997 6 1 28.50645 -91.1015 27       20091023 20090702 N RISER   7/2/2009 6 2 28.50646 -91.0978 27 72 2075659 -58185.7 19960318 19970424 Y     4/24/1997 6 2 28.50646 -91.0978 27       20091027 20091027 Y     10/27/2009 6 2 28.50646 -91.0978 27       20091023 20090702 N   AMOCO 8" TIE 1 7/2/2009 6 3 28.50665 -91.0952 27 72 2076504 -58114.2 19960318 19970424 Y     4/24/1997 6 3 28.50665 -91.0952 27       20091027 20091027 Y     10/27/2009 6 3 28.50665 -91.0952 27       20091023 20090702 N   AMOCO 8" TIE 2 7/2/2009

 

Hi Rick,

 

 

there is of course nothing "dumb" about being a surveyor :-)

 

 

This is not really a question that is related to FME, this should ideally be a question for your software developer or database administrator to handle.

 

 

But here is something that might work for your sample data:

 

 

SELECT seg.* FROM segments seg INNER JOIN     (     SELECT seg_no, MAX(version_date) AS max_version_date     FROM segments     GROUP BY seg_no     ) groupedseg ON seg.seg_no = groupedseg.seg_no                   AND seg.version_date = groupedseg.max_version_date ORDER BY seg_no, asbuilt_seq_num;

 

 

As you can see, this is not trivial, and there is really no good way for me to say if there are some gotchas in your data or logical model that might not be covered by the above. The worst errors are the silent ones... So please attribute as much confidence in the above as you would if you sent a software developer on a surveying mission ;-)

 

 

David

 

 

Hi Rick,

 

 

Sorry about the SQL example in my previous answer, using "in" operator is concise but "in" with multiple columns - "(A, B) in (...)" - is not standard, som database servers e.g. MS SQL Server and MS Access don't support such a syntax.  David's example is available in general.

 

  Takashi