Need help with sql

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

Need help with sql

sql.JPG


Accepted Solutions
Junior Contributor

Re: Need help with sql

No, it doesn't, it returns the row with data correctly.

There's no "last record" based on inserts, empty string/spaces sort before characters and NULLs sort before anything else, thus DESC returns rows with data in address1 on top.

1 ACCEPTED SOLUTION
5 REPLIES
Enthusiast

Re: Need help with sql

Hi,

 

Please try below code, it should suffice your need:

create multiset table SCD_T (
  id char(10) not null,
  des char(10) not null,
  add1 char(10) ,
  add2 char(10)   
) primary index (id)
;
 insert into scd_t values ( 1, 'Main', null ,null );
insert into scd_t values ( 1, 'Main', 'Janpath' ,'main street' );
insert into scd_t values ( 1, 'Main', '' ,'' );

use any one of these:
sel id,des,
case 
	when add1 is null  or  add1 = '' then ''
	else add1
end as myadd1
,
case 
	when add2 is null  or  add2 = '' then ''
	else add2
end as myadd2
from scd_t
where (myadd1 <> '' and myadd2 <> '');

sel id,des,coalesce(add1,'') as myadd1 ,coalesce(add2,'')  as myadd2
from scd_t
where (myadd1 <> '' and myadd2 <> '')

--Samir Singh

Enthusiast

Re: Need help with sql

Hi,

as per the requirement i need to get the record with ID 3 , by using the above two queries the last record is getting

skipped

 

Junior Contributor

Re: Need help with sql

Is this a kind of best match per Id?

 

select * from yourTable
qualify 
   row_number() 
   over (partition by Id 
         order by Address1 desc) = 1

 

Enthusiast

Re: Need help with sql

order by address1 desc will gives me last record.. but as per the sanp provided above, for id 1 i will get ''(blank data) values instead of actual data

Junior Contributor

Re: Need help with sql

No, it doesn't, it returns the row with data correctly.

There's no "last record" based on inserts, empty string/spaces sort before characters and NULLs sort before anything else, thus DESC returns rows with data in address1 on top.