In SQL, I have three columns, the first two are values to create distinction for a person. The third column will have different values for each person (dates). How do I select the row for each person with the oldest valued date?

should output:
John Plumber 1/3/2020
Elizabeth Teacher 12/1/2020

You can check a DB fiddle of the solution here (without a function just raw SQL): https://www.db-fiddle.com/f/fR2JtQKVcQzrnuZ9SfpcNS/0

For the solution, I am providing some dummy data to show some real output as I don't have your data:

create table if not exists users (
    user_id serial primary key,
    name text
);
create table if not exists games (

game_id serial primary key,

user_id int references users(user_id),

score numeric,

inserted_at timestamptz

);
insert into users (name)

values ('5w3n'),('CCoX'),('FHWbb8f7'),('NhsX1XcP'),('OPP6A3j3'),('t4N'),('scdHS'),('Adve'),('yqy'),

('fALF'),('J77T'),('TAXQ5B3V'),('GQGtBXV'),('U2yx6o'),('4in74FhL'),('ZsoZs'),('9dn6'),

('ewhi3u6q'),('q18');
insert into games(user_id, score, inserted_at)

values  (5,589.53,'2022-01-01'),(9, 48.70,'2022-01-11'),(6, 27.05,'2022-02-01'),

(1,952.81,'2022-02-23'),(11,23.80,'2022-02-15'),(5,732.60,'2022-03-02'),

(7,460.05,'2022-03-09'),(4,402.98,'2022-03-29'),(7,375.06,'2022-03-30'),

(3,325.03,'2022-04-12'),(2,363.32,'2022-04-15'),(12,34.60,'2022-04-19'),

(6,370.70,'2022-04-22'),(6,462.08,'2022-05-01'),(2,575.39,'2022-05-02'),

(13,14.56,'2022-05-03');

The function:

create or replace function get_user_averages(
    p_start_date timestamptz,
    p_end_date timestamptz default now(),
    p_interval interval default '1 month',
    p_truncate text default 'month'
)
  returns table(
    user_id int,
    name text,
    data numeric[]
  )
  language plpgsql
as
$$
begin
    return query
    select x.user_id, x.name, array_agg(x.average order by x.gen)
    from (
        select
            gen,
            u.user_id,
            u.name,
            round(avg(g.score) filter(where gen = date_trunc(p_truncate, g.inserted_at)) ,2) as average
        from generate_series(p_start_date, p_end_date, p_interval) gen
            cross join games g
            join users u on u.user_id = g.user_id
        group by gen, u.user_id, u.name
    ) x
    group by x.user_id, x.name
    order by x.user_id;
end;
$$;

This function allows you to input your own custom start and end date as well as choosing your own interval (it can be 1 month, 1 day etc.). The output of the array itself (data column) is intentionally ordered (so the array itself is ordered) by the generated dates in ascending order. It might not be a useful feature, however, considering you don't have the dates available in the final output you won't be able to know which average belongs to which generated date.

For this reason I have sorted the array so that your application code may be able to at least do something with the dates if it needs to. I would recommend trying to use a jsonb output instead of array where you would have a {month1:average1, month2:average2 ...} kind of output which may be more useful.

You have to match the p_interval and p_truncate parameters to get the correct output. As one is for date generation, the other specifies the truncation of the timestamptz of the real table's inserted_at (so if you're sending interval '1 month', you should send month in p_truncate parameter)

Calling the function:

select * from get_user_averages('2022-01-01');

The result:

+-------+--------+-----------------------------------+
|user_id|name    |data                               |
+-------+--------+-----------------------------------+
|1      |5w3n    |{null,952.81,null,null,null,null}  |
|2      |CCoX    |{null,null,null,363.32,575.39,null}|
|3      |FHWbb8f7|{null,null,null,325.03,null,null}  |
|4      |NhsX1XcP|{null,null,402.98,null,null,null}  |
|5      |OPP6A3j3|{589.53,null,732.6,null,null,null} |
|6      |t4N     |{null,27.05,null,370.7,462.08,null}|
|7      |scdHS   |{null,null,417.56,null,null,null}  |
|9      |yqy     |{48.7,null,null,null,null,null}    |
|11     |J77T    |{null,23.8,null,null,null,null}    |
|12     |TAXQ5B3V|{null,null,null,34.6,null,null}    |
+-------+--------+-----------------------------------+

We have Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

The server physically has 256 GB of RAM installed. SQL Server is set to a memory limit of 210 GB. I noticed that the server requests 39 GB of memory per query (210 * 0.75 * 0.25) when making queries, i.e. it thinks that it has 210 GB available, not 128 GB.

Question - is this setting correct or should the limit be set accordingly to SQL Server Standard edition - 128 GB?

As long as the second server is also the 2019 version, then I don't think you'll have any issues technologically.

But you have to be careful not violate your licensing agreement with Microsoft by using the Developer edition server for any production purpose. I'd recommend speaking to Microsoft or your Microsoft var to verify that side of things.

To your follow up question on which type of replication you should use, it depends on your use case and requirements:

1. https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver16 is probably the most commonly used kind. It is very configurable and flexible, and it replicates changes in near-realtime. (It replicates only the changes after the initial snapshot is synchronized.) One of the biggest requirements to use it is that any replicated tables need a primary key defined on them. A workaround for this is to create an indexed view which is applicable for replication because of its unique clustered index.

2. https://docs.microsoft.com/en-us/sql/relational-databases/replication/snapshot-replication?view=sql-server-ver16 is probably the next most commonly used kind of replication. It can be scheduled to run at a frequency of your choice but it drops and re-creates the entities being replicated, every time. This makes it a heavier type of replication than Transactional, and usually is not used for realtime synchronization.

3. https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-ver16 is another form of replication similar to Transactional, and can be used for realtime synchronization. I don't believe it has the same requirements as Transactional, in the sense that it can replicate tables without primary keys on them. But it has its own requirements. It's also useful for when your Subscriber database needs to be applicable for data changes that need to be propagated back to the Publisher, which is not usually possible with regular Transactional Replication (but I believe Transactional Replication with Updatable Subscriptions does allow this).

4. https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication?view=sql-server-ver16 is a fourth kind of replication that is also a type of Transactional Replication. (I never had a use case for it, so I don't know much about it, but please see the linked docs.)

You can use the xpath() function:

select pd.person_id, 
       (xpath('/PDRecord/PDName', pd.person_data))[1]
from person_data pd;

This returns

person_id | xpath                         
----------+-------------------------------
        1 | Daniel Morgan
        2 | Taner Poder  

xpath() returns an array of matches. [1] then picks the first element of that array.

If you want the raw content of the tag, then xmltable() is more suitable as it's also easier to add more columns:

select pd.person_id, 
       x.*
from person_data pd
  cross join xmltable('/PDRecord' passing pd.person_data
                      columns name text path 'PDName', 
                              email text path 'PDEmail')  as x

This returns:

person_id | name          | email                    
----------+---------------+--------------------------
        1 | Daniel Morgan | damorgan@u.washington.edu
        2 | Taner Poder   | damorgan@u.washington.edu

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9eace732300305ecc1146cdc1bf9d07b

TempDB data file configuration is generally based on total logical CPUs, regardless of NUMA count.

The typical reason for adding data files is due to the locking of GAM, SGAM or PFS as objects are created and dropped within a single file. Unlike data files of user databases, objects are continuously created and dropped in TempDB very often. By adding data files, you can get around this limitation by spreading those actions across other files. As the CPU count goes up, so does the potential for more contention within a single data file.

I’ve always followed the general guidelines mentioned in the linked doc for years. Set TempDB data file count equal to CPU count, up to a max of 8. Even on systems with 16+ CPUs, I’ll initially create 8 files. Only if contention is observed it TempDB would I add more, in increments of 4.

When talking about other configurations, like MAXDOP, the general reason for limits related to NUMA is to attempt to keep a single query running on the same memory bank. In that case, you don’t want a query spread across CPU cores on different NUMA nodes. However, with TempDB contention, you’re dealing with all running queries running on the severer, not just the scope of a single query going parallel. Thus, you don’t care about NUMA.

JMeter can be used for this, it doesn't support ODBC but it supports https://en.wikipedia.org/wiki/Java_Database_Connectivity which should be more or less the same from the database point of view.

Also JMeter will give you connect time and latency (time to first byte) https://jmeter.apache.org/usermanual/glossary.html so you will be able to assess not only query execution time but also the time required for the result to be transferred over the wire.

More information: https://www.blazemeter.com/blog/real-secret-building-database-test-plan-jmeter

If you just need to measure the maximum network throughput between database host and the machine where you're running your tests from - take a look at https://iperf.fr/

To answer this question, let's look at what happens when a transaction is committed to the database.

When a DML statement is executed against SQL Server, a few events must happen.

1. The transaction is written to the transaction log on disk.
2. The transaction is next written to the corresponding data page in memory and the data page is then marked as dirty (I.E. it no longer matches what is on disk).
3. At the next CHECKPOINT or LAZY WRITE, dirty pages will be written back to disk. Due to the frequency of when these run, it's actually possible for a single data page to receive several changes before the entire page is written back to disk.

Once the transaction is in the transaction log, it's essentially committed. This is the key point in the process. If the SQL Server were to crash prior to the change making it to the transaction log, SQL Server would have never acknowledged to the client that the transaction was successful.

If the SQL Server were to crash after the transaction log is updated, and prior to the page being updated in memory or on disk, the transaction could still be recovered from the transaction log. Once the SQL Server service starts on the new primary node, the database would go through recovery, and roll the database forward by applying the transaction from the transaction log that were not yet written to the data files.

Your issue is trying to use single quotes within a string:

'name='jhon''

Here the second quote is not taken as part of the string, but the end of it. The parser then sees jhon and doesn't know what to do with it.

To include a single quote in a string you need to "escape" it with another quote, so that example becomes:

'name=''jhon'''

Note the three single quotes at the end: the first two represent a quote in the string value, the last terminates the string as it is not escaped by another.

In other languages you see back-slash characters used as escapes, so \' for a quote, and with certain options that works in postgres but doubling up quotes is the standard way for SQL.

^{As a side note: be very careful when producing ad-hoc SQL this way, you will find it very easy to open you code up to "SQL injection" attacks. If you are not already aware of this danger, I suggest you look it up before continuing down this path.}

I need to list all undo tablespace names in an Oracle (19c) database (regardless of whether they're used or not) from the ts$ table.

My SQL statement below gives that info but it also lists all the tablespace types (which I don't want). If I refine the statement to exclude the types and return just the list of UNDO tablespace name(s) is DECODE the core T function to use?

select name,
       decode(ts.contents$, 0,(decode(bitand(ts.flags, 4503599627370512), 16, 'UNDO',4503599627370496, 'LOST WRITE PROTECTION','PERMANENT')), 1, 'TEMPORARY') 
from ts$ ts ;

Is there a better alternative to this function? (Like CASE)