Select one row for each array value

Question

I would like to fetch one row in my MySQL database for every value in an array. What I'm trying to do is get the posts which were most recently voted on. The votes table has the following structure

| id | postid | voter | vote type |    time    |
|====|========|=======|===========|============|
| 1  |   1    |   1   |     1     | 1445389824 |
| 2  |   2    |   6   |     1     | 1445408529 |  
| 3  |   1    |   5   |     2     | 1445435978 |

I would like to select the posts that were most recently voted on, in the order they were voted on. So, for example, because the ids of the votes ordered by time from greatest to lowest is 3, 2, 1, I would like to select the post ids 1, 2, 1. But, because 1 appears twice, I would only like to select the first one, so the final result would be 1, 2.

This table is going to be very, very large, so selecting every post id and then trimming it to the desirable array using php doesn't seem like a very good idea.

Also, only the posts that are in an array should be selected. For example, selecting all of the posts without omitting duplicates would be

SELECT `postid`
FROM `votes`
WHERE `postid` IN ($posts)
ORDER BY `time` DESC

But by using this method, I would have to get rid of the duplicate entries using php, which seems like it would be very intensive.

I would also like to select the number of votes on each post in the list. I could do this in a separate query, but doing it in one would probably be faster. So, for example

SELECT COUNT(`id`)
FROM `votes`
WHERE `postid` IN ($posts)
ORDER BY `time` DESC

Would select all of the votes on the posts given. Instead, I would like it to select an array of the votes for each post, or something that could be converted to that.

Is there any MySQL operator that would allow me to select the number of votes on each post included in the array, and order them by the time the most recent post was voted on? In the above table, because there are 2 votes on post 1, and 1 vote on post 2, the result would be

array("1" => 2, "2" => 1)

There is DISTINCT postid, which would give you only one ID back, instead of 1,2,1, you'd get 1,2. I believe the order would be preserved as well. — Jared Farrish
– Jared Farrish, Commented Dec 22, 2015 at 21:39
Something sort've like this: SELECT DISTINCT id, COUNT(*) AS num_votes FROM votes ORDER BY id, `time` DESC GROUP BY id — Jared Farrish
– Jared Farrish, Commented Dec 22, 2015 at 21:42

trincot · Accepted Answer · 2015-12-22 21:52:04Z

3

Here is a possible query to get both the time of the latest vote and vote count per post:

SELECT   `postid`, 
          MAX(time) as time,
          COUNT(*) as vote_count
FROM     `votes`
WHERE    `postid` IN ($posts)
GROUP BY `postid`
ORDER BY 2 DESC

If you want all the other fields of these latest votes records, then you could use the above as a sub-query of a larger one:

SELECT     `id`, `postid`, `voter`, 
           `vote_type`, `time`, vote_count
FROM       `votes` v
INNER JOIN (
            SELECT   `postid`, 
                      MAX(time) as time,
                      COUNT(*) as vote_count
            FROM     `votes`
            WHERE    `postid` IN ($posts)
            GROUP BY `postid`) filter
        ON  v.`postid` = filter.`postid`
        AND v.`time` = filter.time 
ORDER BY `time` DESC

edited Dec 22, 2015 at 21:52

answered Dec 22, 2015 at 21:43

trincot

357k38 gold badges282 silver badges338 bronze badges

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

1 Comment

Jojodmo Over a year ago

Thanks! Here's the query I ended up using

SELECT postid, sum(case when votetype = '2' then 1 else 0 end) up_count, sum(case when votetype = '1' then 1 else 0 end) down_count FROM votes WHERE postid IN ($posts) GROUP BY postid ORDER BY MAX(time) DESC

, which selects the number of up and down votes for each post instead of the total

Dan · Accepted Answer · 2015-12-22 21:55:07Z

1

So you want to get the latest vote for each post in an array. I think you can just add a GROUP BY clause.

SELECT `postid`, COUNT(postid) AS votecount
FROM `votes`
WHERE `postid` IN ($posts)
GROUP BY `postid`
ORDER BY MAX(`time`)

edited Dec 22, 2015 at 21:55

answered Dec 22, 2015 at 21:42

Dan

11.2k5 gold badges26 silver badges36 bronze badges

1 Comment

Dan Over a year ago

oh yea, forgot about that.

Collectives™ on Stack Overflow

Select one row for each array value

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related