No doubt everybody has solved it before, but ten seconds on Google didn’t show any obvious results, so here we go: How to pick the top 5 records of each group.
(more…)
August 21, 2006
Top N of a Group
August 8, 2006
Staging Table
Can MySQL handle a staging table, one where a fixed number of rows are reused in a round-robin basis (e.g., temporarily store AJAX-driven auto-saved forms, without overwriting the real values so the user can still abandon changes), without an external script?
First thoughts turned to events, triggers, stored procedures, and such toys in the most recent versions of MySQL – none of which worked quite right. Turns out we can do it with a little one-time setup and a single statement that will work as far back as version 4.0.4.
(more…)
August 1, 2006
Sum top N of a group
Trying to sum the top 5 entries in each group.
SELECT
theId,
SUM(theData) FROM (
(SELECT
@count := 0,
@oldId := 0 AS theId,
0 AS theData)
UNION
(SELECT
@count := if(@oldId = id, @count+1, 0),
@oldId := id,
if(@count < 5, a, 0)
FROM
(SELECT * FROM theTable ORDER BY id, a DESC) AS theTable
)
) AS theView
WHERE theId != 0
GROUP BY theId;
Table population:
INSERT INTO `thetable` VALUES
(1,1), (1,2), (1,3), (1,4),
(1,5), (1,6), (1,7), (2,1),
(2,2), (2,3), (2,4), (2,5);
July 11, 2006
Quoth the maven
Straight quotes can be used like backticks around aliases IN SOME CASES (as of 5.0.15, at least). Observe:
CREATE TABLE myTable (id int, someData int);
INSERT INTO myTable VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2);
These three queries produce identical output:
SELECT * FROM myTable GROUP BY id;
SELECT * FROM myTable GROUP BY `id`;
SELECT * FROM myTable GROUP BY 'id';
(more…)
June 16, 2006
Unified Rows
Here are a couple table definitions:
CREATE TABLE `users1` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);
CREATE TABLE `users2` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);
After inserting some data, we get the following row counts:
SELECT COUNT(*) FROM users1;
+----------+
| COUNT(*) |
+----------+
| 15 |
+----------+
SELECT COUNT(*) FROM users2;
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+
Now, for this query:
SELECT * FROM users1 UNION SELECT * FROM users2;
How many rows will be in the result set? (Choose the best answer)
a) Exactly 27
b) At least 12
c) At least 15
d) Between 1 and 27
e) Zero or more
November 19, 2005
Concatenating NULL
CREATE TABLE myTable ( id INT, string varchar(100) DEFAULT NULL ); INSERT INTO myTable (id, string) VALUES (1, "First"), (2, NULL); UPDATE myTable SET string = CONCAT(id, " has the value ", string); SELECT * FROM myTable;
What is the output?
Show Answer ▼
November 18, 2005
Alias
What would result from the following?
CREATE TABLE my_table ( id INT ); INSERT INTO my_table (id) VALUES (2.9), (3), (3.4), (3.9), (4); SELECT * FROM my_table AS m WHERE my_table.id > 3;
November 17, 2005
Truncation
Assume the following has completed correctly:
CREATE TABLE test ( charField varchar(5) );
What is the end difference between the following two statements?
INSERT INTO test (charField) VALUES ("123456");
INSERT INTO test (charField) VALUES ("12345 ");
November 16, 2005
Alias
What would result from the following?
CREATE TABLE my_table (
id INT
);
INSERT INTO my_table (id) VALUES (2.9), (3), (3.4), (3.9), (4);
SELECT * FROM my_table AS m WHERE my_table.id > 3;
Show Answer ▼
Passwords
Assume the following have completed:
GRANT ALL ON test1.* TO 'johnQ'@'localhost' IDENTIFIED BY 'pass1'; GRANT ALL ON test2.* TO 'johnQ'@'localhost' IDENTIFIED BY 'pass2';
User johnQ has a query that joins tables from test1 to tables from test2. How should he log in?
a) Using ‘pass1’.
b) Using ‘pass2’.
c) Using ‘pass1’ and ‘pass2’, separated by a colon.
d) It can’t be done; he must change the two passwords to match.
e) Other (explain)
Show Answer ▼