You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields: SELECT tt.* FROM topten tt INNER JOIN (SELECT home, MAX(datetime) AS MaxDateTime FROM topten GROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime
I have briefly discussed this question in a table so you can conclude whether to go with InnoDB or MyISAM. Here is a small overview of which db storage engine you should use in which situation: MyISAM InnoDB —————————————————————- Required full-text search Yes 5.6.4 —————————————————————- Require transactions Yes —————————————————————- Frequent select queries Yes —————————————————————- Frequent … Read more
TEXT and BLOB may by stored off the table with the table just having a pointer to the location of the actual storage. Where it is stored depends on lots of things like data size, columns size, row_format, and MySQL version. VARCHAR is stored inline with the table. VARCHAR is faster when the size is … Read more
Do a SELECT with a GROUP BY clause. Let’s say name is the column you want to find duplicates in: SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1; This will return a result with the name value in the first column, and a count of how many times that value … Read more
From the documentation (MySQL 8) : Type | Maximum length ———–+————————————- TINYTEXT | 255 (2 8−1) bytes TEXT | 65,535 (216−1) bytes = 64 KiB MEDIUMTEXT | 16,777,215 (224−1) bytes = 16 MiB LONGTEXT | 4,294,967,295 (232−1) bytes = 4 GiB Note that the number of characters that can be stored in your column will … Read more
I would recommend using INSERT…ON DUPLICATE KEY UPDATE. If you use INSERT IGNORE, then the row won’t actually be inserted if it results in a duplicate key. But the statement won’t generate an error. It generates a warning instead. These cases include: Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints. Inserting … Read more
To get all tables with columns columnA or ColumnB in the database YourDatabase: SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘columnA’,’ColumnB’) AND TABLE_SCHEMA=’YourDatabase’;
A query may fail for various reasons in which case both the mysql_* and the mysqli extension will return false from their respective query functions/methods. You need to test for that error condition and handle it accordingly. mysql_ extension: NOTE The mysql_ functions are deprecated and have been removed in php version 7. Check $result … Read more
To add a unique constraint, you need to use two components: ALTER TABLE – to change the table schema and, ADD UNIQUE – to add the unique constraint. You then can define your new unique key with the format ‘name'(‘column1’, ‘column2’…) So for your particular issue, you could use this command: ALTER TABLE `votes` ADD … Read more
IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below… Previous versions can’t do that with DATETIME… But you can do it with TIMESTAMP: mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.00 sec) mysql> … Read more