My input table looks like: id user_id at 1 1 2020-12-07 13:07:35 2 794 2020-12-07 13:12:17 3 794 2020-12-07 13:12:26 4 794 2020-12-07 13:12:27 5 263 2020-12-07 13:12:50 6 263 2020-12-07 13:12:50 7 263 2020-12-07 13:13:10 8 1029 2020-12-07 13:18:07 9 1029 2020-12-07 13:18:07 10 378 2020-12-07 13:19:10 11 378 2…
Tag: mysql
MySQL query to select a record from each group that meets a user-defined criterion
I am looking for a query that selects a record from each group that meets a user-defined criterion. I can explain with the following illustrative table: A simple GROUP BY categoryID query returns the first record in each group, as shown below: To return the last record in each group we can use the approach su…
I want to update my table columns based on another one
I have one database that I’ve created a year ago but then I’ve created a new one recently and added some new columns into tables. I want to update my ex database with new schema without losing my data….
MySQL use JSON_CONTAINS with a subquery
I’m trying to make a subquery inside JSON_CONTAINS to have a results like this This is what I have tried But since the subery query returns Client instead of “Client” JSON_CONTAINS doesn’t accept the value. ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_contains: …
Query help in SQL
I have the below table, with empid, deptid, and Name. I need to get the results as empid,deptid,name and count of employees in each department. I need to get the results as empid,deptid,name, and count of employees in each department as below. I am able to achieve results using the below queries. Is it possib…
What is the type casting hierarchy in mysql?
Taking the following example: If I only include the first select statement in the cte, the type will be int. If I include the first and second, the type will be DECIMAL, and if I include all three, the type will be cast to VARCHAR (regardless of position of the three statements). How does mysql determine whic…
Mysql Conditional Aggregation Using group_by query and Case to group Month and Year
I’ve a table structure where I’m saving the data in the below format. The ledger amount needs to be divided into two columns and grouped through the sum equation. Those rows having receipt_id not as null their ledger_amount needs to be displayed as sum(ledger_amount) as credit whole rows having pa…
Can I use DECLARE as an SQL statement in PHP
I am trying to execute a query in PHP, but this code wouldn’t work for some reason, it doesn’t detect the keywords at all(DECLARE, SELECT, WHERE). Is there anything I can do and after all can I even use DECLARE in PHP as a mySQLi statement. $sql2 =”DECLARE @MaxID INT; SELECT @MaxID = MAX(pro…
mysql date conversion returns null converting certain months
I have this query (take a look on between dates): The select is between dates: startDate: (STR_TO_DATE(’11/24/2020′,’%m/%d/%y’)) finishDate: (STR_TO_DATE(’12/26/2021′,’%m/%d/%y’)) This query will return something because there are records on year 2020 the proble…
how to cast input in “SELECT * FROM PRODUCT LIMIT CAST(select CAST(‘1’ AS UNSIGNED)), 5”?
this will give error for syntax also tried create view to replace the (select CAST(‘1’ AS UNSIGNED)), which still doesnt work I am using mysql. Answer LIMIT clause cannot take expressions or variables. Therefore if you absolutely need such functionality, you must do a huge workaround – you h…