I have a table (called users) I need rank of users based on their score but I want rank on the bases of users max score. Expect result Answer You are looking for DENSE_RANK, But it supports mysql version higher than 8.0 use correlated-subquery to get max value by each User_id use two variables one to store rank another to
Tag: mysql
Why is MySQL SUM query only returning one row?
I am trying to create a MySQL query to return the sum of all statement balances for each account in my DB. The query looks like this: SELECT SUM(balance), handle FROM statement_versions INNER JOIN …
How to pass a php variable in WHERE clause of SELECT statement?
I have a php variable that I want to fetch another field in database using this variable in my where clause. My code: But this is printing me nothing. Don’t know where I am going wrong but any help would be appreciated. Answer You can either break the string and concatenate the variable with “..” Or you could wrap the
SQL: Give the names of employees who earn more than their manager
Hi guys, I have this type of table, and I need to show the employees who earn more than there managers? It would be easier if we had 2 tables, but I cannot imagine a code within one table. Any ideas? Answer You can do “self-join” between the two tables. In the second table, EMPNO will be equal to the
How to select a photo from the photo table for each post by query in MySQL
I have two table I want to show one photo for every post but when the run query show post for every photo Photo table : +————————————————————+ | photo_id …
MySQL Select distinct rows with same id with different value in a column and excluded a specific value in another column
I have a MySQL database table like following: id task_id users 1 1 1 1 2 2 2 1 2 2 2 1 2 10 1 3 1 2 3 …
Searching multiple rows that are not in a relationship n:m
Similar question here Very similar to the question above but with a slight difference, I need to find a list of users that haven’t seen at least one film in a list of movies. Assuming two tables ‘…
Mysql: GROUP BY custom date interval
Situation : a table where every minute X energy device saves its consumption. I have to calculate the daily consumption (at hourly intervals, 00 – 01 – 02 – 03 – 04 ….. 23) of a single device on a …
select every 100 lines, one line SQL request? [closed]
I have a table that contains 50000 lines for example. I need to select 50 lines from table that contains this 50000 lines, so I need to select every 1000 lines, one line. for example divide 50000/50, …
PHP Database Query foreign key data from database
I try to build a simple blog system. database table like below. Posts +—-+———-+————-+ |id | title | content | +—-+———-+————-+ |1 | Tile | Content | +…