Oracle Guru's Ki Oka Question

[Kakateyudu]

na table ela vundhi

 

ID_NUM, Created_Date

1                3-10-2016  

2                2-10-2008

1                3-10-2012   

2                2-10-2014

1                3-10-2011   

2                2-10-2010

1                3-10-2009   

2                2-10-2016

 

aa table meda oka view create cheyyali.... It should return only 1 row per ID_NUM and that row should be the one with latest created_date.

 

aa paina example ki output below la vundali

 

ID_NUM, Created_Date

1                3-10-2016   

2                2-10-2016

 

CITI#H@  CITI#H@

 

 

 

 

[kiladi bullodu]

Select * from

[Kakateyudu]

Select * from

Endhi vayya * ? Anni rows vaddu naku

[mettastar]

SELECT ID_NUM, CREATED_DATE FROM 

(

select 

ID_NUM,

CREATED_DATE,

MAX(CREATED_DATE) OVER(PARTITION BY ID_NUM) LATEST_DATE

FROM TABLE

) A

WHERE CREATED_DATE=LATEST_DATE

[UNITED99]

Select * from

 

 

bro nuvvvu select * from KOTTESEMU ani type cheyyadam marichipoyyav

[andhravodu]

SELECT ID_NUM, CREATED_DATE FROM 

(

select 

ID_NUM,

CREATED_DATE,

MAX(CREATED_DATE) OVER(PARTITION BY ID_NUM) LATEST_DATE

FROM TABLE

) A

WHERE CREATED_DATE=LATEST_DATE

 

max vadi group by cheyaledu, wrong answer

[mettastar]

max vadi group by cheyaledu, wrong answer

lol.. adi analytical function antaru vuncle.. no group needed.. im partitioning in the function 

[andhravodu]

lol.. adi analytical function antaru vuncle.. no group needed.. im partitioning in the function 

 

ok, got it.

[Kakateyudu]

SELECT ID_NUM, CREATED_DATE FROM
(
select
ID_NUM,
CREATED_DATE,
MAX(CREATED_DATE) OVER(PARTITION BY ID_NUM) LATEST_DATE
FROM TABLE
) A
WHERE CREATED_DATE=LATEST_DATE

Oka ID_Num ki same created_date vunte oka row kavali baa.. This returns all those rows

[mettastar]

Oka ID_Num ki same created_date vunte oka row kavali baa.. This returns all those rows

paina distinct padeyyi aithe aipodhi

[andhravodu]

Oka ID_Num ki same created_date vunte oka row kavali baa.. This returns all those rows

 

idi try cheyi

select
ID_NUM,
CREATED_DATE

FROM

(

SELECT ID_NUM, CREATED_DATE, row_number() OVER(PARTITION BY ID_NUM ORDER BY CREATED_DATE DESC) AS RNK
FROM TABLE
)
WHERE RNK = 1;

 

 

Inko approach dorikindi search cheste, idi test kudirite cheppu

 

select * id_num, latest_date from

(
select
ID_NUM,
CREATED_DATE,
MAX(CREATED_DATE) KEEP (DENSE_RANK LAST ORDER BY CREATED_DATE)

 OVER(PARTITION BY ID_NUM) LATEST_DATE
FROM TABLE
)

 

[mettastar]

idi try cheyi

select
ID_NUM,
CREATED_DATE

FROM

(

SELECT ID_NUM, CREATED_DATE, DENSE_RANK() OVER(PARTITION BY ID_NUM ORDER BY CREATED_DATE DESC) AS RNK
FROM TABLE
)
WHERE RNK = 1

deeniki kuda rendu records vastayi... 

[andhravodu]

deeniki kuda rendu records vastayi... 

 

hmm. akkada row_number petta, ippudu correct ga vastundi

[Kakateyudu]

paina distinct padeyyi aithe aipodhi

Distinct pettina multiple coming baa

[Kakateyudu]

idi try cheyi
select
ID_NUM,
CREATED_DATE
FROM
(
SELECT ID_NUM, CREATED_DATE, row_number() OVER(PARTITION BY ID_NUM ORDER BY CREATED_DATE DESC) AS RNK
FROM TABLE
)
WHERE RNK = 1;


Inko approach dorikindi search cheste, idi test kudirite cheppu

select * id_num, latest_date from
(
select
ID_NUM,
CREATED_DATE,
MAX(CREATED_DATE) KEEP (DENSE_RANK LAST ORDER BY CREATED_DATE)
OVER(PARTITION BY ID_NUM) LATEST_DATE
FROM TABLE
)

Top one working baa... Tnx u da man

Will try below one too