0

So, I have a table with several entries of my users and I need to group by their ID's and retrieve the last register in the table.

To optimize my job, I have created a view using the query with a SELECT inside another for order by timestamp descendant, but it fails. I think it cannot build by views or already have a solution?

Please, if anyone know how can I do this, share!

Thanks!

P.S.: Sorry, here is the code:

SELECT 
    dg_users.id,
    dg_users.email,
    dg_maps.id,
    dg_maps.latitude,
    dg_maps.longitude,
    dg_maps.timestamp
FROM
    (SELECT 
        *
    FROM
        dg_maps
    ORDER BY dg_maps.timestamp DESC) dg_maps
JOIN
    dg_geologs ON dg_geologs.map_id = dg_maps.id
JOIN
    dg_users ON dg_users.id = dg_geologs.user_id
GROUP BY dg_geologs.user_id;
Alexandre Ferreira
  • 65
  • 13

1 Answers1

2

Based on that you have not published your table structure, I can only provide a general answer. I would say though that I think making a view for such a small query doesn't provide that much of an advantage, unless you have a reason listed here ( When to use database views and when not? ).

In order to create a view with the last inserted entry you would do something like this:

CREATE VIEW MyView AS
SELECT *
FROM myTable
ORDER BY TIMESTAMP DESC LIMIT 1;

Where:

  • MyView is the name you give to your view
  • myTable is your table
  • timestamp is the field with your timestamp

And you would query this with:

select * from MyView;
Community
  • 1
  • 1
Menelaos
  • 23,508
  • 18
  • 90
  • 155