2 rows for each

[XpertWorlock]

What I'm trying to do is group by supplies but return 2 of each and than sort by date

 

 

TABLE NAME : FRUIT

 

supplies | type |  date

 

4 |    apple    |    1357656565

4 |    orange    |    123333321

4 |    pear      |    1212312321

6 |    grape        |  1312312321

6 |    grapefuit    |  13121312321

 

Results in :

 

4 | apple | 1357656565

4 | orange | 123333321

6 |    grape        |  1312312321

6 |    grapefuit    |  13121312321

 

 

For the life of me I can't get my brain to wrap around it.  I'm pretty sure I've done this before and it had to be done by using a subquery.

 

Can anyone help?

[Jessica]

Are you saying you want the top 2 for each "supplies" column?

 

If that's not the case, then what you want isn't clear to me anyway.

[XpertWorlock]

What I would like is it to be sorted by date ASC but get 2 results for each supply.  Here I have a better example that people can understand better:

 

 

Table : Comments

post_idcommentdate

123Hi there1312323443

123How are you131232454

123How are you Jim1312342454

146How are you131232454

146How are you131232454

 

 

Output :

 

post_idcommentdate

123How are you131232454

123How are you Jim1312342454

146How are you131232454

146How are you131232454

 

 

 

So in this example, I would want to take 2 of the latest comments by each post

 

[jazzman1]

You need to redesign a database structure!

 

About your question:

SELECT `post_id`,`comment`, FROM_UNIXTIME(`date`) as `timeDate` FROM `Comments` HAVING(`timeDate`) <> FROM_UNIXTIME(1312342454) 

[XpertWorlock]

I think I got it working properly using (a modified version of) the query from the following page

 

http://stackoverflow.com/questions/2596489/how-to-select-the-last-two-records-for-each-topic-id-in-mysql

[jazzman1]

I think I got it working properly using (a modified version of) the query from the following page

 

http://stackoverflow.com/questions/2596489/how-to-select-the-last-two-records-for-each-topic-id-in-mysql

 

Ah...., that's different. You got it.

Now you have a unique id for each record, that's why I said "to redesign a database structure" 

[XpertWorlock]

Yeah I had the idea of doing something similar to this, I just couldn't figure out how to do it properly.

 

For future reference to anyone viewing this thread, it does work

[jazzman1]

Yeah I had the idea of doing something similar to this, I just couldn't figure out how to do it properly.

For future reference to anyone viewing this thread, it does work

Yep, this will work with one table and an unique id, but the query is a little complex:

Take a look at example:

+----+---------+-----------------+------------+

| id | post_id | comment        | date      |

+----+---------+-----------------+------------+

|  1 |      1 | Hi there        | 1312323443 |

|  2 |      1 | How are you    | 131232454  |

|  3 |      1 | How are you Jim | 1312342454 |

|  4 |      2 | How are you    | 131232454  |

|  5 |      2 | How are you    | 131232454  |

|  6 |      1 | New comment    | 1234245    |

+----+---------+-----------------+------------+

 

SELECT `c`.`id`, `c`.`comment`, FROM_UNIXTIME(`date`) as `timeDate`
FROM `comments` `c`
WHERE `c`.`id` IN (
    SELECT MAX(`c1`.`id`)
    FROM `comments` `c1`
    WHERE `c`.`post_id` = `c1`.`post_id`
              ) OR`c`.`id` IN (
    SELECT MAX(`c1`.`id`)
    FROM `comments` `c1`
    WHERE `c`.`post_id` = `c1`.`post_id` AND 
    `c1`.`id` NOT IN (
        SELECT MAX(`c2`.`id`)
        FROM `comments` `c2`
        WHERE `c1`.`post_id` = `c2`.`post_id`
                )
              )       
ORDER BY `c`.`post_id`, `c`.`id`

 

RESULT:

 

+----+-----------------+---------------------+

| id | comment        | timeDate            |

+----+-----------------+---------------------+

|  2 | How are you    | 1974-02-27 16:27:34 |

|  3 | How are you Jim | 2011-08-02 23:34:14 |

|  4 | How are you    | 1974-02-27 16:27:34 |

|  5 | How are you    | 1974-02-27 16:27:34 |

+----+-----------------+---------------------+

[jazzman1]

PS. The last id # 6, I was added it, just to check whether the query return 2 last results and forgot to delete it.

Sorry about it