CODEWITHSUNDEEP
mysqlsqldatabase
sandeep pawar
sandeep pawar

Reputation: 39

How to select all the rows having same and latest value in a particular column from database mysql

I want to select all the rows that are having the same and latest time in a particular column i.e., time1. The below image gives a brief idea about my question. Please guide me with this. Thank you in advance.

enter image description here

Upvotes: 0

Views: 58

Answers (3)

GMB
GMB

Reputation: 222402

Here is a solution that relies on window function RANK(), available since MySQL 8.0:

SELECT *
FROM (SELECT id, name, time1, RANK() OVER(ORDER BY time1 DESC) rnk FROM mytable) x
WHERE rnk = 1

The inner query assignes a rank to each record, sorted by descending time; top ties get the same rank, 1. Then the outer query just filters in records having rank 1.

Upvotes: 1

JIJOMON K.A
JIJOMON K.A

Reputation: 1280

Please try this ,

select * from table1 where time1  =(select time1 from table1 order by time1 limit 0,1);

or

select * from table1 where time1  = ( select max(time1) from table1 )

or

 select * from table1 where time1=(select TOP 1 time1 from table1 order by time1 desc  )

DEMO

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a sunquery for max time 

        select * from my_table  
        where time1  = (
          select max(time1)
          from my_table  
        )

Upvotes: 1

Related Questions