My English is not so well and I am new with php. So, maybe you can be bored with my question. I am really sorry for that. Here’s the Question: I have a table created in SQL database like this: – ID Name PICTURE – 1 John john.jpg – 1 dora h.jpg – 1 Dane hkjpg – 2 Mougli f.jp…
Tag: mysql
Limit total number of results across tables
I want to get the most recent 100 events that happened. The events are scattered across multiple tables. Here is an example: This will return up to 300 records. I would then take the result set, order by timestamp, and take the first 100 records. How can I perform this in a single SQL query, where SQL will on…
MySQL – JOIN with table on partial string match
Trying to LEFT JOIN a table with partial string match. I’ve tried changing the wildcard in the LIKE condition, but it still only searches for the full string. Any suggestions? I’ve got a list of product abbreviations stored in a table. Would be easier to manage this list with partial matching vers…
Inner join 2 tabelas and get last result
I’m having trouble writing the query that returns the last id by date of a table and fetching the name of that id from another table Result above 2| 1 |2014-04-04 | ok 6| 2 |2014-04-04 | ok 4| 3 |2014-04-04 | ok ficha_cadastro SQ_FICHA|NOME| 1|gabriel 2|carlos 3|manuel ficha_evento id|dia_atendimento|li…
Transpose Rows to Columns Dynamically in MySQL – Page Rank Per User
I have the following data frame. I want to create a new table with the 4 most viewed pages per user (page and number of times viewed). So, I need to transpose the following columns: page, quantity and rank. Note that the variable to order is the rank and not necessarily all the names were in all the pages. Ne…
Multiple JOINs to the same subquery expression
I’ve recently been teaching myself SQL, and have been working on a toy project to do so. Here is a sample schema: As part of this project, I have a query that gets a list of users, with information populated relative to the currently authenticated user. A simplified version of the query looks like this:…
SQL how can we get monthly trend from 2 separated columns start_date and end_date?
Imagine we have the following data: The question here is to get the 2020 monthly trend of active paid subscriptions. For each subscriber (ID), we can only count the months that they are active. So for S1, we can only count S1 active in Jan 2020 and March 2020, not Feb 2020. During the interview, I wrote a fun…
mysql: Select all rows and compare with other table
I have two tables and need to get all rows from the first one and then check which values from the second table match the first table. My goal is to get all so called ‘achievements’ and then check which one the user has reached. achievements achievement_user My desired result for a query where I c…
Order by multiple columns not working as expected
I have this sql : The result is : Expected result : But I want to order and by month not only by year. I tried like this ORDER BY dc.year DESC, dc.month ASC, d. user_id ASC, but not working as expected because I want to liste each user order by year desc and by month asc. Can you help
Comapring DATE_ADD with NOW not working on MySQL
I’ve try to compare an DateTime in MySQL. The table is to check locked users. For sample, an User will be locked for 1 Day at the DateTime 2020-04-09 14:51:32: until is the datetime time_created with added days from locked_time. Here is the Result of entries: The calculation (time_created with added loc…