sql babulu ravali - urgent helf

[rajivn786]

Select * from (
Select t.*,
       rownum () over (partition by t.c1, t.c2,t.c3, t.c4, t.c5 order by t.c8 desc) rn
  from t)
where rn = 1

 

Partition by lo (except the max date keep everythig if the values are same)  ...Ikkada c8 ante changing date

[Srimantudu]

2 minutes ago, nenu_devudni said:

rank este anitiki oke rank ostundi thatha  ... nenu select cheyatledu fields ni .. where clause lo fix cheyali  because i am displaying this data in a oracle form... 

rank( date)

[Srimantudu]

SELECT RANK()OVER(ORDER BY Date DESC) as yourdatecolumn from yourtable into #temp

[chicchara]

if u want a detailed account of all accounts...and which are dups...which are not dups...then u can try the below algo

 

select count(*),* from TABLE

group by 62055-columnname)

having count >1

chestey..u can seperate out the accounts.. with dups.

then take those dups  and do max timestamp on those. you will get your 1 record. 

 UNION

select * from TABLE 

where account not in 

(

select count(*),* from TABLE

group by 62055-columnname)

having count >1

) 

deentlo all accounts having no dups vostaye.

In short we are doing this

 'dups with max'

union

'no dups'

 

[mettastar]

11 minutes ago, nenu_devudni said:

rank este anitiki oke rank ostundi thatha  ... nenu select cheyatledu fields ni .. where clause lo fix cheyali  because i am displaying this data in a oracle form... 

rank chesetapudu last date column meda descending cheyi.. okate rank enduku vasthadi .. partition by key and date and last timestamp paina descending order cheyi

[nenu_devudni]

9 minutes ago, rajivn786 said:

Select * from (
Select t.*,
       rownum () over (partition by t.c1, t.c2,t.c3, t.c4, t.c5 order by t.c8 desc) rn
  from t)
where rn = 1

 

Partition by lo (except the max date keep everythig if the values are same)  ...Ikkada c8 ante changing date

Rank

1    869997    54BKW    EL    1.1.1.8.0.255    62075    11/17/2016 1:48:00 AM    UIMSUSR    11/17/2016 7:37:06 AM
2    869997    54BKW    EL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    UIMSUSR    11/16/2016 11:27:19 AM
3    869997    54BKW    EL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    UIMSUSR    11/16/2016 10:05:17 AM
4    869997    54BKW    EL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    UIMSUSR    11/16/2016 7:37:01 AM
5    869997    54BKW    EL    1.1.1.8.0.255    61969    11/13/2016 1:48:00 AM    UIMSUSR    11/14/2016 7:36:57 AM
5    869997    54BKW    EL    1.1.1.8.0.255    62019    11/14/2016 1:48:00 AM    UIMSUSR    11/14/2016 7:36:57 AM
 

Nu chepinatu cheste ... ela ostundi vuncle .. result set .. i need to get rid of the duplicates .. and get just one record for 11/16th based on the max date ... below is the query m using .. okasari chusi chepp vuncle 

 

Select * from (
select RANK()OVER(ORDER BY uwtsmrr_activity_date DESC) as rankdt , a.*
from uwtsmrr a
where exists (select 'Y'
            from uibminv,
                 uwtobis,
                 utrcnfg
            where uibminv_code    = uwtsmrr_minv_code
            and uibminv_asvc_code = uwtsmrr_asvc_code
            and uwtobis_code      = uwtsmrr_obis_code
            and uwtobis_scat_code = utrcnfg_scat_code
            and utrcnfg_code      = uibminv_cnfg_code)
 and uwtsmrr_minv_code = '869997' and uwtsmrr_asvc_code = 'EL' 
 and trunc(uwtsmrr_read_time) >= trunc(sysdate) -4
 ) where rankdt = ? 

 

[nenu_devudni]

[k2s]

very nice post devudu_man... ltt for you

[nenu_devudni]

19 minutes ago, k2s said:

very nice post devudu_man... ltt for you

thnx vuncle .. teliste answer sepochu ga 

[ranku_mogudu]

58 minutes ago, nenu_devudni said:

Rank

1    869997    54BKW    EL    1.1.1.8.0.255    62075    11/17/2016 1:48:00 AM    UIMSUSR    11/17/2016 7:37:06 AM
2    869997    54BKW    EL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    UIMSUSR    11/16/2016 11:27:19 AM
3    869997    54BKW    EL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    UIMSUSR    11/16/2016 10:05:17 AM
4    869997    54BKW    EL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    UIMSUSR    11/16/2016 7:37:01 AM
5    869997    54BKW    EL    1.1.1.8.0.255    61969    11/13/2016 1:48:00 AM    UIMSUSR    11/14/2016 7:36:57 AM
5    869997    54BKW    EL    1.1.1.8.0.255    62019    11/14/2016 1:48:00 AM    UIMSUSR    11/14/2016 7:36:57 AM
 

Nu chepinatu cheste ... ela ostundi vuncle .. result set .. i need to get rid of the duplicates .. and get just one record for 11/16th based on the max date ... below is the query m using .. okasari chusi chepp vuncle 

 

Select * from (
select RANK()OVER(ORDER BY uwtsmrr_activity_date DESC) as rankdt , a.*
from uwtsmrr a
where exists (select 'Y'
            from uibminv,
                 uwtobis,
                 utrcnfg
            where uibminv_code    = uwtsmrr_minv_code
            and uibminv_asvc_code = uwtsmrr_asvc_code
            and uwtobis_code      = uwtsmrr_obis_code
            and uwtobis_scat_code = utrcnfg_scat_code
            and utrcnfg_code      = uibminv_cnfg_code)
 and uwtsmrr_minv_code = '869997' and uwtsmrr_asvc_code = 'EL' 
 and trunc(uwtsmrr_read_time) >= trunc(sysdate) -4
 ) where rankdt = ? 

row num try cheyyi

 

[Quickgun_murugan]

1 hour ago, chicchara said:

869997    54BLL     KL    1.1.1.8.0.255    62075    11/17/2016 1:48:00 AM      SUSR    11/17/2016 7:37:06 AM
869997    54BLL    KL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    SUSR    11/16/2016 7:37:01 AM
869997    54BLL    KL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    SUSR    11/16/2016 10:05:17 AM
869997    54BLL    KL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    SUSR    11/16/2016 11:27:19 AM
869997    54BLL     KL    1.1.1.8.0.255    62019    11/14/2016 1:48:00 AM     SUSR    11/14/2016 7:36:57 AM
869997    54BLL    KL    1.1.1.8.0.255    61969    11/13/2016 1:48:00 AM     SUSR    11/14/2016 7:36:57 AM

 

aa red vi poyi neeku result lo 11/13, 11/14, 11/16-1 record, 11/17 raavala?

and anni columns ravala? result set lo

Use ranking functions man.. it will be easy... partition by the 869997 and KL values... Andulo date max petti 10gu

[Quickgun_murugan]

1 hour ago, nenu_devudni said:

Rank

1    869997    54BKW    EL    1.1.1.8.0.255    62075    11/17/2016 1:48:00 AM    UIMSUSR    11/17/2016 7:37:06 AM
2    869997    54BKW    EL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    UIMSUSR    11/16/2016 11:27:19 AM
3    869997    54BKW    EL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    UIMSUSR    11/16/2016 10:05:17 AM
4    869997    54BKW    EL    1.1.1.8.0.255    62055    11/16/2016 1:48:00 AM    UIMSUSR    11/16/2016 7:37:01 AM
5    869997    54BKW    EL    1.1.1.8.0.255    61969    11/13/2016 1:48:00 AM    UIMSUSR    11/14/2016 7:36:57 AM
5    869997    54BKW    EL    1.1.1.8.0.255    62019    11/14/2016 1:48:00 AM    UIMSUSR    11/14/2016 7:36:57 AM
 

Nu chepinatu cheste ... ela ostundi vuncle .. result set .. i need to get rid of the duplicates .. and get just one record for 11/16th based on the max date ... below is the query m using .. okasari chusi chepp vuncle 

 

Select * from (
select RANK()OVER(ORDER BY uwtsmrr_activity_date DESC) as rankdt , a.*
from uwtsmrr a
where exists (select 'Y'
            from uibminv,
                 uwtobis,
                 utrcnfg
            where uibminv_code    = uwtsmrr_minv_code
            and uibminv_asvc_code = uwtsmrr_asvc_code
            and uwtobis_code      = uwtsmrr_obis_code
            and uwtobis_scat_code = utrcnfg_scat_code
            and utrcnfg_code      = uibminv_cnfg_code)
 and uwtsmrr_minv_code = '869997' and uwtsmrr_asvc_code = 'EL' 
 and trunc(uwtsmrr_read_time) >= trunc(sysdate) -4
 ) where rankdt = ? 

 

Partition by kuda use cheyy ochettadi.. seat ichettaru

[Quickgun_murugan]

@nene_devudni @nenu_devudni

[rajivn786]

1 hour ago, nenu_devudni said:

 

 

try this :

 

Select * from (
select Row_number ()OVER(partition by uwtsmrr_minv_code ORDER BY uwtsmrr_activity_date DESC) as rankdt , a.*
from uwtsmrr a
where exists (select 'Y'
            from uibminv,
                 uwtobis,
                 utrcnfg
            where uibminv_code    = uwtsmrr_minv_code
            and uibminv_asvc_code = uwtsmrr_asvc_code
            and uwtobis_code      = uwtsmrr_obis_code
            and uwtobis_scat_code = utrcnfg_scat_code
            and utrcnfg_code      = uibminv_cnfg_code)
 and uwtsmrr_minv_code = '869997' and uwtsmrr_asvc_code = 'EL' 
 and trunc(uwtsmrr_read_time) >= trunc(sysdate) -4
 ) where rankdt = 1 

[nenu_devudni]

6 minutes ago, Quickgun_murugan said:

Partition by kuda use cheyy ochettadi.. seat ichettaru

atla sepakapothe ..  etla seyalo sepa radu vuncle