oracle sql hero's, please come here

[Rabbo]

Edited, table has 87 millions rows of data...

 

i have 4 columns below,    how can i get the max order_date row from the below table , which is 12345    4321    9999    18-Mar-20

and 1000    4000    1111    16-Dec-19

 

customer_id	product_id	customer_product_id	order_date
12345	4321	9999	18-Mar-20
12345	4321	None	18-Dec-19
12345	4321	9999	16-Feb-18
1000	4000	1111	16-Dec-19
1000	4000	1111	14-Dec-19

[HEROO]

https://www.oratable.com/select-rows-with-max-value/

[ring_master]

1 minute ago, Rabbo said:

i have 4 columns blow, how can i get the max order_date row from the below table 

12345

4321

9999

18-Mar-20

 

 

 

customer_id	product_id	customer_product_id	order_date
12345	4321	9999	18-Mar-20
12345	4321	None	18-Dec-19
12345	4321	9999	16-Feb-18

Select * from table t

order by order_date desc

limit 1

[Nalla_Balu]

Select * from order

where order_date = ( select max(order_date) from order)

 

it’s basic sql bro

[Arey_Neekundhi]

select customer_id,product_id,customer_product_id,max(order_date)

from table t

where t. customer_id=  some condition

group by customer_id,product_id,customer_product_id

[Rabbo]

edit chesa question, tell now...

[Arey_Neekundhi]

20 minutes ago, Rabbo said:

edit chesa question, tell now...

idhi work avvaledha ?

 

select customer_id,product_id,customer_product_id,max(order_date)

from table t

where t. customer_id=  some condition

group by customer_id,product_id,customer_product_id

[Rabbo]

1 minute ago, Arey_Neekundhi said:

idhi work avvaledha ?

 

select customer_id,product_id,customer_product_id,max(order_date)

from table t

where t. customer_id=  some condition

group by customer_id,product_id,customer_product_id

No bro, i will get both  of this rows in example

12345	4321	9999	18-Mar-20
12345	4321	None	18-Dec-19

[Arey_Neekundhi]

2 minutes ago, Rabbo said:

No bro, i will get both  of this rows in example

12345	4321	9999	18-Mar-20
12345	4321	None	18-Dec-19

oh ya, two different customer_product_id values kadha..  if you don't need that column you can simply lift that. 

[Rabbo]

i got it by using 

 

select customer_id,product_id,customer_product_id,order_date
from   ( select customer_id,product_id,customer_product_id,order_date, rank() over 
(partition by customer_id,product_id  order by order_date desc) as rnk
         from   table t
       )
where  rnk = 1

[Rabbo]

2 minutes ago, Arey_Neekundhi said:

oh ya, two different customer_product_id values kadha..  if you don't need that column you can simply lift that. 

oorko bro...mari comedy chestavu...

[Arey_Neekundhi]

good..

 

[ARYA]

59 minutes ago, Rabbo said:

Edited, table has 87 millions rows of data...

 

i have 4 columns below,    how can i get the max order_date row from the below table , which is 12345    4321    9999    18-Mar-20

and 1000    4000    1111    16-Dec-19

 

customer_id	product_id	customer_product_id	order_date
12345	4321	9999	18-Mar-20
12345	4321	None	18-Dec-19
12345	4321	9999	16-Feb-18
1000	4000	1111	16-Dec-19
1000	4000	1111	14-Dec-19

use any window function row/rank/dense_rank , partition by prod id, cust id and order by date desc..

[Pitta]

12 minutes ago, Rabbo said:

i got it by using 

 

select customer_id,product_id,customer_product_id,order_date
from   ( select customer_id,product_id,customer_product_id,order_date, rank() over 
(partition by customer_id,product_id  order by order_date desc) as rnk
         from   table t
       )
where  rnk = 1

[ARYA]

4 minutes ago, Pitta said:

fittaa