Jump to content

sql babulu ravali - urgent helf

nenu_devudni

By nenu_devudni
in Discussions

 Share

Recommended Posts

rajivn786 Side Hero

rajivn786

Posted
Posted

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

  • Replies 56
  • Created
  • Last Reply

Top Posters In This Topic

  • nenu_devudni

    13

  • 4Vikram

    8

  • rajivn786

    5

  • Srimantudu

    5

Popular Days

Top Posters In This Topic

Popular Days

Srimantudu Celebrity

Srimantudu

Posted
Posted
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 Celebrity

Srimantudu

Posted
Posted

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

chicchara Hero

chicchara

Posted
Posted

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 LEGEND

mettastar

Posted
Posted
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 Hero

nenu_devudni

Posted
  • Author
Posted
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 Hero

nenu_devudni

Posted
  • Author
Posted
19 minutes ago, k2s said:

very nice post devudu_man... ltt for you

thnx vuncle .. teliste answer sepochu ga $%^

ranku_mogudu Celebrity

ranku_mogudu

Posted
Posted
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 LEGEND

Quickgun_murugan

Posted
Posted
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 LEGEND

Quickgun_murugan

Posted
Posted
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

rajivn786 Side Hero

rajivn786

Posted
Posted
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 Hero

nenu_devudni

Posted
  • Author
Posted
6 minutes ago, Quickgun_murugan said:

Partition by kuda use cheyy ochettadi.. seat ichettaru

atla sepakapothe ..  etla seyalo sepa radu vuncle 

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...