CODEWITHSUNDEEP
mysqldatabase
Aamna D
Aamna D

Reputation: 45

How to write a query to fetch recent records from database table

I have created a table i.e., table_1 which has different fields. I want to write a query to fetch the latest records from column part_name1 with issue_priority based on time. Column part_name1 may contain same record with different priorities like p1, p2 or p3. Here I have taken less number of records but if I have n number of records how to fetch the latest among the inserted records.

Just an example is given here. Please check out the given image link

**part_name1**      **issue_priority**    **time1**

    blower              p1                 03:15:00
    reverse             p2                 03:16:18
    blower              p2                 04:11:30
    reverse             p3                 04:44:05

Output: From the above table i want the output to be as mentioned below

blower       p2
reverse      p3

image link

Upvotes: 1

Views: 1576

Answers (4)

gaborsch
gaborsch

Reputation: 15758

First, you need to query the latest time for each part_name1. This gives you the maximum time values.

SELECT part_name1, max(time1) as maxtime1
FROM table_1
GROUP BY part_name1;

If you also need the other data in the table (e.g. issue_priority), you should use the above as a subquery:

SELECT t1.*
FROM table_1 t1
JOIN (
    SELECT part_name1, max(time1) as maxtime1
    FROM table_1
    GROUP BY part_name1 ) s1
ON (t1.part_name1  = s1.part_name1 AND t1.time1 = s1.maxtime1);

This will return you the latest row for each part_name1. You can add further LIMIT clause, if you want.

Upvotes: 1

sandeep pawar
sandeep pawar

Reputation: 39

Use max(time1) instead of min(time1) to fetch latest inserted records.

SELECT t1.*
FROM table_1 t1
JOIN (
SELECT part_name1, max(time1) as maxtime1
FROM table_1 
GROUP BY part_name1 ) s1
ON (t1.part_name1  = s1.part_name1 AND t1.time1 = s1.maxtime1);

Upvotes: 1

Someguywhocodes
Someguywhocodes

Reputation: 781

You could use ORDER BY:

SELECT part_name1, issue_priority
FROM table_1
ORDER BY time1 DESC
LIMIT 2

Limit 2 will give you the two most recent records. If you remove the LIMIT then you will get all records ordered by time1 from newest to oldest.

https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

Upvotes: 0

alexandru.tapirdea
alexandru.tapirdea

Reputation: 1

You just need to use ORDER BY : 

SELECT fields FROM table ORDER BY time1 DESC LIMIT 2;

In your case, fields is part_name1, table is the name of your table and LIMIT 2 is because you want exactly the last 2 records ( can be set to any number ).

Upvotes: 0

Related Questions