Cannot write mysql query i want

Question

I have a table (Counts) with many entries like this:

ID | userid | count | entrydate

where ID is an autoinc PK, userid refers to my Users table, count is a random integer, and entrydate is a unix timestamp.

I also have a table called Listmembers:

listid | userid

where both fields together are PK, and listid refers to some list information.

I want to write a query that returns the most recently inputted count value for each user in a specific list.

I tried variations with GROUP BY (only returns the first inputted data item per userid, not the most recent), with ORDER BY (but this returns all counts for the relevant users), with user selection inside my query (where userid IN (..)) and outside the query (join listmembers on ..). No query resulted in what i want to achieve :-/

James Curran · Accepted Answer · 2010-08-23 20:28:38Z

3

select c.userid, c.count  from counts C
inner join (select userid, max(entrydate)  
            from Counts group by userid) g 
   on c.userid =g.userid and c.entrydate = g.entrydate
inner join Listmembers L on l.userid = c.userid
where l.listid = @desiredList

(Note: I've never actually used mysql. That should be the T-Sql syntax, but it should be standard enough to work on an SQL)

answered Aug 23, 2010 at 20:28

James Curran

104k37 gold badges186 silver badges264 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tominator Over a year ago

i see what you did there, but is it efficient when there are many entries in Counts? To select from it twice?

Ihor Kaharlichenko Over a year ago

That's going to work. The only thing you forgot is filtering by listid. Consider adding it.

Tominator Over a year ago

yes, it does work (with slight modifications not important to the question), so now i'm wondering efficiency :-) Anyone have data on that?

James Curran Over a year ago

@Ihor: Did our posts cross in the ether? I have added filtering, but I though I had it up before your comment.

Undo · Accepted Answer · 2016-05-23 21:04:23Z

0

Use max() to do this:

max(entryDate)

edited May 23, 2016 at 21:04

Undo♦

25.7k38 gold badges114 silver badges132 bronze badges

answered Aug 23, 2010 at 20:26

dmr

22.5k37 gold badges105 silver badges139 bronze badges

1 Comment

Tominator Over a year ago

i don't want the entrydate though, i want the count that comes with the max(entrydate)

Eton B. · Accepted Answer · 2010-08-23 20:27:29Z

-1

SELECT c.count FROM Counts as c, Listmembers as l
 WHERE l.userid = c.userid 
        AND l.listid = 'specific_value'
        AND c.entrydate = MAX(c.entrydate)

answered Aug 23, 2010 at 20:27

Eton B.

6,3015 gold badges33 silver badges43 bronze badges

1 Comment

Tominator Over a year ago

mysql tells me "Invalid use of group function"

Collectives™ on Stack Overflow

Cannot write mysql query i want

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related