Subscribe to this thread
Home - General / All posts - SQL problem (M8)
vincent

1,972 post(s)
#27-Apr-18 21:08

Hi,

I have this SQl Query :

'SQL 

Update

(Select * from [Splitted_Lines] as [A] 

INNER JOIN [EndPoints] as [B] on [A].[no_ponceau] = [B].[no_ponceau]

 where  [A].[no_ponceau] = "Doris_05"

and 

[A].[situation] = "aval" 

and 

Touches([A].[Geom (I)], [B].[Geom (I)])

 order by

 [B].[elevation] DESC)

 Set [alt_end]  = [elevation]

The result returns one or two lines. I would like to have only the first line if there is two, before updating the value of alt_end. The result is not good most of the time if there is more than one record in the record set.

I know the "first" argument, but cannot find a place to insert it with success.

Any suggestion ?

tjhb
10,094 post(s)
#27-Apr-18 22:00

It is an aggregate function. You can use it in the SELECT list around any column (or expression) which is not used for grouping. Here you are not using any columns for grouping, so you can wrap any columns you like inside FIRST (or LAST, MIN, MAX...).

Here you will need FIRST([A].[alt_end]) and FIRST([B].[elevation]). (I am assuming alt_end is from table A. By the way it's a good idea to make this explicit in the code, especially for when you come back to it later.)

But there is a catch, when wrapping an aggregate query inside a SELECT statement.

tjhb
10,094 post(s)
#27-Apr-18 22:52

Here you are not using any columns for grouping

...but you will need to. See code below.

tjhb
10,094 post(s)
#27-Apr-18 22:10

The catch is that aggregating rows means the rows are new. It is not like filtering. The resulting rows are not rows from the original source table or tables, but rows in a new virtual table existing in the context of the query.

If you UPDATE that aggregated table, you do not update the original source table(s). It really has no effect.

To update the original source table, you must include original source rows.

The way to do that is to join the aggregate table back to a separate, non-aggregated instance of the original. The aggregated table should therefore include a unique ID for the purpose of the join. It does not need to include the column you want to update--that is redundant, since you only update the original.

tjhb
10,094 post(s)
#27-Apr-18 22:46

Try this (not tested).

UPDATE

    (

    SELECT

        [L].[alt_end][T].[source elevation]

    FROM

        [Splitted_Lines] AS [L]

        INNER JOIN

        (

        SELECT

            [ID] AS [target ID],

            FIRST([elevation]AS [source elevation]

        FROM

            (

            SELECT

                [A].[ID],

                [B].[elevation]

            FROM

                (

                SELECT *

                FROM [Splitted_Lines]

                WHERE [no_ponceau] = "Doris_05"

                AND [situation] = "aval"

                ) AS [A]

                INNER JOIN

                [EndPoints] as [B]

                ON [A].[no_ponceau] = [B].[no_ponceau]

                AND Touches([A].[ID][B].[ID])

            ORDER BY [B].[elevation] DESC

            )

        GROUP BY [ID]

        ) AS [T]

    ON [L].[ID] = [T].[target ID]

    )

SET [alt_end] = [source elevation]

;

I have rearranged some things. Table A is pre-filtered, since that is faster than filtering after the expensive join. Touches is changed to use object IDs, so that it is controlled by the location precision of the (first) drawing, whereas Geom (I) would the use default precision of 0.000001.

adamw


10,447 post(s)
#28-Apr-18 10:41

Tim answered how to do this in 8, here is (one possible way) to do it in 9.

Query text:

--SQL9

 

FUNCTION elev(_no_ponceau NVARCHAR, _geom GEOMINT32 AS (

  SELECT Min([elevation])

  FROM [endpoints]

  WHERE [no_ponceau] = _no_ponceau AND GeomTouches([Geom], _geom, 0)

END;

 

UPDATE (

  SELECT [mfd_id][alt_end], elev([no_ponceau][geom]AS [elevation]

  FROM [splitted_lines]

  WHERE [no_ponceau] = 'Doris_05' AND [situation] = 'aval'

SET [alt_end] = [elevation];

Example MAP file attached.

Attachments:
update-join-query-9.mxb

tjhb
10,094 post(s)
#29-Apr-18 23:52

This is a really nice example of how far Manifold 9 SQL has progressed from Manifold 8.

Sometimes it appears more complicated (mainly the obligation to think carefully about indexes). But this example is massively simpler.

I'm not yet in the habit of using a lookup function instead of a join. (Riivo recently gave another example that I also didn't think of.)

Here I would instinctively have used a join then COLLECT... ORDER... FETCH...), which is great if we need the best N matches for a given test (where N > 1; very hard to replicate in 8!), but that struicture is wasteful if we only need one match.

We could use MAX or MIN in Manifold 8 instead of FIRST, just as Adam does for 9. That would remove one subquery layer (no need to ORDER) and would also be quicker.

(I don't know if it matters, but MAX might be a better tiebreaker for elevation than MIN, to match lionel's original draft query with DESC.)

vincent

1,972 post(s)
#30-Apr-18 13:55

I'm gonna test it today Tim. Thank you for all the explanations and optimizations. I'll post back.

For M9, this have to wait. My script is too long to bring it to 9 now.

vincent

1,972 post(s)
#30-Apr-18 16:47

It works great !

UPDATE (

    SELECT

        [L].[alt_end][T].[source elevation]

    FROM

        [Splitted_Lines] AS [L]

        INNER JOIN

        (

        SELECT

            [ID] AS [target ID],

            Max([elevation]AS [source elevation]

        FROM

            (

            SELECT

                [A].[ID],

                [B].[elevation]

            FROM

                (

                SELECT *

                FROM [Splitted_Lines]

                WHERE [no_ponceau] = "Doris_01"

                AND [situation] = "amont"

                ) AS [A]

                INNER JOIN

                [EndPoints] as [B]

                ON [A].[no_ponceau] = [B].[no_ponceau]

                AND Touches([A].[ID][B].[ID])

            )

        GROUP BY [ID]

        ) AS [T]

    ON [L].[ID] = [T].[target ID]

    )

SET [alt_end] = [source elevation]

;

jw15 post(s)
#01-May-18 19:04

Haven't tried it, but what about SELECT TOP 1 ... and skip the aggregating?

tjhb
10,094 post(s)
#01-May-18 20:23

That would be fine if you only wanted one result row for the entire table.

If you need one result row for each X—here, for each split line—then that won’t do it.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.