-1

i am trying to set up a geo data base for my company using PostgreSQL with pgAdmin4. I am very new in database administration. We need a database storing the information about drilling points. Each drilling point has a unique LGRB NR, coordinates and some other information such as geology, all stored in table 1.

Table 1:

LGRB NR North coordinate East coordiante some columns with other information
3481/12 3444130 5284092
3481/8 3444130 5284092

Primary key is the LGRB NR. The second table contains survey data, since during the project the coordiantes may change sligthly. Primary keys are LGRB NR and the coordinates.

Table 2:

LGRB NR North coordinate East coordiante date of geodetic measurment
3481/12 5555555 8888888 2022-01-01
3481/12 3444130 5284092 1990-01-01
3481/8 11111111 2222222 1999-01-01

Table 1 is the one containing all relevant information that is planned to be usig in QGIS with the postgis exctension (works well so far). So, now what i want is that table 1 always contains the newes coordinates from table 2. Is this possible? How can i do this? Thanks in advance!!

Improve this question

1 Answer 1

Reset to default
1 
WITH cte AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY "LGRB NR" 
                              ORDER BY "date of geodetic measurment" DESC) rn
    FROM table2
    )
SELECT *
FROM table1
JOIN cte USING ("LGRB NR")
WHERE cte.rn = 1;
Improve this answer
2
  • now what I want is that table 1 always contains the newest coordinates from table 2 , I think the query should select North coordinate and East coordiante columns from table2. Example Commented Apr 6, 2023 at 10:26
  • @ErgestBasha I select everything. OP'd decide what columns she needs in. Commented Apr 6, 2023 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.