sql babulu ravali - urgent helf

[nenu_devudni]

2 minutes ago, rajivn786 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 

atla cheste oka record ostadi .. em cheyali dantoh 

[rajivn786]

 

didn;t see your o/p ,

instead of partition by uwtsmrr_minv_code

  use  this partition by uwtsmrr_minv_code,  column name with this value (11/16/2016 1:48:00 AM)

[rajivn786]

In the future, post your create/insert statements for the  dummy data, so that we can create and test on our side..rather than just posting the output.

[nenu_devudni]

30 minutes ago, rajivn786 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 

 

17 minutes ago, rajivn786 said:

In the future, post your create/insert statements for the  dummy data, so that we can create and test on our side..rather than just posting the output.

bascially this is the query i am using in my form .. and i need to avoid those duplicates .. so i have to fix the sql in the where clause .. the script below that m using is incorrect as it will directly fetch one record with the max date ... 

select *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
 and a.uwtsmrr_activity_date = (select max(b.uwtsmrr_activity_date) from uwtsmrr b 
                                        where   b.uwtsmrr_minv_code = a.uwtsmrr_minv_code
                                           and b.uwtsmrr_meter_model = a.uwtsmrr_meter_model
                                           and b.uwtsmrr_asvc_code = a.uwtsmrr_asvc_code
                                           and b.uwtsmrr_obis_code = a.uwtsmrr_obis_code) 
order by uwtsmrr_read_time desc

 

 

 

[rajivn786]

You asked to get rid of the duplicates from the below...The  query will give you only 1 date for 11/16. 

So what is the output you need from the below :

If you need row 1,2,5,6 as output, use partition by column 1,2,3,4,5,6,7  order by last column date desc.

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

[k2s]

1 hour ago, nenu_devudni said:

thnx vuncle .. teliste answer sepochu ga 

nenu inka SQL data side kaalu elu pettaledu devudu_man... aa field ki inka kilometer dooram unna.... 

[CheGuevara]

Select * from (select  a,b,c,d,e,f,g,h,i,max(i) over(partition by a order by i desc) as max_date,rank() over(partition by a order by i desc) as rank from table)

where rank =1;

 

use this code and replace the a,b values with your column names as you showed data above

[CheGuevara]

Select b.* from (select  a,b,c,d,e,f,g,h,i,first_value(i) over(partition by a order by i desc) as max_date) as rank from x) y, x

where y.a = x.a

and y.b = x.b

and y.c = x.c

and y.d = x.d

and y.e = x.e

and y.f = x.f

and y.g = x.g

and y.h = x.h

and y.i = x.i

and y.max_date=X.i;

 

replace a,b with column names as you posted above

[4Vikram]

4 hours ago, nenu_devudni said:

vikki thatha etla unay nuvu ... na num ichina kada vay .... pm sesssa .. susko ..  em sesinav thatha nek pm potle ..

pm disable aindi ani jamana la chepina... kontekurradu vuncle ki pm petu will take from him 

3 hours ago, nenu_devudni said:

4 hours ago, 4Vikram said:

u got my point vuncle .. exactly alage result set undali 

ee comment ela ochindi nenu asalu post ee seyaledu?

[mtkr]

CREATE TABLE #temp
(
id INT,
bill VARCHAR(20),
city VARCHAR(20),
series VARCHAR(20),
zip VARCHAR(20),
create_dt DATETIME,
user_ct VARCHAR(20),
update_dt DATETIME
)


SELECT * FROM #temp



INSERT INTO #temp VALUES  ('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')
INSERT INTO #temp VALUES ('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')
INSERT INTO #temp VALUES  ('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')
INSERT INTO #temp VALUES  ('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')
INSERT INTO #temp VALUES  ('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')
INSERT INTO #temp VALUES  ('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')



SELECT * FROM #temp

id	     bill	city	series	     zip	create_dt	          user_ct	      update_dt
869997	54BLL	KL	1.1.1.8.0.255	62075	2016-11-17 01:48:00.000	SUSR	2016-11-17 07:37:06.000
869997	54BLL	KL	1.1.1.8.0.255	62055	2016-11-16 01:48:00.000	SUSR	2016-11-16 07:37:01.000
869997	54BLL	KL	1.1.1.8.0.255	62055	2016-11-16 01:48:00.000	SUSR	2016-11-16 10:05:17.000
869997	54BLL	KL	1.1.1.8.0.255	62055	2016-11-16 01:48:00.000	SUSR	2016-11-16 11:27:19.000
869997	54BLL	KL	1.1.1.8.0.255	62019	2016-11-14 01:48:00.000	SUSR	2016-11-14 07:36:57.000
869997	54BLL	KL	1.1.1.8.0.255	61969	2016-11-13 01:48:00.000	SUSR	2016-11-14 07:36:57.000


SELECT  a.*
FROM    #temp a
        INNER JOIN
        (
            SELECT  zip, MAX(update_dt) maxdt
            FROM    #temp
            GROUP BY zip
        ) b ON  a.zip = b.zip and
                a.update_dt = b.maxdt


Result:

id	    bill	city	series	     zip	   create_dt	           user_ct	   update_dt
869997	54BLL	KL	1.1.1.8.0.255	62075	2016-11-17 01:48:00.000	SUSR	2016-11-17 07:37:06.000
869997	54BLL	KL	1.1.1.8.0.255	62055	2016-11-16 01:48:00.000	SUSR	2016-11-16 11:27:19.000
869997	54BLL	KL	1.1.1.8.0.255	62019	2016-11-14 01:48:00.000	SUSR	2016-11-14 07:36:57.000
869997	54BLL	KL	1.1.1.8.0.255	61969	2016-11-13 01:48:00.000	SUSR	2016-11-14 07:36:57.000

assuming zip is distinct for each set of records...

@nenu_devudni

@k2s

[mtkr]

SELECT  id, bill, city, series, zip, create_dt, user_ct, update_dt
FROM    
    ( SELECT  id, bill, city, series, zip, create_dt, user_ct, update_dt,
                ROW_NUMBER() OVER (Partition By zip   ORDER BY update_dt DESC) row_num
        FROM    #temp
    ) a
WHERE   a.row_num = 1

 

 

with row number

 

@nenu_devudni

@k2s

[k2s]

47 minutes ago, mtkr said:

SELECT  id, bill, city, series, zip, create_dt, user_ct, update_dt
FROM    
    ( SELECT  id, bill, city, series, zip, create_dt, user_ct, update_dt,
                ROW_NUMBER() OVER (Partition By zip   ORDER BY update_dt DESC) row_num
        FROM    #temp
    ) a
WHERE   a.row_num = 1

 

 

with row number

 

@nenu_devudni

@k2s

But y me

[Doola]

trunc(date) vadaku man nee query lo,  hh:mm:ss pothadi kada comparison lo 

[4Vikram]

8 minutes ago, Doola said:

trunc(date) vadaku man nee query lo,  hh:mm:ss pothadi kada comparison lo 

Doola man you sql thopu aa 

[Doola]

3 minutes ago, 4Vikram said:

Doola man you sql thopu aa 

ledu man....edo naaku telisindi cheppa... adi fix kaado TS issue ki mari