Jump to content

sql babulu ravali - urgent helf

nenu_devudni

By nenu_devudni
in Discussions

 Share

Recommended Posts

nenu_devudni Hero

nenu_devudni

Posted
  • Author
Posted
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 _%~

  • 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

rajivn786 Side Hero

rajivn786

Posted
Posted

 

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

rajivn786

Posted
Posted

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 Hero

nenu_devudni

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

rajivn786

Posted
Posted

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 PRODUCER

k2s

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

CheGuevara

Posted
Posted

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 Sr. Hero

CheGuevara

Posted
Posted

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 LEGEND

4Vikram

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

mtkr

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

mtkr

Posted
Posted

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 PRODUCER

k2s

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

4Vikram LEGEND

4Vikram

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

Doola

Posted
Posted
3 minutes ago, 4Vikram said:

Doola man you sql thopu aa 

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

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