How come Min function in this query statement print name rather than integers

This is a question on

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively. Note: Print NULL when there are no more names corresponding to an occupation.

Input Format The OCCUPATIONS table is described as follows:

Column Type Name String Occupation String Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input  Name    Occupation Samantha    Doctor Julia   Actor Maria   Actor Meera   Singer Ashely  Professor Ketty   Professor Christeen   Professor Jane    Actor Jenny   Doctor Priya   Singer 

]

Sample Output

Jenny Ashley Meera Jane Samantha Christeen Priya Julia NULL Ketty NULL Maria 

Explanation The first column is an alphabetically ordered list of Doctor names. The second column is an alphabetically ordered list of Professor names. The third column is an alphabetically ordered list of Singer names. The fourth column is an alphabetically ordered list of Actor names. The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL valu

SET @r1=0, @r2=0, @r3 =0, @r4=0; SELECT MIN(Doctor), MIN(Professor), MIN(Singer), MIN(Actor) FROM (SELECT CASE Occupation WHEN 'Doctor' THEN @r1:=@r1+1                        WHEN 'Professor' THEN @r2:=@r2+1                        WHEN 'Singer' THEN @r3:=@r3+1                        WHEN 'Actor' THEN @r4:=@r4+1 END        AS RowLine,        CASE WHEN Occupation = 'Doctor' THEN Name END AS Doctor,        CASE WHEN Occupation = 'Professor' THEN Name END AS Professor,        CASE WHEN Occupation = 'Singer' THEN Name END AS Singer,        CASE WHEN Occupation = 'Actor' THEN Name END AS Actor        FROM OCCUPATIONS ORDER BY Name) AS t GROUP BY RowLine; 

My doubt how come MIN(DOCTOR) print names This is from ..Here in this tutorials it comes as number

Please give me a solid answer .I am in the process of learning mysql

wp_insert_post fails at return statement (nothing is returned to the caller, the error displayed is: The Link You Followed Has Expired)

there is a WooCommerce site where we try to add the Wholesale Pro plugin, which upon activation creates two pages (login, store). For this task it calls the wp_insert_post from a static method. The wp_insert_post does its stuff, and we even log every part of the wp_insert_post procedure to debug.log, comes down to return statement and then the WordPress fails with "The Link You Followed Has Expired" on the plugins.php screen. The return statement returns nothing to the caller static method, but the page/post is created in wp_posts table.

The plugin itself is proven to activate and work properly on another site.

Using the array result of a select statement returns ERROR: pq: operator does not exist: integer = integer[]

The query in question is the following:

SELECT * FROM options WHERE id = any(SELECT option_ids FROM workshops WHERE id=3) 
ERROR: pq: operator does not exist: integer = integer[] 

Basically the statement inside any() returns an array of ids which I want to use to get some rows on the options table.

I don’t understand why is it trying to compare an integer to the whole array, isnt any supposed to compare a single int to multiple integers in an array?

Why is OR statement slower than UNION

Database version: Postgresql 12.6

I have a table with 600000 records.

The table has columns:

  • name (varchar)
  • location_type (int) enum values: (1,2,3)
  • ancestry (varchar)

Indexes:

  • ancestry (btree)

The ancestry column is a way to build a tree where every row has an ancestry containing all parent ids separated by ‘/’

Consider the following example:

id name ancestry
1 root null
5 node ‘1’
12 node ‘1/5’
22 leaf ‘1/5/12’

The following query takes 686 ms to execute:

SELECT * FROM geolocations WHERE EXISTS (    SELECT 1 FROM geolocations g2    WHERE g2.ancestry =        CONCAT(geolocations.ancestry, '/', geolocations.id) ) 

This query runs in 808 ms seconds:

SELECT * FROM geolocations WHERE location_type = 2 

When combining both queried with an OR it takes around 4 seconds 475 ms to finish if it ever finishes.

SELECT * FROM geolocations WHERE EXISTS (    SELECT 1 FROM geolocations g2    WHERE g2.ancestry =        CONCAT(geolocations.ancestry, '/', geolocations.id) ) OR location_type = 2 

Explain:

[   {     "Plan": {       "Node Type": "Seq Scan",       "Parallel Aware": false,       "Relation Name": "geolocations",       "Alias": "geolocations",       "Startup Cost": 0,       "Total Cost": 2760473.54,       "Plan Rows": 582910,       "Plan Width": 68,       "Filter": "((SubPlan 1) OR (location_type = 2))",       "Plans": [         {           "Node Type": "Index Only Scan",           "Parent Relationship": "SubPlan",           "Subplan Name": "SubPlan 1",           "Parallel Aware": false,           "Scan Direction": "Forward",           "Index Name": "index_geolocations_on_ancestry",           "Relation Name": "geolocations",           "Alias": "g2",           "Startup Cost": 0.43,           "Total Cost": 124.91,           "Plan Rows": 30,           "Plan Width": 0,           "Index Cond": "(ancestry = concat(geolocations.ancestry, '/', geolocations.id))"         }       ]     },     "JIT": {       "Worker Number": -1,       "Functions": 8,       "Options": {         "Inlining": true,         "Optimization": true,         "Expressions": true,         "Deforming": true       }     }   } ] 

While combining them with a union takes 1 sec 916 ms

SELECT * FROM geolocations WHERE EXISTS (    SELECT 1 FROM geolocations g2    WHERE g2.ancestry =        CONCAT(geolocations.ancestry, '/', geolocations.id) ) UNION SELECT * FROM geolocations WHERE location_type = 2 

Explain

[   {     "Plan": {       "Node Type": "Unique",       "Parallel Aware": false,       "Startup Cost": 308693.44,       "Total Cost": 332506.74,       "Plan Rows": 865938,       "Plan Width": 188,       "Plans": [         {           "Node Type": "Sort",           "Parent Relationship": "Outer",           "Parallel Aware": false,           "Startup Cost": 308693.44,           "Total Cost": 310858.29,           "Plan Rows": 865938,           "Plan Width": 188,           "Sort Key": [             "geolocations.id",             "geolocations.name",             "geolocations.location_type",             "geolocations.pricing",             "geolocations.ancestry",             "geolocations.geolocationable_id",             "geolocations.geolocationable_type",             "geolocations.created_at",             "geolocations.updated_at",             "geolocations.info"           ],           "Plans": [             {               "Node Type": "Append",               "Parent Relationship": "Outer",               "Parallel Aware": false,               "Startup Cost": 15851.41,               "Total Cost": 63464.05,               "Plan Rows": 865938,               "Plan Width": 188,               "Subplans Removed": 0,               "Plans": [                 {                   "Node Type": "Hash Join",                   "Parent Relationship": "Member",                   "Parallel Aware": false,                   "Join Type": "Inner",                   "Startup Cost": 15851.41,                   "Total Cost": 35074.94,                   "Plan Rows": 299882,                   "Plan Width": 68,                   "Inner Unique": true,                   "Hash Cond": "(concat(geolocations.ancestry, '/', geolocations.id) = (g2.ancestry)::text)",                   "Plans": [                     {                       "Node Type": "Seq Scan",                       "Parent Relationship": "Outer",                       "Parallel Aware": false,                       "Relation Name": "geolocations",                       "Alias": "geolocations",                       "Startup Cost": 0,                       "Total Cost": 13900.63,                       "Plan Rows": 599763,                       "Plan Width": 68                     },                     {                       "Node Type": "Hash",                       "Parent Relationship": "Inner",                       "Parallel Aware": false,                       "Startup Cost": 15600.65,                       "Total Cost": 15600.65,                       "Plan Rows": 20061,                       "Plan Width": 12,                       "Plans": [                         {                           "Node Type": "Aggregate",                           "Strategy": "Hashed",                           "Partial Mode": "Simple",                           "Parent Relationship": "Outer",                           "Parallel Aware": false,                           "Startup Cost": 15400.04,                           "Total Cost": 15600.65,                           "Plan Rows": 20061,                           "Plan Width": 12,                           "Group Key": [                             "(g2.ancestry)::text"                           ],                           "Plans": [                             {                               "Node Type": "Seq Scan",                               "Parent Relationship": "Outer",                               "Parallel Aware": false,                               "Relation Name": "geolocations",                               "Alias": "g2",                               "Startup Cost": 0,                               "Total Cost": 13900.63,                               "Plan Rows": 599763,                               "Plan Width": 12                             }                           ]                         }                       ]                     }                   ]                 },                 {                   "Node Type": "Seq Scan",                   "Parent Relationship": "Member",                   "Parallel Aware": false,                   "Relation Name": "geolocations",                   "Alias": "geolocations_1",                   "Startup Cost": 0,                   "Total Cost": 15400.04,                   "Plan Rows": 566056,                   "Plan Width": 68,                   "Filter": "(location_type = 2)"                 }               ]             }           ]         }       ]     },     "JIT": {       "Worker Number": -1,       "Functions": 15,       "Options": {         "Inlining": false,         "Optimization": false,         "Expressions": true,         "Deforming": true       }     }   } ] 

My question is, why does postgresql execute the OR query much slower?

put the result of a query into a variable and WITH statement

I need to put the result of a query into a variable.

Just a query, works as successful

DECLARE @count INT = (  SELECT count (*) FROM [AdventureWorks].[Person].[Address]     );  select @count; 

But if I need to use the WITH statement in a query, then I get a syntax error

DECLARE @count INT = (    WITH person_address (id)    as (     SELECT AddressID     FROM [AdventureWorks].[Person].[Address]    )    SELECT count (*)    FROM person_address     );  select @count; 

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword ‘WITH’.

Msg 319, Level 15, State 1, Line 2 Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ‘)’.

How do I put the query value into a variable if the WITH clause is used in the SQL statement?

add_filter( ‘the_title’ gets through this if statement twice

I am using the accepted answer shown here: Apply the_title() filter in post & page title, but not in menu title

For some reason, the code is getting through this if statement twice:

if ( ! is_admin() && $  post instanceof WP_Post && ( $  post->post_type == 'page' ) && in_the_loop() && is_main_query() ) { 

I am already using the pre_wp_nav_menu and wp_nav_menu_items filters as shown on that answer.

What would be making this code run twice please? Or how would I debug to find out what is calling it and letting it through the if twice?

(When I use debug_backtrace() is shows apply_filters line 212 in plugins.php both times but I’m not sure what to do with that info)

Sorry, I would ask as a follow-up on the posted question, but I evidently can’t comment yet.

Do I need to create index for the column when using it in update statement?

I have a table in postgresql11 which has a userId as a column. And it is not primary key in the table. I need to perform below update statement on the table.

update entity set name='xxxx' where userId = 'yyy.

The userId is in the where condition but I don’t know whether I should create an index for userId. Will it improve performance? If yes, how can I specify index in the update statement?

Case statement query

How can this be re-written. somehow not able to make it right. In case statement Then and ELSE are same would like to change it to single statement.

SELECT t.status_id status_id, ( CASE WHEN t .status_id NOT IN (SELECT status_id FROM firm_return WHERE enabled = ‘D’ and firm_id=t.firm_id)

THEN (cl.category_code + ‘-‘ + s.status_name) ELSE cl.category_code + ‘-‘ + s.status_name END) AS status_type FROM bla bla

Split data across multiple rows in a single statement

I have data in table like

Customer Invoice No Date Value Paid Balance
ABC 1 01/12/2020 25 0 25
ABC 2 01/12/2020 50 0 50
XYZ 3 02/12/2020 200 0 200
XYZ 4 04/12/2020 100 0 100
ABC 5 04/12/2020 500 0 500

Now I received amounts for customers as below

ABC 540 XYZ 210

ABC XYZ
540 210

After receiving the amounts my table should like this

Customer Invoice No Date Value Paid Balance
ABC 1 01/12/2020 25 25 0
ABC 2 01/12/2020 50 50 0
XYZ 3 02/12/2020 200 200 0
XYZ 4 04/12/2020 100 10 90
ABC 5 04/12/2020 500 465 35

I got some clues but this works only for date based values but I need customer and date based.

Any help will be appreciated