Tagged Questions
61
votes
4answers
47k views
SQL Select only rows with Max Value on a Column
I have this table for documents (simplified version here):
+------+-------+--------------------------------------+
| id | rev | content |
...
10
votes
4answers
24k views
MYSQL : First and last record of a grouped record (aggregate functions)
I am trying to do fectch the first and the last record of a 'grouped' record. More precisely, I am doing a query like this
SELECT MIN(low_price), MAX(high_price), open, close
FROM symbols
WHERE date ...
18
votes
3answers
13k views
Extra Fields with SQL MIN() & GROUP BY
When using the SQL MIN() function, along with GROUP BY, will any additional columns (not the MIN column, or one of the GROUP BY columns) match the data in the matching MIN row?
For example, given a ...
27
votes
5answers
58k views
MySQL: Select N rows, but with only unique values in one column
Given this data set:
ID Name City Birthyear
1 Egon Spengler New York 1957
2 Mac Taylor New York 1955
3 Sarah Connor Los Angeles 1959
4 ...
7
votes
1answer
4k views
MySQL: GROUP_CONCAT with LEFT JOIN
I'm experiencing a problem with MySQL's "GROUP_CONCAT" function. I will illustrate my problem using a simple help desk database:
CREATE TABLE Tickets (
id INTEGER NOT NULL PRIMARY KEY,
...
6
votes
4answers
19k views
SELECTING with multiple WHERE conditions on same column
Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column...
My table is a very simple ...
3
votes
1answer
783 views
MySQL dynamic cross tab
I have a table like this:
way stop time
1 1 00:55
1 2 01:01
1 3 01:07
2 2 01:41
2 3 01:47
2 5 01:49
3 1 ...
1
vote
3answers
839 views
mysql: select the last 10 messages and for each message the last 3 replies
For simplicity lets strip down the messages table to its minimum, with some sample data
message_id reply_to createdate
1 0 123
2 0 124
3 0 ...
8
votes
5answers
2k views
Is it possible to perform a bitwise group function?
I have a field in a table which contains bitwise flags. Let's say for the sake of example there are three flags: 4 => read, 2 => write, 1 => execute and the table looks like this*:
user_id ...
8
votes
1answer
441 views
MySQL aggregate function problem
In the following example, why does the min() query return results, but the max() query does not?
mysql> create table t(id int, a int);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into ...
2
votes
3answers
3k views
MySQL: How to SUM() a TIMEDIFF() on a group?
So I've got a set of results that looks something like this:
SELECT User_ID, StartTime, EndTime, TIMEDIFF(EndTime, StartTime) AS TimeDiff
FROM MyTable
...
5
votes
7answers
202 views
Find row with maximum value of id in MySQL
Take a look at the MySQL table below called "Articles":
+----+-----------+---------+------------------------+--------------------------+
| id | articleId | version | title | content ...
3
votes
1answer
100 views
MySql Query help
I am doing a query to get the number of builds per day from our database for the last 30 days. But it has become needed to marked days where there were no builds also.
In my WHERE clause I use ...
2
votes
1answer
6k views
How to group mysql rows with same column value into one row?
I have to tables, keywords and data.
Table keywords have 2 columns (id, keyword), table data have 3 columns (id[foreign key of keywords.id], name, value).
I am using this query:
SELECT k.id, ...
1
vote
4answers
370 views
SQL - Select 'n' greatest elements in group
The SQL MAX aggregate function will allow you to select the top element in a group. Is there a way to select the top n elements for each group?
For instance, if I had a table of users that held their ...
