CODEWITHSUNDEEP
phpmysql
BenM
BenM

Reputation: 53198

Select the earliest record in a MySQL table

Please consider the following table structure (this is sample data, so please ignore the timestamps being identical):

+---------+---------+------------+-----------+
| list_id | item_id | date_added | is_active |
+---------+---------+------------+-----------+
|    1    |     1   | 1352073600 |     1     |
|    1    |     2   | 1372073600 |     1     |
|    1    |     3   | 1332073600 |     1     |
|    1    |     4   | 1302073600 |     1     |
|    2    |     1   | 1302073600 |     1     |
|    3    |     1   | 1302073600 |     1     |
+---------+---------+------------+-----------+

Our client wishes to show how many lists were created on a certain day. The list is created when the first item is added, but the date is not explicitly stored, only the date the items have been added.

My question is this:

Using MySQL (and PHP for computing the timestamp), how can I return the number of lists that were created on a certain day. Essentially, the logic should be (pseudo-code):

It is difficult to explain what I'm looking for, so consider the following actions:

No items added to lists yet (i.e. all lists have 0 items)
User added `item_id = 1` to `list_id = 1` yesterday.
User added `item_id = 2` to `list_id = 1` today.
User added `item_id = 1` to `list_id = 2` yesterday.
User added `item_id = 1` to `list_id = 3` yesterday.
User added `item_id = 2` to `list_id = 2` today.

If we wanted to look how many lists were created yesterday (or rather, how many lists had the first item added yesterday), I would like to return the total number where the item first added = yesterday. Given the action set above, this would return a total of 3 (i.e. list_id = 1, list_id = 2 and list_id = 3).

Upvotes: 0

Views: 231

Answers (5)

Andrea
Andrea

Reputation: 396

This returns the list of lists created on a specific day:

SELECT list_id, min(date_added) AS date_creation
FROM tbl_list_items
GROUP BY list_id
HAVING min(date_added) >= @min_age AND min(date_added) <= @max_age

To count them you can count number of results of the query, or use sub-queries like:

SELECT COUNT(*) AS total FROM
(
    SELECT list_id, min(date_added) AS date_creation
    FROM tbl_list_items
    GROUP BY list_id
    HAVING min(date_added) >= @min_age AND min(date_added) <= @max_age
)

However, note that this is totally not-optimized, because you have to GROUP BY the whole table for each query; maybe will be better to add a flag ( creation = 0 | 1 ) when the first item of a list is added.

Upvotes: 1

user428517
user428517

Reputation: 4193

If you want to select records from one particular day, why would you specify min_age and max_age? You just need to compare to a single date (say, of the format YYYY-MM-DD):

select count(*) from table_name where from_unixtime(date_added, %Y-%m-%d) = $date group by list_id

or something like that.

Upvotes: 0

Marc B
Marc B

Reputation: 360572

Those look like unix timestamps, in which case you could do something like

SELECT ..
FROM ...
WHERE DATE(FROM_UNIXTIME(date_added)) = '2012-11-03'

Note that this will be inefficient, since you're comparing derived values - indexes won't be used.

Upvotes: 0

Darrrrrren
Darrrrrren

Reputation: 6078

You should have multiple tables to normalise your data. One for list, and another for list item.

Table List
ListID
ListCreatedDate
...any other list information...

Table ListItem
ListID
ListItemID
ListItemCreatedDate
...any other list item information...

This way you'll have access to the list creation date as well as individual item dates. Then you can simply join on List.ListID = ListItem.ListID to get all of your information.

Upvotes: 0

Fred Wuerges
Fred Wuerges

Reputation: 1965

Try this:

SELECT count(*) FROM tbl_list_items WHERE date_added >= :min_age and date_added <= :max_age GROUP BY date_added

Upvotes: 0

Related Questions