Select maximum of a count in a grouped clause

I have the following tables:

Vehicles(v͟i͟n͟, model,category) Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date) vehicleSold(saleID,v͟i͟n͟,salePrice) 

When I join these tables using:

select YEAR(Sales.saleDate)      , Vehicles.model      , count(Vehicles.model) 'Sold'      , Vehicles.category   from Vehicles    JOIN vehicleSold     on Vehicles.vin = vehicleSold.vin   JOIN Sales      on Sales.saleID = vehicleSold.saleID  group      by YEAR(Sales.saleDate)      , Vehicles.model      , Vehicles.category; 

Result is:

+----------------------+-------------+------+----------------+ | YEAR(Sales.saleDate) | model       | Sold | category       | +----------------------+-------------+------+----------------+ |                 2020 | Altima      |    1 | car            | |                 2020 | Flying Spur |    2 | car            | |                 2020 | Lifan E3    |    3 | Electric Moped | |                 2020 | Ridgeline   |    2 | truck          | |                 2020 | Shiver      |    4 | motorbike      | +----------------------+-------------+------+----------------+ 

Out of this table I want to get the model that was most sold in a category. So, in this case I only want to return a 2020, Flying Spur, car as the only row in category car because it was the most sold in 2020 in its category. I tried using a subquery is MAX(COUNT(*)) but I guess that is not supported in mysql. If anyone could point out my mistake and has any idea how to do this then that would be big help!

Coloured integers: Select outcome unexpected (part of a graph based problem using coloured vertex labels)

I use the following code output and need to retrieve the position of the part containing the blue-coloured integer 4

Part 1:

List[Placed[List[       Style[3, Rule[LineColor, RGBColor[1, 0, 0]],         Rule[FrontFaceColor, RGBColor[1, 0, 0]],         Rule[BackFaceColor, RGBColor[1, 0, 0]],         Rule[GraphicsColor, RGBColor[1, 0, 0]],         Rule[FontColor, RGBColor[1, 0, 0]]],        Style[4, Rule[LineColor, RGBColor[0, 0, 1]],         Rule[FrontFaceColor, RGBColor[0, 0, 1]],         Rule[BackFaceColor, RGBColor[0, 0, 1]],         Rule[GraphicsColor, RGBColor[0, 0, 1]],         Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]], Placed[      List[Style[4, Rule[LineColor, RGBColor[1, 0, 0]],         Rule[FrontFaceColor, RGBColor[1, 0, 0]],         Rule[BackFaceColor, RGBColor[1, 0, 0]],         Rule[GraphicsColor, RGBColor[1, 0, 0]],         Rule[FontColor, RGBColor[1, 0, 0]]],        Style[3, Rule[LineColor, RGBColor[0, 0, 1]],         Rule[FrontFaceColor, RGBColor[0, 0, 1]],         Rule[BackFaceColor, RGBColor[0, 0, 1]],         Rule[GraphicsColor, RGBColor[0, 0, 1]],         Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]]] 

I tried:

Part 2:

        Select[List[Placed[List[               Style[3, Rule[LineColor, RGBColor[1, 0, 0]],                 Rule[FrontFaceColor, RGBColor[1, 0, 0]],                 Rule[BackFaceColor, RGBColor[1, 0, 0]],                 Rule[GraphicsColor, RGBColor[1, 0, 0]],                 Rule[FontColor, RGBColor[1, 0, 0]]],                Style[4, Rule[LineColor, RGBColor[0, 0, 1]],                 Rule[FrontFaceColor, RGBColor[0, 0, 1]],                 Rule[BackFaceColor, RGBColor[0, 0, 1]],                 Rule[GraphicsColor, RGBColor[0, 0, 1]],                 Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]], Placed[              List[Style[4, Rule[LineColor, RGBColor[1, 0, 0]],                 Rule[FrontFaceColor, RGBColor[1, 0, 0]],                 Rule[BackFaceColor, RGBColor[1, 0, 0]],                 Rule[GraphicsColor, RGBColor[1, 0, 0]],                 Rule[FontColor, RGBColor[1, 0, 0]]],                Style[3, Rule[LineColor, RGBColor[0, 0, 1]],                 Rule[FrontFaceColor, RGBColor[0, 0, 1]],                 Rule[BackFaceColor, RGBColor[0, 0, 1]],                 Rule[GraphicsColor, RGBColor[0, 0, 1]],                 Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]]], Select[#[[1]][[2]], IntegerQ] &  == 4] 

Which produces { }

However

Part 3

 a = Placed[     List[Style[3, Rule[LineColor, RGBColor[1, 0, 0]],        Rule[FrontFaceColor, RGBColor[1, 0, 0]],        Rule[BackFaceColor, RGBColor[1, 0, 0]],        Rule[GraphicsColor, RGBColor[1, 0, 0]],        Rule[FontColor, RGBColor[1, 0, 0]]],       Style[4, Rule[LineColor, RGBColor[0, 0, 1]],        Rule[FrontFaceColor, RGBColor[0, 0, 1]],        Rule[BackFaceColor, RGBColor[0, 0, 1]],        Rule[GraphicsColor, RGBColor[0, 0, 1]],        Rule[FontColor, RGBColor[0, 0, 1]]]],      List[Before, After]][[1]][[2]]  Select[a,IntegerQ] 

produces 4 as expected.

Hence I would have expected Part 2 to produce the expression containing the blue label 4, i.e. the following result

Placed[List[           Style[3, Rule[LineColor, RGBColor[1, 0, 0]],             Rule[FrontFaceColor, RGBColor[1, 0, 0]],             Rule[BackFaceColor, RGBColor[1, 0, 0]],             Rule[GraphicsColor, RGBColor[1, 0, 0]],             Rule[FontColor, RGBColor[1, 0, 0]]],            Style[4, Rule[LineColor, RGBColor[0, 0, 1]],             Rule[FrontFaceColor, RGBColor[0, 0, 1]],             Rule[BackFaceColor, RGBColor[0, 0, 1]],             Rule[GraphicsColor, RGBColor[0, 0, 1]],             Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]] 

On closer inspection I tried the following:

FullForm[Select[a, IntegerQ]] 

which results in

Style[4] 

However, Style[4] should not be the result of a selection for integers.

What is going on?

Select box-shape grid between two positions on tilemap

I want to be able to click and hold on a certain point and mark it as startPosition. Then, while the mouse is held down and moved, mark that second position as endPosition.

When the mouse is finally released, I want to get all the positions between those two points.

Here is my code so far:

for (float x = startPosition.x; x <= endPosition.x; x++)         {             for (float y = startPosition.y; y <= endPosition.y; y++)             {                 Vector3Int newPosition = Utilities.GridPosFromWorldPos(new Vector2(x, y));                 if (!selectedPositions.Contains(newPosition)) { selectedPositions.Add(newPosition); }             }         } 

This works only when dragging up and/or to the right. As soon as I try to go to left or bottom, the endPosition coordinates become smaller than the startPosition coordinates, which breaks the for loop.

How can I solve this?

Select by mask

Hi, @Sven
Is there any way that I can select multiple projects by mask? I mean like:
*Project1*;*Project2*;Project3*
Or something similar. I’ve tried a couple of ways but didn’t work. Is there any?
I’ve got projects with sub projects and I need to select a various projects and their subprojects too.
Thanks

Multiple aggregations in select slow on postgres

I have a table with columns: id, antenna_id, latitude, longitude. There are two composite indexes on (antenna_id, latitude) and (antenna_id, longitude). When I do a max(latitude) for a specific antenna id(s), the speed is acceptable, but doing a min and max for both latitude and longitude at the same time is very slow.

SELECT version()

PostgreSQL 12.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit

Query

SELECT max(latitude) FROM packets WHERE antenna_id IN (1,2)

Explain

Finalize Aggregate  (cost=439588.11..439588.12 rows=1 width=32)   ->  Gather  (cost=439588.00..439588.11 rows=1 width=32)         Workers Planned: 1         ->  Partial Aggregate  (cost=438588.00..438588.01 rows=1 width=32)               ->  Parallel Index Only Scan using idx_packets_antenna_id_latitude on packets  (cost=0.57..430103.40 rows=3393839 width=7)                     Index Cond: (antenna_id = ANY ('{1,2}'::integer[])) JIT:   Functions: 5   Options: Inlining false, Optimization false, Expressions true, Deforming true 

Duration

[2021-03-06 12:14:38] 1 row retrieved starting from 1 in 4 s 438 ms (execution: 4 s 400 ms, fetching: 38 ms) [2021-03-06 12:14:51] 1 row retrieved starting from 1 in 2 s 590 ms (execution: 2 s 560 ms, fetching: 30 ms) 

The explain looks almost identical for max(longitude), min(latitude) and min(longitude) on their own. Speed is acceptable.

But when I combine the queries

SELECT max(latitude), max(longitude), min(latitude), min(longitude) FROM packets WHERE antenna_id IN (1,2)

Duration

[2021-03-06 09:28:30] 1 row retrieved starting from 1 in 5 m 35 s 907 ms (execution: 5 m 35 s 869 ms, fetching: 38 ms)

Explain

Finalize Aggregate  (cost=3677020.18..3677020.19 rows=1 width=128)   ->  Gather  (cost=3677020.06..3677020.17 rows=1 width=128)         Workers Planned: 1         ->  Partial Aggregate  (cost=3676020.06..3676020.07 rows=1 width=128)               ->  Parallel Seq Scan on packets  (cost=0.00..3642080.76 rows=3393930 width=14)                     Filter: (antenna_id = ANY ('{1,2}'::integer[])) JIT:   Functions: 7   Options: Inlining true, Optimization true, Expressions true, Deforming true 

Question

Why does the second query which does the min and max on the latitude and longitude field not use the indexes? And how can I rewrite the query so that it is faster?

Why doesn’t SQL Server use my index in this SELECT … WHERE?

I’ve created a table with a nonclustered PK (this is by design), and an additional nonclustered index on the column I’m filtering with a WHERE clause ([target_user_id]):

CREATE TABLE [dbo].[MP_Notification_Audit] (     [id]                    BIGINT             IDENTITY (1, 1) NOT NULL,     [type]                  INT                NOT NULL,     [source_user_id]        BIGINT             NOT NULL,     [target_user_id]        BIGINT             NOT NULL,     [discussion_id]         BIGINT             NULL,     [discussion_comment_id] BIGINT             NULL,     [discussion_media_id]   BIGINT             NULL,     [patient_id]            BIGINT             NULL,     [task_id]               BIGINT             NULL,     [date_created]          DATETIMEOFFSET (7) CONSTRAINT [DF_MP_Notification_Audit_date_created] DEFAULT (sysdatetimeoffset()) NOT NULL,     [clicked]               BIT                NULL,     [date_clicked]          DATETIMEOFFSET (7) NULL,     [title]                 NVARCHAR (MAX)     NULL,     [body]                  NVARCHAR (MAX)     NULL,     CONSTRAINT [PK_MP_Notification_Audit1] PRIMARY KEY NONCLUSTERED ([id] ASC) );  [...]  CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUser] ON [dbo].[MP_Notification_Audit] (     [target_user_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO 

This table has about 11,700 rows of data in, so it should be enough to trigger the use of indexes with WHERE clauses. If I SELECT just the column I’m filtering on, only the index is used and 133 matching rows are read – an index-only scan:

SELECT [target_user_id]   FROM [TestDb].[dbo].[MP_Notification_Audit]   WHERE [target_user_id] = 100017 

Execution plan 1

However, as soon as I add an extra column to the SELECT, the index is ignored and a table scan with a predicate is done to attain the result, reading over 11,700 rows:

SELECT [target_user_id], [patient_id]   FROM [TestDb].[dbo].[MP_Notification_Audit]   WHERE [target_user_id] = 100017 

Execution plan 2

Why is it ignoring my index in this second query? I’d have thought it would still be more efficient to use the index to get down to 133 RIDs, then query the extra row data required, than to go through every row of the table with a predicate? I know I can add columns to the index with INCLUDE with the extra fields needed in the SELECT clause to make it use the index again, but I’m interested as to why it doesn’t still use the index in this case.

A flag (indicator) that shows for each row which conditions was used to select it

Suppose I have the table foo. I want to filter the rows of this table using different conditions (cond_1, cond_2, …, cond_nthe order is important) combined in a disjunction, i.e.:

SELECT * FROM foo WHERE cond_1 OR cond_2 OR ... OR cond_n; 
  1. Is it possible (in PostgreSQL 13) to distinguish for each row which condition cond_i was used to select it? Since I stated that the order is important the question may be reformulated as – I want to know the first cond_i that selected the row, for each row.

Now consider that I have in foo a column for this purpose, e.g. selected_by.

  1. Is it possible to distinguish for each row which condition cond_i was used to select it and store it in selected_by? (the same as 1st question but for an UPDATE statement)

Two things are important: the order of conditions (as it was highlighted earlier); performance (i.e. table foo may have many rows, the number of conditions is small, up to 5-10).

I want to get the value of a select attribute in the checkout page, and then use it to show or not the delivery data

I want to get the value of a select attribute in the checkout page on WooCommerce, and then use it to show or not the delivery data.

I have tried everything but it still does not appear, the object of my products when I go through it comes out empty.

I have tried something similar to this to see what data I am getting but it comes out empty:

add_filter( 'woocommerce_cart_item_name', 'cart_variation_description', 20, 3); function cart_variation_description( $  name, $  cart_item, $  cart_item_key ) {     // Get the corresponding WC_Product     $  product_item = $  cart_item['data'];      if(!empty($  product_item) && $  product_item->is_type( 'variation' ) ) {         // WC 3+ compatibility         //$  descrition = $  product_item->get_description();         //$  descrition = $  product_item->get_attribute( 'pa_modo' );         $  descrition = $  product_item->get_post_meta( $  variation->ID, 'price_1_50', true );         $  result = __( 'Description: ', 'woocommerce' ) . $  descrition;         return $  name . '<br>' . $  result;     } else         return $  name; } 

Thanks a lot.

How to select selected Html Drop Down and paste it automatically into a Contact Form

first of all its my first question in Stackoverflow, because im hardstuck in a problem right now. So hopefully someone can help me with that.

The project: I am building a Product Configurator for a Website. The main goal is, that customers can configurate their product and the options they selected are automatically placed in a Contact Form.

I am working on WordPress with Elementor. The Plugin I used for the configurator is: "Woocommerce Extra Product options"

My Problem: I need to get the ID’s of the specific selected containers and fill the text of the containers automatically in the Form down below.

The site: https://soundgreets.com/produkt/konfigurator/

Does anyone know how i can do this? Maybe in a .php file? Or in elementor? I did some massive CSS on the site, so that it looks like it is right now. This Selector is to select all containers of the Dropdowns.

.tc-extra-product-options .cpf-type-select .tmcp-field-wrap label.fullwidth select{ color: red; } What i also tried is to use Elementors Html Code-widget to get the variable of the specific Id of a container.

This didnt work: the output is "undefined"

var e = document.getElementById(“tmcp_select_160362915b5ef2”); var strUser = e.options[e.selectedIndex].text; document.write(strUser) I hope someone has an idea on this.

thanks in advance

Berkan

PS: The site could look a bit messy i am sorry for that

select partially matching filenames

I have two lists of files with slightly different naming conventions

files1 = {"DTideal_circ_def=000um_astX=000um_astY=410um.png","DTideal_circ_def=050um_defX=000um_defY=010um.png"}  files2={"DT120_blur7_Defoc=000um_AstigX=000um_AstigY=410um.png", "DT120_blur7_Defoc=000um_AstigX=460um_AstigY=310um.png", "DT120_blur7_Defoc=000um_AstigX=460um_AstigY=360um.png", "DT120_blur7_Defoc=000um_AstigX=460um_AstigY=410um.png", "DT120_blur7_Defoc=000um_AstigX=460um_AstigY=460um.png", "DT120_blur7_Defoc=050um_AstigX=000um_AstigY=010um.png", "DT120_blur7_Defoc=050um_AstigX=000um_AstigY=060um.png", "DT120_blur7_Defoc=050um_AstigX=000um_AstigY=110um.png", "DT120_blur7_Defoc=050um_AstigX=000um_AstigY=160um.png", "DT120_blur7_Defoc=050um_AstigX=000um_AstigY=210um.png", "DT120_blur7_Defoc=050um_AstigX=000um_AstigY=260um.png"} 

I need to create pairs of files the paremeters of which are matching, i.e. to get

{{"DTideal_circ_def=000um_astX=000um_astY=410um.png","DT120_blur7_Defoc=000um_AstigX=000um_AstigY=410um.png"}, {"DTideal_circ_def=050um_defX=000um_defY=010um.png","DT120_blur7_Defoc=050um_AstigX=000um_AstigY=010um.png"}}