CODEWITHSUNDEEP
sqlsql-serveroracle-database
Nick
Nick

Reputation: 4493

How do I use max date on SQL query

I have sql query question as;

Col1, Col2, Col3, Col4
aaaa, 2018.11.06, 8.0, 400.0
aaaa, 2018.06.06, 12.0, 600.0
aaaa, 2018.02.10, 10.0, 500.0
bbbb, 2017.11.06, 8.0, 100.0
bbbb, 2017.06.06, 12.0, 300.0
bbbb, 2017.02.10, 10.0, 200.0

In select statement I need to get only biggest (newest) Col2. The correct result must be:

aaaa, 2018.11.06, 8.0, 400.0
bbbb, 2017.11.06, 8.0, 100.0

because 2018.11.06 and 2017.11.06 is the biggest(newest).

How do I use max date on SQL query on MS SQL or ORACLE?

Upvotes: 0

Views: 102

Answers (3)

Radim Bača
Radim Bača

Reputation: 10701

Quite a universal solution can be

select yt.*
from your_table yt
join (
   select col1, max(col2) max_col2
   from your_table
   group by col1
) t on t.col1 = yt.col1 and
       t.max_col2 = yt.col2

this solution will return all rows with the maximal date per col1. Another solution is to use the row_numer(), however, it can have suboptimal performance.

select *
from
(
  select *,
         row_number() over (partition by col1 order by col2 desc) rn
  from your_table
) t
where t.rn = 1

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You can use correlated subquery

select * from tablename a where col2 in 
   (select max(col2) from tablename b on a.col1=b.col1)

Upvotes: 3

apomene
apomene

Reputation: 14389

SQL Server: 

SELECT TOP 1 * FROM myTable ORDER BY Col2 DESC

Upvotes: 1

Related Questions