how to select only 1 record of each id

Asked

Viewed 2,126 times

2

After making an Inner Join between user (1) and image (N) tables, the following table is returned (the id refers to the user id and the filename to the image file):

| id | filename

| 12 | img1.png

| 12 | img2.png

| 13 |img3.png

| 13 |img4.png

How to recover only 1 image of each id? (without using Where = id, because at the time of query, I do not have id to compare.)

mysql sql

  • You need to explain better what you want. The way you are is not possible to answer

  • Post your SQL also for analysis?

2 answers

2

Use a Group By Id

Something like

Select * from Tabela
Group By Id

  • The problem with that is that you have no control over which image is returned to a given id, in his example.

  • Exactly agree @bfavaretto but he also did not specify that he wants to return one, not which

  • Okay, that makes sense. But I think that’s his next question ;)

2


I’d do it this way:

SELECT u.*, i.imagem as imagem_usuario FROM usuario u 
LEFT JOIN imagem i ON(i.id_usuario=u.id) GROUP BY u.id_usuario ORDER BY u.id_usuario ASC

This way, we can also sort by id, to know if you take the first record or the last record.

  • See my comment to Otto below.

  • Yes, but we can order in this case.

  • 1

    Sorting is no use (in Mysql), you would need to do a subquery.

  • It would also be possible

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.