MYSQL: Retrieve latest unique records

Question

I have a table that logs rows with some duplicate data.

The column fields are as follows:
id: primary key, auto inc.
member_id: int index
item_qt: int

id, member_id, item_qt, timestamp

553, 107, 10, time
552, 107, 25, time
551, 122, 6, time
550, 122, 9, time
549, 107, 19, time

I would like to do a few things:

How would you select the latest unique rows,
sum the item_qt,
join on a 'username' table. cols: id, name
and sort descending

Anyone have suggestions for a subselect query
without the high overhead of filesorting? Or possibly another reconfiguration of the data?

This is very similar: I just can't get the syntax correct.
http://stackoverflow.com/questions/1227619/retrieve-last-latest-distinct-highest-value

Answer 1

Not sure how all this fits together as I don't 100% understand what you are asking...

To get the last row in the table

select top 1 * from [table] order by id desc

To sum a colum

select sum(item_qt) as SumItem_qt from [table]

For joins just google "inner join"

Sort desc is just order by column name desc

Answer 2

Sorry for not being clear. If you refer to the db table row info i've given above:

I want to retrieve the latest rows per unique member id. Without joining on the username table, here is my very slow code for what I want to retrieve.

Something like:

SELECT * FROM table1
WHERE id IN
( SELECT max( id ) FROM table1 GROUP BY member_id )
ORDER BY id DESC LIMIT 5

To retrieve:
553, 107, 10, time
551, 122, 6, time

I also wanted to sum all(item_qt) for each member_id within the last 5 minutes in the same query. I think this may be more complicated...

Answer 3

OK then use...

SELECT un.name, t.item_qt
FROM TABLE t
JOIN USERNAME un ON un.id = t.member_id
ORDER BY t.id DESC LIMIT 1;

You could also use say LIMIT 5 to get 5 records...

When you say slow what do you mean? There may appear to be a problem that we don't know about. I suspect there may be lots of records and no indexes....

It may be wise to post the schema of both tables so we can help you further.... Also give an indication of number of rows in each table....