Akina
  • Member for 3 years, 9 months
  • Last seen this week
  • Zelenograd, Moscow, Russia
How to delete duplicate records in MySQL, in a table without IDs?
Accepted answer
15 votes

There is no any field combination which identifies the record uniqually. I see at least 2 different solutions. First solution: move unique records to a copy of table and replace original table. ...

View answer
How to update Column related to Row Number
Accepted answer
12 votes

UPDATE contact SET ContactNumber = (@rownum := 1 + @rownum) + 500 WHERE 0 = (@rownum:=0) -- for a given client, for example, Client_Id=123, use -- WHERE Client_Id = 123 + (@rownum:=0) ORDER BY id;

View answer
Is possible to reuse a already calculatesd column into a query in MySQL?
Accepted answer
11 votes

You can use user-defined variable. SELECT @temp := ( /* Complex query */ ) AS myValue, IF ( @temp > 10, "OK" , "" ) FROM table; Maybe server demands preliminary variable definition. If so,...

View answer
GROUP BY needs ORDER BY NULL to avoid filesort
Accepted answer
11 votes

MySQL 5.7 Reference Manual / ... / SELECT Syntax If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the ...

View answer
Sqlite: Finding the next or previous element in a table consisting of integer tuples
Accepted answer
9 votes

At the moment I try to find the next tuple with ("center" is (1-1-1-3)) Test SELECT * FROM tuples WHERE (1,1,1,3) < (a,b,c,d) ORDER BY a,b,c,d LIMIT 1 fiddle

View answer
PostgreSQL; Update different column on condition using CASE statement
Accepted answer
7 votes

It is to be something like (logic only) update table_A set column_A = case when (column_A>table_B.balance) then value else column_A end, column_B = case when not (column_A>table_B....

View answer
Converting multiple rows to one row
Accepted answer
6 votes

Main idea: WITH cte AS ( SELECT UserId, CheckTime, LEAD(CheckTime) OVER (PARTITION BY UserId ORDER BY CheckTime) NextTime, CheckType, ...

View answer
Results of using a comparison operator in the ORDER BY clause
Accepted answer
6 votes

This is common comparison operators. When comparison is performed then some boolean result is produced. It may be TRUE, FALSE or NULL. These 3 values are related as NULL < FALSE < TRUE This ...

View answer
MSSQL: Generate a 8 digit serial/Barcode number from a prefix number
Accepted answer
6 votes

DECLARE @prefix INT; SET @prefix = 573071; WITH cte1 AS (SELECT 0 num UNION ALL SELECT num+1 FROM cte1 WHERE num < 9) SELECT num, @prefix*100+num*10+9-((@prefix/100000+(@prefix/...

View answer
How to UPDATE from JOIN with GROUP BY
Accepted answer
6 votes

UPDATE table2 AS b1, ( SELECT b.id, MIN(IFNULL(a.views, 0)) AS counted FROM table1 a JOIN table2 b ON a.id = b.id GROUP BY id ...

View answer
How to get last and first occurrence of items into separated columns?
6 votes

Test: SELECT t_min_max.name, t_min.value AS value_1st, CASE WHEN min_id <> max_id THEN t_min.value END AS value_last FROM ( SELECT name, min(id) AS min_id, max(id) AS max_id FROM ...

View answer
MySQL set column value if another column's new value is different than current one
Accepted answer
6 votes

All expressions in the SET section are evaluated in the order in which they are written. So mycolumn in the second expression is equal to the value set in the first expression. Simply switch ...

View answer
Meaning of "rows between 15 preceding and 15 following"
Accepted answer
5 votes

This means that avg(num_trips) is calculated not over all rows but over 31 rows (max) - 15 adjacent rows with trip_date lower than in current row, current row and 15 adjacent rows with trip_date ...

View answer
Understanding NOT EXISTS
Accepted answer
5 votes

Let's move the problematic expression from WHERE condition to SELECT output list. EXISTS gives boolean, and SQL server don't want to display it directly, so we'll use CASE and convert it to readable ...

View answer
remove leading and trailing quotes
5 votes

SELECT TRIM(' "' FROM fieldname) fieldname FROM tablename TRIM() function requires SQL Server 2017.

View answer
Get ids only where one id is null and other isn't
Accepted answer
5 votes

WITH cte AS ( SELECT id, num, COUNT(*) OVER (PARTITION BY id) total, COUNT(num) OVER (PARTITION BY id) notnull FROM ...

View answer
Get percentage of group by
Accepted answer
5 votes

WITH number_of_tickets AS ( SELECT user_id, COUNT(user_id) AS number_of_tickets_per_user FROM tickets GROUP BY user_id ), total_tickets AS ( SELECT SUM(number_of_tickets_per_user) ...

View answer
MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains non aggregated column fixed bug reappear every restart
Accepted answer
5 votes

That's not a bug. That's 'move to standard'. Do not fix this problem by change server's SQL Mode. Fix it by altering the queries which uses partial GROUP BY expressions. Details can be read in ...

View answer
Does the timestamp type have (hidden) milliseconds?
4 votes

Is there any way to extract more accuracy from updated? No. It is not possible to select the data which is absent in the table and cannot be calculated from the data present in the tables. does ...

View answer
Removing leading zeros from month and day
Accepted answer
4 votes

Use FM format modifier: to_char(tips_chronic_pain_weekly_selection.start_date, 'YYYY/FMMM/FMDD'::text) This modifies suppresses leading zeros and padding spaces.

View answer
A table that has two foreign keys to the same column in another table?
Accepted answer
4 votes

You must join 2 copies of players table. SELECT m.id, p1.name player_1, p2.name player_2 FROM matches m JOIN players p1 ON m.player_1 = p1.id JOIN players p2 ON m.player_2 = p2.id

View answer
Get the highest parent id recursively in MySQL
Accepted answer
4 votes

WITH RECURSIVE cte as ( SELECT id, id nextid, parentid FROM t1 UNION ALL SELECT cte.id, t1.id, t1.parentid FROM t1 JOIN cte ON cte.parentid = t1.id ) ...

View answer
How to change an incorrect datatype for a birth date field from VARCHAR to DATE
4 votes

ALTER TABLE users ADD COLUMN DOB DATE; UPDATE users SET DOB = STR_TO_DATE(birthdate, '%Y-%m-%d') WHERE birthdate REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}'; https://dbfiddle.uk/?rdbms=mysql_5.7&...

View answer
Do these joins change anything
Accepted answer
4 votes

CREATE TABLE a (id INT, id_b INT); CREATE TABLE b (id INT, id_c INT); CREATE TABLE c (id INT); INSERT INTO c VALUES (1), (1), (2), (3), (3), (4), (6); INSERT INTO b VALUES (1,1), (1,1), (2,2), (2,2), (...

View answer
Create new table with a sum where row from table 2 matches field in table 1 and aggregate on another field
4 votes

SELECT t1.code_1, SUM(t2.measure_1) FROM table1 t1 NATURAL JOIN table2 t2 GROUP BY t1.code_1 A working example can be found at db<>fiddle where I used the PostgreSQL 10 engine. You can build on ...

View answer
Merge Multiple Selects in one request
Accepted answer
4 votes

SELECT Date_, application_name, SUM(SUBSTR(Statistics_Variables,36,1)='1') total1, SUM(SUBSTR(Statistics_Variables,38,1)='1') total2 FROM pdetail WHERE Application_Name='...

View answer
What's difference between MySql IN and ANY operator?
Accepted answer
4 votes

What's difference between MySql IN and ANY operator? There is 2 differences between them. IN is an operator whereis ANY is not, it is a keyword and may be treated as a comparison operator modifier (...

View answer
Count Over returns Row_Number based on Order By
Accepted answer
4 votes

If I run this query I will get the ROW_NUMBER() instead of the COUNT() OVER Clause (Transact-SQL) If ORDER BY is not specified entire partition is used for a window frame. This applies only to ...

View answer
LEFT JOIN confusion
4 votes

You have the list of foods. So you'll obtain all of them (using left join), even they are not present on orders. But you have NO the list of persons. So if you want to obtain all of them even they ...

View answer
Get time from Unix milliseconds timestamp
Accepted answer
4 votes

Universal solution (for datetime >= '2001-09-09 04:46:40' - see comments). SELECT FROM_UNIXTIME(@time * POWER(10, 9 - FLOOR(LOG10(@time)))); @time variable contains unix timestamp (any accuracy). ...

View answer
1
2 3 4 5
32