Get one single row of data from a mysql field

Question

So I have a MySQL database with the following fields on the "main" table:

Table main: id name age photos
            3  John 22  photo1.jpg
                        photo2.jpg
                        photo3.jpg
                        photo4.png
            72 Diane 31 photo1.jpg
                        photo2.png
            33 Doe   26 photo1.png
                        photo2.png
                        photo3.jpg

This is just an example to ilustrate my real problem: And I'm querying this database with the following code snippet:

 // getJson
 router.get('/getJson', async function(req, res) {
  var objs = [] ;
  res.setHeader('Content-Type', 'text/plain' );

  try {

 var  querySql = 'SELECT id,name,age,photos from main';

  var result = await pool.query( querySql )
  Object.keys(result).forEach(function(key) {
    var row = result[key] ; 
objs.push({"uid":row.id,"prevDesc":row.name,"identifier":row.age,"title":row.photos});}
    });
  res.end( JSON.stringify(objs) );
    } catch(err) {
   console.log(err);
 });

My problem is the following: After querying the database using a query like this one,I get the following result:

    3 John 22 photo1.jpg
    3 John 22 photo2.jpg
    3 John 22 photo3.jpg
    3 John 22 photo4.png
    72 Diane 31 photo1.jpg
    72 Diane 31 photo2.png
    ...
    ...

And so on.My question is:How could I get only the first photo from every row/person profile ? I mean something like:

     3 John 22 photo1.jpg
     72 Diane 31 photo1.jpg
     33 Doe   26 photo1.png

I want to mention that I'm working with NodeJS + ExpressJS. Thank you for help!

How do you define *first photo*? SQL tables represent *unordered* sets. There is no "first" unless a column defines first. — Gordon Linoff, Oct 29 '18 at 22:10
. . If those are the only columns, your question is not solvable on this table. It would be like asking for the number of pixels in the image or the native language of the person. The information is not in the table. — Gordon Linoff, Oct 29 '18 at 22:27
Good start would be using the id column with primary key and auto_increment option or a datetime column with on update current_timestamp option.. Then you should be able to indentify the first foto in a group.. — Raymond Nijland, Oct 29 '18 at 22:40

score 2 · Accepted Answer · answered Oct 29 '18 at 22:11

2

You can get one photo using aggregation:

select id, name, age, min(photo)
from t
group by id, name, age;

There is no first photo unless you have another column that specifies the ordering.

answered Oct 29 '18 at 22:11

Gordon Linoff

1,242,037
58
646
786

Get one single row of data from a mysql field

1 Answers1