0

I have a table that contains posts. Said table has few important fields, I'd like to use for filtering, mainly

id, thread and bump.

ID, is, well, the id. I'd like to sort from newest, but I'm using the timestamp for that (bump).

Thread is the thread number. If a post IS a thread, then thread IS null.

Bump is a timestamp, of when a thread was updated OR when a post was made.

I have the following SQL query:

SELECT * FROM `posts_boardname` WHERE `thread` IS NULL ORDER BY `bump` DESC LIMIT 10 OFFSET 0

So take 10 threads and sort them by update timestamp. Simple, right? It workst just fine, yet since I also want to grab 3 last responses to each thread, I'd also like to have 3 posts, for each thread, so I came up with this:

SELECT * FROM `posts_boardname` AS thr WHERE `thread` IS NULL OR (SELECT * FROM `posts_boardname` WHERE `thread` = thr.id ORDER BY `bump` DESC LIMIT 3) ORDER BY `bump` DESC LIMIT 10 OFFSET 0

But I get an error: Operand should contain 1 column(s)

Is what I'm trying to do even possible in MySQL? I've been unable to find any satisfying answers or solutions to this problem.

Sample data:

ID | Thread | Comment   | Timestamp | Bump
-- | ------ | --------- | --------- | ----
1  | NULL   | Thread #1 | 15        | 60
2  | NULL   | Thread #2 | 20        | 20
3  | 1      | ComTT #1  | 30        | 30
4  | NULL   | Thread #3 | 33        | 33
5  | 1      | ComTT #1-2| 60        | 60
6  | NULL   | Thread #4 | 65        | 65

Let's say I want to grab 3 lastest threads + 1 last response for each thread, if there are any, so my results would be:

ID | Thread | Comment   | Timestamp | Bump
-- | ------ | --------- | --------- | ----
6  | NULL   | Thread #4 | 65        | 65
1  | NULL   | Thread #1 | 15        | 60
5  | 1      | ComTT #1-2| 60        | 60
4  | NULL   | Thread #3 | 33        | 33

I tried doing the following with UNION, but it doesn't seem to work:

(SELECT * 
 FROM `posts_boardname` as thread 
 WHERE `threads` IS NULL 
 ORDER BY `bump` 
 DESC LIMIT 10 OFFSET 0) 
UNION 
(SELECT * 
 FROM `posts_boardname` as posts 
 WHERE `threads` = thread.id 
 ORDER BY `bump` DESC LIMIT 3)

Error: #1054 Unknown column thread.id in where clausule

P.K.
  • 777
  • 1
  • 7
  • 18
  • Edit your question and provide sample data and desired results. – Gordon Linoff Jul 13 '17 at 11:53
  • @GordonLinoff Added. – P.K. Jul 13 '17 at 12:02
  • Do two queries, one for the threads and one for the posts. Combine them with `UNION`. – Barmar Jul 13 '17 at 12:13
  • @Barmar Creating sample data for a question like this is annoying, that's why I shortened the example. A good solution should work for both cases regardless as long as you change how many results I'd want. – P.K. Jul 13 '17 at 12:13
  • @Barmar I've tried using `UNION` but it doesn't seem to quite work, could you maybe take a look? I've added it to the bottom of the question. – P.K. Jul 13 '17 at 12:22
  • The two queries in a union can't refer to each other. That only works in a join. – Barmar Jul 13 '17 at 12:25
  • @Barmar so then it's impossible to achieve what I was trying to do with a single query? – P.K. Jul 13 '17 at 12:26
  • I just misunderstood what you were trying to achieve. I thought the list of threads and list of 3 post in each thread were independent queries. – Barmar Jul 13 '17 at 12:30
  • You need a join. Write a query that returns 10 threads, join it with a query that returns 3 posts from each thread. See https://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group for how to get N rows per thread. – Barmar Jul 13 '17 at 12:31

0 Answers0