Startseite » Foren » Newsgroup Archiv » comp.databases.mysql

Can I do this in one query?

4 replies [Letzter Beitrag]
27. October 2009 - 14:55
Thomas Gabrielsen
Offline
Joined: 12.10.2009
Beiträge:

Hi!

Like the subject says, is this possible to do this in one query?

I want to select three projects projects sorted by the 'ordering'
field and for each of this projects I want three rows from the
'content' table selcted by 'projectId' ordered by ordering.

Here is the two tables I use, the way I do it now, and the result:

--
-- Table structure for table `project`
--

CREATE TABLE `project` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL,
`customer` varchar(255) NOT NULL,
`description` text NOT NULL,
`published` tinyint(1) NOT NULL default '1',
`ordering` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D137 ;

--
-- Dumping data for table `project`
--

INSERT INTO `project` (`id`, `title`, `customer`, `description`,
`published`, `ordering`) VALUES
(135, 'FESTEMEKANISME / NETTKROK', 'SCANSIS AS', 'Fotballm=E5l,
ballbinger etc. ordreproduserer spr=F8ytest=F8pte produkter i thermoplast
med vekt fra 0,5 til 1000 gram i st=F8pemaskiner med lukketrykk fra 22
tonn til 330 tonn. Vi arbeider med alle typer oppdrag innenfor alle
typer bransjer, og vi st=F8per ogs=E5 detaljer med formverkt=F8y produsert
av andre leverand=F8rer. \r\n\r\nForm-Tek produserer st=F8peverkt=F8y i ege=
t
maskineringsverksted. Vi bruker CAD / CAM gjennom hele prosessen til
ferdig formverkt=F8y. ', 1, 3),
(131, 'BATTERIPAKKE TIL FORSVARET', 'GYLLING TEKNIKK AS', 'Forsvaret
ordreproduserer spr=F8ytest=F8pte produkter i thermoplast med vekt fra 0,5
til 1000 gram i st=F8pemaskiner med lukketrykk fra 22 tonn til 330 tonn.
Vi arbeider med alle typer oppdrag innenfor alle typer bransjer, og vi
st=F8per ogs=E5 detaljer med formverkt=F8y produsert av andre leverand=F8re=
r.
\r\n\r\nForm-Tek produserer st=F8peverkt=F8y i eget maskineringsverksted.
Vi bruker CAD / CAM gjennom hele prosessen til ferdig formverkt=F8y. ',
1, 1),
(130, 'CAT5 VEGGUTTAK', 'ELKO AS', 'Vi har laget CAT5 vegguttaksbokser
for Elko i en =E5rrekke.', 1, 4),
(136, 'NORR=D8NA SPORT AS', 'DETALJER TIL SEKK', 'Norr=F8na Sport
ordreproduserer spr=F8ytest=F8pte produkter i thermoplast med vekt fra 0,5
til 1000 gram i st=F8pemaskiner med lukketrykk fra 22 tonn til 330 tonn.
Vi arbeider med alle typer oppdrag innenfor alle typer bransjer, og vi
st=F8per ogs=E5 detaljer med formverkt=F8y produsert av andre leverand=F8re=
r.
\r\n\r\nForm-Tek produserer st=F8peverkt=F8y i eget maskineringsverksted.
Vi bruker CAD / CAM gjennom hele prosessen til ferdig formverkt=F8y. ',
1, 2);

--
-- Table structure for table `content`
--

CREATE TABLE `content` (
`contentId` int(11) NOT NULL auto_increment,
`projectId` int(11) NOT NULL,
`img` text NOT NULL,
`ordering` int(11) NOT NULL default '0',
PRIMARY KEY (`contentId`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 COMMENT=3D'img'
AUTO_INCREMENT=3D133 ;

--
-- Dumping data for table `content`
--

INSERT INTO `content` (`contentId`, `projectId`, `img`, `ordering`)
VALUES
(127, 135, 'DSC_0011.jpg', 6),
(131, 131, 'DSC_00011.jpg', 2),
(126, 131, 'DSC_0004.jpg', 7),
(130, 136, 'DSC_0002.jpg', 4),
(129, 136, 'DSC_00111.jpg', 8),
(125, 136, 'DSC_0001.jpg', 3),
(128, 130, 'DSC_0014.jpg', 5),
(132, 135, 'DSC_0010.jpg', 1);

This is the way I do it now:

$conn =3D mysql_connect( 'server', 'user', 'password' );
mysql_select_db( 'database', $conn );

$query1 =3D "SELECT p.id, p.title, p.customer, p.ordering
FROM project p
WHERE p.published =3D 1
ORDER BY p.ordering LIMIT 3 ";

$result1 =3D mysql_query( $query1, $conn );
$projects =3D array();
while( $projectRow =3D mysql_fetch_object( $result1 ) )
{
$project =3D array();
$project['numRows'] =3D count( $projectRow );
$project['projectId'] =3D $projectRow->id;
$project['title'] =3D $projectRow->title;
$project['customer'] =3D $projectRow->customer;
$project['imgs'] =3D array();

$query2 =3D "SELECT c.projectId, c.img, c.ordering
FROM content c
ORDER BY c.ordering LIMIT 3 ";

$result2 =3D mysql_query( $query2, $conn );
while( $contentRow =3D mysql_fetch_object( $result2 ) )
{
$imgArray =3D array();
$imgArray['img'] =3D $contentRow->img;
$imgArray['ordering'] =3D $contentRow->contentordering;
$imgArray['projectId'] =3D $contentRow->id;
array_push( $project['imgs'], $imgArray);
}
array_push( $projects, $project );
}

foreach( $projects as $pro )
{
echo 'projectId =3D ' . $pro['projectId'] . '
';
foreach( $pro['imgs'] as $img )
{
echo 'imagename =3D ' . $img['img'] . '
';
}
echo '=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
';
}

This outputs:

projectId =3D 131
imagename =3D DSC_0010.jpg
imagename =3D DSC_00011.jpg
imagename =3D DSC_0001.jpg
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
projectId =3D 136
imagename =3D DSC_0010.jpg
imagename =3D DSC_00011.jpg
imagename =3D DSC_0001.jpg
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
projectId =3D 135
imagename =3D DSC_0010.jpg
imagename =3D DSC_00011.jpg
imagename =3D DSC_0001.jpg
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Noch keine Bewertungen
Oben
  • 198 Aufrufe
27. October 2009 - 15:05
#1
Thomas Gabrielsen
Offline
Joined: 12.10.2009
Beiträge:

On 27 Okt, 14:55, Thomas Gabrielsen wrote:
> Hi!
>
> Like the subject says, is this possible to do this in one query?
>
> I want to select three projects projects sorted by the 'ordering'
> field and for each of this projects I want three rows from the
> 'content' table selcted by 'projectId' ordered by ordering.
>
> Here is the two tables I use, the way I do it now, and the result:
>
> --
> -- Table structure for table `project`
> --
>
> CREATE TABLE `project` (
> =A0 `id` int(11) NOT NULL auto_increment,
> =A0 `title` varchar(255) NOT NULL,
> =A0 `customer` varchar(255) NOT NULL,
> =A0 `description` text NOT NULL,
> =A0 `published` tinyint(1) NOT NULL default '1',
> =A0 `ordering` int(11) NOT NULL default '0',
> =A0 PRIMARY KEY =A0(`id`)
> ) ENGINE=3DMyISAM =A0DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D137 ;
>
> --
> -- Dumping data for table `project`
> --
>
> INSERT INTO `project` (`id`, `title`, `customer`, `description`,
> `published`, `ordering`) VALUES
> (135, 'FESTEMEKANISME / NETTKROK', 'SCANSIS AS', 'Fotballm=E5l,
> ballbinger etc. ordreproduserer spr=F8ytest=F8pte produkter i thermoplast
> med vekt fra 0,5 til 1000 gram i st=F8pemaskiner med lukketrykk fra 22
> tonn til 330 tonn. Vi arbeider med alle typer oppdrag innenfor alle
> typer bransjer, og vi st=F8per ogs=E5 detaljer med formverkt=F8y produser=
t
> av andre leverand=F8rer. \r\n\r\nForm-Tek produserer st=F8peverkt=F8y i e=
get
> maskineringsverksted. =A0Vi bruker CAD / CAM gjennom hele prosessen til
> ferdig formverkt=F8y. =A0', 1, 3),
> (131, 'BATTERIPAKKE TIL FORSVARET', 'GYLLING TEKNIKK AS', 'Forsvaret
> ordreproduserer spr=F8ytest=F8pte produkter i thermoplast med vekt fra 0,=
5
> til 1000 gram i st=F8pemaskiner med lukketrykk fra 22 tonn til 330 tonn.
> Vi arbeider med alle typer oppdrag innenfor alle typer bransjer, og vi
> st=F8per ogs=E5 detaljer med formverkt=F8y produsert av andre leverand=F8=
rer.
> \r\n\r\nForm-Tek produserer st=F8peverkt=F8y i eget maskineringsverksted.
> Vi bruker CAD / CAM gjennom hele prosessen til ferdig formverkt=F8y. ',
> 1, 1),
> (130, 'CAT5 VEGGUTTAK', 'ELKO AS', 'Vi har laget CAT5 vegguttaksbokser
> for Elko i en =E5rrekke.', 1, 4),
> (136, 'NORR=D8NA SPORT AS', 'DETALJER TIL SEKK', 'Norr=F8na Sport
> ordreproduserer spr=F8ytest=F8pte produkter i thermoplast med vekt fra 0,=
5
> til 1000 gram i st=F8pemaskiner med lukketrykk fra 22 tonn til 330 tonn.
> Vi arbeider med alle typer oppdrag innenfor alle typer bransjer, og vi
> st=F8per ogs=E5 detaljer med formverkt=F8y produsert av andre leverand=F8=
rer.
> \r\n\r\nForm-Tek produserer st=F8peverkt=F8y i eget maskineringsverksted.
> Vi bruker CAD / CAM gjennom hele prosessen til ferdig formverkt=F8y. ',
> 1, 2);
>
> --
> -- Table structure for table `content`
> --
>
> CREATE TABLE `content` (
> =A0 `contentId` int(11) NOT NULL auto_increment,
> =A0 `projectId` int(11) NOT NULL,
> =A0 `img` text NOT NULL,
> =A0 `ordering` int(11) NOT NULL default '0',
> =A0 PRIMARY KEY =A0(`contentId`)
> ) ENGINE=3DMyISAM =A0DEFAULT CHARSET=3Dutf8 COMMENT=3D'img'
> AUTO_INCREMENT=3D133 ;
>
> --
> -- Dumping data for table `content`
> --
>
> INSERT INTO `content` (`contentId`, `projectId`, `img`, `ordering`)
> VALUES
> (127, 135, 'DSC_0011.jpg', 6),
> (131, 131, 'DSC_00011.jpg', 2),
> (126, 131, 'DSC_0004.jpg', 7),
> (130, 136, 'DSC_0002.jpg', 4),
> (129, 136, 'DSC_00111.jpg', 8),
> (125, 136, 'DSC_0001.jpg', 3),
> (128, 130, 'DSC_0014.jpg', 5),
> (132, 135, 'DSC_0010.jpg', 1);
>
> This is the way I do it now:
>
> $conn =3D mysql_connect( 'server', 'user', 'password' );
> mysql_select_db( 'database', $conn );
>
> $query1 =3D =A0 =A0 =A0 "SELECT p.id, p.title, p.customer, p.ordering
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 FROM project p
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 WHERE p.published =3D 1
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ORDER BY p.ordering LIMIT=
3 ";
>
> $result1 =3D mysql_query( $query1, $conn );
> $projects =3D array();
> while( $projectRow =3D mysql_fetch_object( $result1 ) )
> {
> =A0 =A0 =A0 =A0 $project =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0=3D array();
> =A0 =A0 =A0 =A0 $project['numRows'] =A0 =A0 =A0 =A0 =A0 =A0 =3D count( $p=
rojectRow );
> =A0 =A0 =A0 =A0 $project['projectId'] =A0 =3D $projectRow->id;
> =A0 =A0 =A0 =A0 $project['title'] =A0 =A0 =A0 =A0 =A0 =A0 =A0 =3D $projec=
tRow->title;
> =A0 =A0 =A0 =A0 $project['customer'] =A0 =A0=3D $projectRow->customer;
> =A0 =A0 =A0 =A0 $project['imgs'] =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=3D array=
();
>
> =A0 =A0 =A0 =A0 $query2 =3D =A0 =A0 =A0 "SELECT c.projectId, c.img, c.ord=
ering
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 FROM cont=
ent c
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ORDER BY =
c.ordering LIMIT 3 ";
>
> =A0 =A0 =A0 =A0 $result2 =3D mysql_query( $query2, $conn );
> =A0 =A0 =A0 =A0 while( $contentRow =3D mysql_fetch_object( $result2 ) )
> =A0 =A0 =A0 =A0 {
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 $imgArray =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =3D array();
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 $imgArray['img'] =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0=3D $contentRow->img;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 $imgArray['ordering'] =A0 =3D $contentRow=
->contentordering;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 $imgArray['projectId'] =A0=3D $contentRow=
->id;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 array_push( $project['imgs'], $imgArray);
> =A0 =A0 =A0 =A0 }
> =A0 =A0 =A0 =A0 array_push( $projects, $project );
>
> }
>
> foreach( $projects as $pro )
> {
> =A0 =A0 =A0 =A0 echo 'projectId =3D ' . $pro['projectId'] . '
';
> =A0 =A0 =A0 =A0 foreach( $pro['imgs'] as $img )
> =A0 =A0 =A0 =A0 {
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 echo 'imagename =3D ' . $img['img'] . ' r />';
> =A0 =A0 =A0 =A0 }
> =A0 =A0 =A0 =A0 echo '=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

';
>
> }
>
> This outputs:
>
> projectId =3D 131
> imagename =3D DSC_0010.jpg
> imagename =3D DSC_00011.jpg
> imagename =3D DSC_0001.jpg
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> projectId =3D 136
> imagename =3D DSC_0010.jpg
> imagename =3D DSC_00011.jpg
> imagename =3D DSC_0001.jpg
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> projectId =3D 135
> imagename =3D DSC_0010.jpg
> imagename =3D DSC_00011.jpg
> imagename =3D DSC_0001.jpg
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

There was an error in the first message. query2 should have a WHERE
clause like this:

$query2 =3D "SELECT c.projectId, c.img, c.ordering
FROM content c
WHERE c.projectId =3D $projectRow->id
ORDER BY c.ordering LIMIT 3 ";

Thanks,
Thomas

Oben
27. October 2009 - 15:54
#2
toby
Offline
Joined: 12.10.2009
Beiträge:

On Oct 27, 8:55=A0am, Thomas Gabrielsen
wrote:
> Hi!
>
> Like the subject says, is this possible to do this in one query?
>
> I want to select three projects projects sorted by the 'ordering'
> field and for each of this projects I want three rows from the
> 'content' table selcted by 'projectId' ordered by ordering.
>

Yes, it can be done in one query. Here is an approach to look at:

mysql> SELECT P.id, T.contentId, T.rank, T.img
FROM ( SELECT id,ordering
FROM project ORDER BY ordering LIMIT 3 ) P
JOIN ( SELECT L.contentId, L.projectId, L.img,
COUNT(*) AS rank
FROM content L JOIN content R
ON R.projectId =3D L.projectId
AND R.ordering <=3D L.ordering
GROUP BY L.contentId ) T
ON T.projectId =3D P.id
WHERE T.rank <=3D 3
ORDER BY P.ordering, P.id, T.rank;

+----+----------+-----+--------------
| id | contentId | rank | img |
+----+----------+-----+--------------
| 131 | 131 | 1 | DSC_00011.jpg |
| 131 | 126 | 2 | DSC_0004.jpg |
| 136 | 125 | 1 | DSC_0001.jpg |
| 136 | 130 | 2 | DSC_0002.jpg |
| 136 | 129 | 3 | DSC_00111.jpg |
| 135 | 132 | 1 | DSC_0010.jpg |
| 135 | 127 | 2 | DSC_0011.jpg |
+----+----------+-----+--------------
7 rows in set (0.00 sec)

I get different results, so perhaps I have not fully understood your
description. Also there does not seem to be enough sample data to give
3x3 results, or to test the limits.

Oben
27. October 2009 - 16:05
#3
Thomas Gabrielsen
Offline
Joined: 12.10.2009
Beiträge:

On 27 Okt, 15:54, toby wrote:
> On Oct 27, 8:55=A0am, Thomas Gabrielsen
> wrote:
>
> > Hi!
>
> > Like the subject says, is this possible to do this in one query?
>
> > I want to select three projects projects sorted by the 'ordering'
> > field and for each of this projects I want three rows from the
> > 'content' table selcted by 'projectId' ordered by ordering.
>
> Yes, it can be done in one query. Here is an approach to look at:
>
> mysql> SELECT P.id, T.contentId, T.rank, T.img
> =A0 =A0 =A0 =A0FROM ( SELECT id,ordering
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 FROM project ORDER BY ordering LIMIT 3 ) P
> =A0 =A0 =A0 =A0 =A0 =A0 JOIN ( SELECT L.contentId, L.projectId, L.img,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 COUNT(*) AS rank
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM content L JOIN content R
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ON R.projectId =
=3D L.projectId
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND R.ordering <=
=3D L.ordering
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0GROUP BY L.contentId ) T
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ON T.projectId =3D P.id
> =A0 =A0 =A0 =A0WHERE T.rank <=3D 3
> =A0 =A0 =A0 =A0ORDER BY P.ordering, P.id, T.rank;
>
> +----+----------+-----+--------------
> | id =A0| contentId | rank | img =A0 =A0 =A0 =A0 =A0 |
> +----+----------+-----+--------------
> | 131 | =A0 =A0 =A0 131 | =A0 =A01 | DSC_00011.jpg |
> | 131 | =A0 =A0 =A0 126 | =A0 =A02 | DSC_0004.jpg =A0|
> | 136 | =A0 =A0 =A0 125 | =A0 =A01 | DSC_0001.jpg =A0|
> | 136 | =A0 =A0 =A0 130 | =A0 =A02 | DSC_0002.jpg =A0|
> | 136 | =A0 =A0 =A0 129 | =A0 =A03 | DSC_00111.jpg |
> | 135 | =A0 =A0 =A0 132 | =A0 =A01 | DSC_0010.jpg =A0|
> | 135 | =A0 =A0 =A0 127 | =A0 =A02 | DSC_0011.jpg =A0|
> +----+----------+-----+--------------
> 7 rows in set (0.00 sec)
>
> I get different results, so perhaps I have not fully understood your
> description. Also there does not seem to be enough sample data to give
> 3x3 results, or to test the limits.

Hi toby

Thank you very much for your reply! You're right that there isn't
enough data in to get 3x3 result, but that is thre result I want
anyway. I forgot the WHERE clause as I added in my previous message. I
know this is much to ask for, but could you go through your query very
quickly and explain what it is doing? I will try to figure it out my
self of course, but if you could give me some comments I would
appriciate it very much!

Thanks,
Thomas

Oben
27. October 2009 - 17:37
#4
toby
Offline
Joined: 12.10.2009
Beiträge:

On Oct 27, 11:05=A0am, Thomas Gabrielsen
wrote:
> On 27 Okt, 15:54, toby wrote:
>
>
>
>
>
> > On Oct 27, 8:55=A0am, Thomas Gabrielsen
> > wrote:
>
> > > Hi!
>
> > > Like the subject says, is this possible to do this in one query?
>
> > > I want to select three projects projects sorted by the 'ordering'
> > > field and for each of this projects I want three rows from the
> > > 'content' table selcted by 'projectId' ordered by ordering.
>
> > Yes, it can be done in one query. Here is an approach to look at:
>
> > mysql> SELECT P.id, T.contentId, T.rank, T.img
> > =A0 =A0 =A0 =A0FROM ( SELECT id,ordering
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 FROM project ORDER BY ordering LIMIT 3 ) P
> > =A0 =A0 =A0 =A0 =A0 =A0 JOIN ( SELECT L.contentId, L.projectId, L.img,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 COUNT(*) AS rank
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM content L JOIN content R
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ON R.projectId =
=3D L.projectId
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND R.ordering <=
=3D L.ordering
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0GROUP BY L.contentId ) T
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ON T.projectId =3D P.id
> > =A0 =A0 =A0 =A0WHERE T.rank <=3D 3
> > =A0 =A0 =A0 =A0ORDER BY P.ordering, P.id, T.rank;
>
> > +----+----------+-----+--------------
> > | id =A0| contentId | rank | img =A0 =A0 =A0 =A0 =A0 |
> > +----+----------+-----+--------------
> > | 131 | =A0 =A0 =A0 131 | =A0 =A01 | DSC_00011.jpg |
> > | 131 | =A0 =A0 =A0 126 | =A0 =A02 | DSC_0004.jpg =A0|
> > | 136 | =A0 =A0 =A0 125 | =A0 =A01 | DSC_0001.jpg =A0|
> > | 136 | =A0 =A0 =A0 130 | =A0 =A02 | DSC_0002.jpg =A0|
> > | 136 | =A0 =A0 =A0 129 | =A0 =A03 | DSC_00111.jpg |
> > | 135 | =A0 =A0 =A0 132 | =A0 =A01 | DSC_0010.jpg =A0|
> > | 135 | =A0 =A0 =A0 127 | =A0 =A02 | DSC_0011.jpg =A0|
> > +----+----------+-----+--------------
> > 7 rows in set (0.00 sec)
>
> > I get different results, so perhaps I have not fully understood your
> > description. Also there does not seem to be enough sample data to give
> > 3x3 results, or to test the limits.
>
> Hi toby
>
> Thank you very much for your reply! You're right that there isn't
> enough data in to get 3x3 result, but that is thre result I want
> anyway. I forgot the WHERE clause as I added in my previous message. I
> know this is much to ask for, but could you go through your query very
> quickly and explain what it is doing? I will try to figure it out my
> self of course, but if you could give me some comments I would
> appriciate it very much!

The key is the 2nd derived table:

SELECT L.contentId, L.projectId, L.img, COUNT(*) AS rank
FROM content L
JOIN content R ON R.projectId =3D L.projectId
AND R.ordering <=3D L.ordering
GROUP BY L.contentId

Using a self join, this finds the 'rank' of each contentId (based on
ordering), within each projectId. We use this rank to restrict the
outer query to 'top three'.

Just be aware this approach may not perform well on large datasets;
and make sure you have suitable indexes.

>
> Thanks,
> Thomas

Oben