June 3, 2012 at 11:21 pm
Hi,
The output of my query contains duplicate records (i.e. column1 value is similar but other column's value different) as well as single record having column2 value blanks(-). now I want to select only one record out of duplicate records for which column2 values are present (eliminating records for which column2 is blanks(-)(Bold) from duplicate records) and all other single records having column2 values blanks.
My query's Present Output
[column1] [column2] [column3]
[abc][xyz][pqr]
[abc] [ - ] [klm]
[ijk] [ - ] [uvw]
[plo] [ - ] [ujn]
[yhk] [ttg] [wea]
[yhk] [ - ] [erf]
Expected Output
[column1] [column2] [column3]
[abc] [xyz] [pqr]
[ijk] [- ] [uvw]
[plo] [- ] [ujn]
[yhk] [ttg] [wea]
Can someone help me. Thanks in advance.
June 3, 2012 at 11:33 pm
The following query will give you the result that you are expecting:
Select Column1, Column2, Column3 From
(Select *, ROW_NUMBER() Over (Partition By Column1 Order By Column1) As rn From Ex) As a
Where a.rn = 1
If this doesn't do it then please read the link in my signature.
June 4, 2012 at 2:38 am
Thanks for helping me. But 'ROW_NUMBER() Over' function is unknown in SQL version I am using (Gives error message). What is alternative solution to this problem. Any simple technique using group by or join or where or any other.
Prakash P.
June 4, 2012 at 3:20 am
prakashp 84206 (6/4/2012)
Thanks for helping me. But 'ROW_NUMBER() Over' function is unknown in SQL version I am using (Gives error message). What is alternative solution to this problem. Any simple technique using group by or join or where or any other.Prakash P.
In that case this might help:
Select Column1, MAX(Column2), MAX(Column3) From Ex
Group By Column1
This will only work when Column2 and/or Column3 will have Empty columns.
What is the logic behind the Selection if the following rows exist?
Column1 Column2 Column3
abc xyz pqr
abc bbl klm
June 4, 2012 at 3:41 am
Hi Vinu Vijayan,
Max() function does not gives intended output. Because row selection from duplicate records is based on column2 value. If column2 value is present then select that row in output else eliminate (in case of duplicate rows only). Along with such rows, others(not duplicating rows) should also be the part of output. Please provide the solution.
Your efforts are appreciated. Thanks
Prakash P.
June 4, 2012 at 6:55 am
prakashp 84206 (6/4/2012)
Thanks for helping me. But 'ROW_NUMBER() Over' function is unknown in SQL version I am using (Gives error message). What is alternative solution to this problem. Any simple technique using group by or join or where or any other.Prakash P.
Then why are you posting in the SQL server 2008 forum? And even more important, why don't you tell us for which version you DO need it to work? Even though the people on here can do a lot, I've not yet heard of one person that can read your mind over the internet.
June 4, 2012 at 10:10 pm
Dear R.P.Rozema,
I understand your concern, don't get angry. Sorry for troubling you. But frankly speaking I can't find the forum for SQL J. I am using SQL J for my query. Also I was not much aware about SQL J. First time I am using it.
Once again Sorry. Pls help.
June 4, 2012 at 10:12 pm
This one doesn't use any functions that might not be in older versions. Check if this works:
--Creating Table
Create Table Ex
(Column1 varchar(3),
Column2 varchar(3),
Column3 varchar(3) )
--Insertint Sample Data
Insert into Ex
Select 'abc', 'xyz', 'pqr'
Union ALL
Select 'abc', '-', 'klm'
Union ALL
Select 'ijk', '-', 'uvw'
Union ALL
Select 'plo', '-', 'ujn'
Union ALL
Select 'yhk', 'ttg', 'wea'
Union ALL
Select 'yhk', '-', 'erf'
--Query For Your Requirement
Select * From Ex
Where Column1 Not IN (Select Column1 From Ex Group By Column1 Having COUNT(Column1) > 1)
Union
Select * From Ex Where Column1 IN (Select Column1 From Ex Group By Column1 Having COUNT(Column1) > 1)
And Column2 <> '-'
June 5, 2012 at 12:59 am
prakashp 84206 (6/4/2012)
Dear R.P.Rozema,I understand your concern, don't get angry. Sorry for troubling you. But frankly speaking I can't find the forum for SQL J. I am using SQL J for my query. Also I was not much aware about SQL J. First time I am using it.
Once again Sorry. Pls help.
I'm not angry, I'm just trying to show you it's impossible for us to help you if you don't provide enough information. You've now given us the name of the SQL dialect you're using, but that still doesn't help much: I've done a quick google for SQL J, but did not find much information on possible syntax. Please provide us with a link to the product development documentation. Or if you don't have this, do you at least have a vendor name, version and such for the product? Given such information we may be able to help you better. i.e. the more information you provide to us, the more likely it is you'll get a usable answer. Plus, it also shows you've put in some effort yourself, which usually makes the people here willing to spend some more time for you.
June 5, 2012 at 1:06 am
I found following site only. Please check. But I couldn't found anything useful here.
June 5, 2012 at 1:19 am
Thanks Vinu,
You have given perfect solution. But I have observed that some records are missing from output (this is my fault for incomplete sample data). After research it is found my actual sample data is like this.
[column1] [column2] [column3]
[abc][xyz][pqr]
[abc] [ - ] [klm]
[ijk] [ - ] [uvw]
[ijk] [ - ] [yyh] => missing from o/p. Should be the part of o/p.
[plo] [ - ] [ujn]
[yhk] [ttg] [wea]
[yhk] [ - ] [erf]
In highlight record, count is greater than 1 but coulnm2 is blank.
So now Expected Output
[column1] [column2] [column3]
[abc] [xyz] [pqr]
[ijk] [- ] [uvw]
[ijk] [ - ] [yyh]
[plo] [- ] [ujn]
[yhk] [ttg] [wea]
Thanks once again and request your great expertise.
I am using such forum first time in my life as well as new to SQL programming, so not able to think in 360 direction.
June 5, 2012 at 3:22 am
How about this?
Select * From Ex Where Column1 IN (Select Column1 From Ex Group By Column1 Having COUNT(Column1) > 1)
And Column2 <> '-'
Union
Select * From Ex Where Column1 NOT IN(Select Column1 From Ex Where Column1 IN (Select Column1 From Ex Group By Column1 Having COUNT(Column1) > 1) And Column2 <> '-')
June 5, 2012 at 4:50 am
Hi Vinu,
I think solution you have given is perfect. but I am not able to use it with my query. Pls see below is my query.
===========
select m.docno, m.adref, m.perf_date from
(
select d.docno,
(select x.docno from doc_header x where x.docno_i =
case when exists (select ref_docno_i from doc_replacement t
where t.docno_i = s.docno_i and replacement_code='SB') then
(select ref_docno_i from doc_replacement t where t.docno_i =
s.docno_i and replacement_code='SB') else s.docno_i end and
x.doc_type in ('AD', 'AAD', 'APPL', 'EAD')) adref, perf_date
from doc_header d, doc_history h, doc_signoff_tree s
where d.docno_i = h.docno_i
and h.docno_i = s.ref_docno_i
and h.doc_status in ('C','R')
and h.perf_date between 14411 and 14755 ) m
Group By m.docno Having COUNT(m.docno) > 1
And m.adref <> ' '
union
select m.docno, m.adref, m.perf_date from
(
select d.docno,
(select x.docno from doc_header x where x.docno_i =
case when exists (select ref_docno_i from doc_replacement t
where t.docno_i = s.docno_i and replacement_code='SB') then
(select ref_docno_i from doc_replacement t where t.docno_i =
s.docno_i and replacement_code='SB') else s.docno_i end and
x.doc_type in ('AD', 'AAD', 'APPL', 'EAD')) adref, perf_date
from doc_header d, doc_history h, doc_signoff_tree s
where d.docno_i = h.docno_i
and h.docno_i = s.ref_docno_i
and h.doc_status in ('C','R')
and h.perf_date between 14411 and 14755 ) m
Group By m.docno Having ??????
order by m.perf_date
===========
Here column1 - m.docno, column2 - m.adref, column3 - m.perf_date
How to use the provided solution to this query.
June 5, 2012 at 5:01 am
That's not SQL J, that's Sybase Adaptive Server. As I read it, SQL J is just a method to include Java code in their database, much similar to SQL server's CLR routines.
June 5, 2012 at 5:20 am
Well, I can't see from here what data you have in your tables to know what your query is doing.
For me to give an opinion there needs to be sample data that matches your query.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply