Let’s say i have 3 tables: Table ‘post’ id title 0 titleA 1 titleB 2 titleC Table ‘comment’ id id_post text 0 1 blaa 1 3 blbb 2 5 blcc Table ‘like’ id id_comment vote 0 1 +1 1 5 -1 2 5 +1 I need to get a list of post with the most liked comment. The query sould
Tag: mysql-5.7
Using GROUP BY and JOIN-ing non-aggregated columns to query
I know there are many similar questions, but I haven’t managed to solve my problem by reading them. I’d appreciate some pointers. Here’s some example data from my dummy table below: id foo bar baz moo ins_date percentage yes no maybe 38 foothing bar_one pizazz amoosing 2018-05-26 06:59:00 81…
SQL – Select rows with greatest revision that are less than a set revision
I have an audit table where entities are stored by id and an associated revision number and revision type (this is a Hibernate Envers audit table). E.g. id rev revtype foo_description 10 1 0 10 foo v1 10 3 1 10 foo v2 10 4 1 10 foo v3 20 2 0 20 foo v1 20 4 1 20 foo
Calculate the number of unique strings with the date, with a possible error
In the select, I get rows from the table with time in the format TIMESTAMP. I want to count unique rows, BUT with a possible error of 1 second. In the example below, for example, 3 unique records (1 …
Determine persistent growth in value during a specific time range
Given the following data set example, how should I structure my SQL query to determine if the value has gradually grown day-by-day given a time range in the query for a specific user_id by returning a …
Determine growth in value in a specific time range
Given the following data set example, how should I structure my SQL query in order to determine if the value has grown over time (given a time range in the query) for a specific UserId by returning either a positive/negative growth percentage result or a true/false result UserId timestamp value 1 1617711825 3…
Compare rows in one table with MySQL
I tried to compare rows in one table that I have. The table is like this: table I have 3 rows for comparing, and they had tp 1,2,3 for each one with the same fid and different id. The question is, how can I compare three of them to return? For example, more explanation For example, if ct of the
MySQL – Performance issue while joining most recent
I have two tables, markets (27 records) and histories (~1.75M records, ~67K per market). I need to get every market with its most recent histories record. The solutions I tried work but are incredibly slow. Tables DDL What I tried 1 – Uncorrelated subquery I started with this solution since I used it ot…
MySQL 5.7 – getting latest entry(s) from specific combination of columns
Hello dear MySQL users! i try to solf this since a while but everything i tried does not seem to work. I’m sorry if somewhere is already an answer for that but i know know how to search for. Its not so easy to explain in words so i use this table (called tbl ) to explain it: ID SerNo
Wrong syntax for OVER and PARTITION in MySQL syntax
SELECT Id, Price, CustomerId, ROW_NUMBER() OVER (PARTITION BY CustomerId) FROM Orders; I get the error “Syntax error: Unexpected ‘(‘ (opening parenthesis) Does MySQL not support this? I’m pretty …