mysql – Selecting a value from a row where another column is max

I have the following SQL query:

SELECT bug.`id`,
       Max(report.`date`),
       Count(report.`id`),
       Max(version.`code`),
       bug.`id`
FROM   `bug`
       LEFT OUTER JOIN `stacktrace`
                    ON ( stacktrace.`bug_id` = bug.`id` )
       CROSS JOIN `version`
                  LEFT OUTER JOIN `report`
                               ON ( report.`stacktrace_id` = stacktrace.`id` )
WHERE  stacktrace.`version_id` = version.`id`
GROUP  BY bug.`id`
ORDER  BY Max(report.`date`) DESC; 

I now want to select version.name instead of version.code from the row where version.code is maximal. Is this possible? If so, how do I do this with minimal amount of queries/overhead?


Relevant Tables (stripped):

CREATE TABLE `bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

INSERT INTO `bug` VALUES();
INSERT INTO `bug` VALUES();

CREATE TABLE `version` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `stacktrace` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` int(11) NOT NULL,
  `version_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_s_v` (`version_id`),
  KEY `FK_s_b` (`bug_id`),
  CONSTRAINT `FK_s_b` FOREIGN KEY (`bug_id`) REFERENCES `bug` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_s_v` FOREIGN KEY (`version_id`) REFERENCES `version` (`id`) ON DELETE CASCADE
);

CREATE TABLE `report` (
  `id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `stacktrace_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_r_s` (`stacktrace_id`),
  CONSTRAINT `FK_r_s` FOREIGN KEY (`stacktrace_id`) REFERENCES `stacktrace` (`id`) ON DELETE CASCADE
);

As SQLFiddle

postgresql – Selecting different values from a table that joins on a single UID

I’m having some trouble with a pretty extensive query/report I’m trying to write.
The error I continue to get is: ERROR: more than one row returned by a subquery used as an expression SQL state: 21000. Now, this report has 60+ columns and references various tables. I believe the problematic part is that I need to select different results using the same unique ID (account.id).

For instance consider these tables:

ACCOUNT:           ORDERS:              Transaction:   
ID (pk)            ID (pk)                 ID (pk)        
fname              buyer_account_id        order_id       
lname              order_number            buyer_id
email                                      seller_id
phone

In the same row, the report calls for both buyer account information as well as seller account information. I assumed I would need to use a subquery to pull the seller information, as if I simply join the tables on one or the other, I won’t be able to get the others’ information.

so I did something like this:

select distinct 
orders.id as "ID",
concat(account.firstname, ' ', account.lastname) as "Buyer",
account.email as "Buyer Email",
account.phone as "Buyer Phone",
(
SELECT
    concat(account.firstname, ' ', account.lastname)
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller",
(
SELECT
    account.email
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller Email",
(
SELECT
    account.phone
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller Phone"
from orders
inner join transaction on transaction.order_id = orders.id
join account on orders.buyer_account_id = account.id

When I run this in my test environment, it runs without issue. However, when run against a production db with thousands of entries, there are multiple results returned for the subquery(ies).

This is a postgresql db.

How can I improve my query so that I wont have this problem?

The actual query is much longer and uses a few additional tables, but I’m not able to provide a reproducible example or example data for it. If you’d like to have a look, the real query is below.

select distinct
orders.id as "ID",
concat(account.firstname, ' ', account.lastname) as "Buyer",
account.email as "Buyer Email",
account.phone as "Buyer Phone",
(
SELECT
    address.address
    from address
    where address.id = orders.delivery_address_id
    and orders.id = transaction.order_id
) as "Buyer Address",
(
SELECT
    address.zip
    from address
    where address.id = orders.delivery_address_id
    and orders.id = transaction.order_id
) as "Buyer Zip",
(
SELECT
    address.neighborhood
    from address
    where address.id = orders.delivery_address_id
    and orders.id = transaction.order_id
) as "Buyer Neighborhood",
(
SELECT
    concat(account.firstname, ' ', account.lastname)
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller",
(
SELECT
    account.email
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller Email",
(
SELECT
    account.phone
    from account
    where account.id = transaction.seller_id
    and orders.id = transaction.order_id
) as "Seller Phone",
(
SELECT
    address.address
    from address
    where address.id = orders.store_address_id
    and orders.id = transaction.order_id
) as "Seller Address",
(
SELECT
    address.zip
    from address
    where address.id = orders.store_address_id
    and orders.id = transaction.order_id
) as "Seller Zip",
(
SELECT
    address.neighborhood
    from address
    where address.id = orders.store_address_id
    and orders.id = transaction.order_id
) as "Seller Neighborhood",
(
SELECT
    order_item.quantity
    from order_item
    where order_item.order_id = orders.id
    and transaction.order_id = orders.id
) as "Quantity",
(
SELECT
    order_item.kitchen_item_name
    from order_item
    where orders.id = order_item.order_id
    and transaction.order_id = orders.id
) as "Store Items",
orders.promo_code_id as "Promo Code",
orders.deliverytype as "Delivery Type",
orders.order_preference as "Order Preference",
orders.pickup_date_time as "Pickup/Delivery DateTime",
orders.placed as "Order Placed",
(
SELECT
    SUM(order_item.purchase_price)
    from order_item
    where order_item.order_id = orders.id
    and orders.id = transaction.order_id
    group by orders.id, order_item.id
) as "Buyer Food Total",
(
SELECT
    COALESCE(SUM(order_item.purchase_price),0) - COALESCE(sum(order_item.iva_on_purchase_price),0)
    from order_item
    where order_item.order_id = orders.id
    and orders.id = transaction.order_id
    group by orders.id, order_item.id
) as "Buyer Food Total Pre IVA",
transaction.delivery_fee as "Delivery Fee",
orders.discounted_amount as "Discounted Amount",
transaction.total as "Buyer Total",
(
SELECT 
    COALESCE(transaction.total,0) - COALESCE(transaction.iva_on_order_amount,0)
    from transaction
    where transaction.order_id = orders.id
) as "Order Total Pre IVA",
transaction.iva_on_order_amount as "IVA On Order Total",
transaction.delivery_fee_pre_iva as "Delivery Fee Pre IVA",
transaction.fees_sub_total_pre_iva as "Savorly Fee Pre IVA",
transaction.iva_on_total_fees as "IVA On Fees",
transaction.total_fees as "Fees Total Incl IVA",
transaction.net_profit_pre_iva as "Net Profit",
transaction.customer_discount as "Customer Discount",
transaction.marketing_discount as "Food Discount",
transaction.delivery_discount as "Delivery Discount",
--DeliveryBlindDiscount,
transaction.marketing_discount_iva_fee as "IVA On Marketing Discount",
transaction.delivery_discount_iva_fee as "IVA On Delivery Discount",
transaction.iva_on_savorly_fee as "IVA On Savorly Fee",
transaction.fees_sub_total_pre_iva as "Fees Sub Total Pre IVA",
transaction.iva_on_net_profit as "IVA On Net Profit",
transaction.net_profit_pre_iva as "Net Profit Pre IVA",
--Merchant Fee pre iva,
transaction.provider_payout_transaction_id as "Provider Transaction Id",
transaction.payout_custom_id as "Provider Payout Transaction Id",
--iVoy Order Id,
transaction.os_type as "OS Type",
transaction.os_version as "OS Version",
transaction.app_version as "App Version",
transaction.notes as "Notes",
transaction.is_paid as "Is Paid",
transaction.status as "Status"
--Fechas, 
--Cupons, 
--Organic, 
--Week
from orders
inner join transaction on transaction.order_id = orders.id
join account on orders.buyer_account_id = account.id
join store_address on orders.store_address_id = store_address.id
join address on store_address.address_id = address.id
join order_item on orders.id = order_item.order_id
where orders.placed >= '2020-09-28' AND orders.placed <= '2020-10-8';

selection – “Shopping Cart behaviour” for selecting rows in a table?

I’m currently struggling with the design of a table that would allow the user to select rows/items from a filtered/unfiltered table (probably with a standard checkbox column) and then still keep this selection when the search or filters of the table gets changed.

On top of that “shopping cart” management (add or remove items, or clear the selection), there will be some specific batch actions that can be applied to the selected items; like adding all of them to a group, having those entries deleted (not from the selection, but from the system) and so on.

I considered using a tab that shows a table with the selected rows:

enter image description here

But that is a no-go, since we already use tabs like this to, for example, separate entries by a specific type (something like “current tasks” from “done tasks”).

Right now I’m fiddling with a bar on top of the title row that expands and replaces the “full list” table:

enter image description here

enter image description here

I’m not really happy with this, since the position of the bar will probably confuse the users on what they are seeing on the table and how it relates to that bar.

Using the bar on the bottom is also off the table (pun intended), since we use infinite scroll on tables (that can easily have hundreds of entries) and the navigation buttons (like “next-step”) already sit on a fixed bar on the bottom of the page.

I think that’s it. If anyone can help with examples or ideas, that will be greatly appreciated.

Poll Results: Which of These Features Do You Look for When Selecting a Provider?

Back on September 14, we asked you what features you look for when selecting a VPS provider.

Those who’ve been around for a while remember the old days when every provider slapped SolusVM on a master node, slurped in some OpenVZ templates, and setup shop.  Today’s consumer expects more, and so we were curious which features really matter to customers.

131 votes later, the results are in!

Interestingly, the ability to upload a custom ISO was the #1 feature, closely followed by DDOS mitigation and snapshots.  I’m very partial to autorun scripts, because I like the ability to deploy a server and immediately have it setup and running the way I want, but possibly people are scratching that itch with Ansible and similar technologies.

We allowed people to pick up to 4 features, so there is some weighting here on what’s most important to customers.  Providers should take note about what matters to the community as they try to differentiate themselves from their competition.

Thanks everyone for participating!

 

 

raindog308

I’m Andrew, techno polymath and long-time LowEndTalk community Moderator. My technical interests include all things Unix, perl, python, shell scripting, and relational database systems. I enjoy writing technical articles here on LowEndBox to help people get more out of their VPSes.

sorting – Find the average number of steps to sort an array by randomly selecting two elements

I have sequence of an unique numbers from 1 to 10 in randomly order (for example: list = (7, 5, 3, 4, 2, 6, 7, 1, 9, 8)). I can choose two random number and if the list from left number larger then right number I swap them (for exmaple 2 and 5 list(2) larger then list(5) so I’ll swap them and etc).

Need to find average number of such step to order the sequence.

The alrothim should executed less then 1 second.

Can anybody help me?

installation – If Cygwin is installed, selecting “Download without installing option” seems to skip installed packages

I tried to build a local installation directory for servers whithout internet connection, from a computer which has already Cygwin installed.

Using this directory leads to an incomplete installation, close to an empty stub (even mintty was missing). I selected “Install” for all packages in the category view during setup, but nothing was installed.

Rebuilding a local directory after having uninstalled Cygwin solved the problem. Did I miss something?

python – Selecting Line of Pandas DataFrame by a String Index

I have a pandas dataframe of the form:

enter image description here

Where “it”, “their” and “charact” are the indexes. How can I select a value based on the index? When I try the following:

corpus_df.iloc('it',1)

I get an error:

ValueError: Location based indexing can only have (integer, integer slice (START point is INCLUDED, END point is EXCLUDED), listlike of integers, boolean array) types

Poll: Which of These Features Do You Look for When Selecting a Provider?

Years ago, most providers offered SolusVM and OpenVZ templates. We’ve come a long way! Now even small providers have custom-developed features that rival the major public clouds. From block storage to custom ISOs to the ability to move your VM across a continent (or to a different one!) with the press of a button, there are a lot of features and capabilities that providers are bringing to the table.

Which of them matter the most to you? Please vote and inform our provider community what matters to you!

Note: You can select a maximum of four features, so please indicate those that are most important.

raindog308

I’m Andrew, techno polymath and long-time LowEndTalk community Moderator. My technical interests include all things Unix, perl, python, shell scripting, and relational database systems. I enjoy writing technical articles here on LowEndBox to help people get more out of their VPSes.