Home » SQL & PL/SQL » SQL & PL/SQL » Sql query
Sql query [message #251693] Mon, 16 July 2007 02:00 Go to next message
shivakumar_k
Messages: 5
Registered: July 2007
Junior Member
Query to select 'a' Field with more than 'b' Field with different values.

Field     Field
a         b
------------------
a         1
a         2
a         3
b         1
b         1
b         7
c         5
c         6
e         4


output should be

Field Field
a         b
--------------
a         1
a         2
a         3
b         7
c         5
c         6

Can you help me to find the query...
Re: Sql query [message #251697 is a reply to message #251693] Mon, 16 July 2007 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you already try?
What about c and e?
Why there is no e in the ouput?

Have a look at COUNT function in its analytic version.

Regards
Michel
Re: Sql query [message #251700] Mon, 16 July 2007 02:34 Go to previous messageGo to next message
shivakumar_k
Messages: 5
Registered: July 2007
Junior Member
I tried the query
select a, b from table
having count(*)
group by a, b;

but this query returns

[CODE]a b
--------------
a 1
a 2
a 3
b 7
c 5
c 6
e 4
Re: Sql query [message #251705 is a reply to message #251700] Mon, 16 July 2007 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong.
This query is not valid.
So you didn't execute it.

Copy and paste what you REALLY did.
Tell us ALL the solutions you thought about to solve this issue.

Regards
Michel
Re: Sql query [message #251707 is a reply to message #251693] Mon, 16 July 2007 02:50 Go to previous messageGo to next message
sdecman
Messages: 20
Registered: July 2005
Location: Ljubljana, Slovenia
Junior Member
What are you trying to achieve? Does e needs to be in the output or not? However, it seems to me that the middle half of your last post is missing.

Assuming that e needs to be there, you could try this:

SELECT a,b
  FROM (SELECT a, b, 
               row_number() OVER (PARTITION BY b ORDER BY b ASC, a ASC) as rn
          FROM t1)
 WHERE rn = 1 
 ORDER BY a ASC

We could do better, you know. That is, if you would use MORE words to describe your problem.

Cheers
Re: Sql query [message #251710 is a reply to message #251693] Mon, 16 July 2007 03:00 Go to previous messageGo to next message
shivakumar_k
Messages: 5
Registered: July 2007
Junior Member
how to select field_a that has more than one field_b with different values?
table has data like..

Field     Field
a         b
------------------
a         1
a         2
a         3
b         6
c         9


query should return the record like

Field     Field
a         b
------------------
a         1
a         2
a         3
Re: Sql query [message #251711 is a reply to message #251710] Mon, 16 July 2007 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain with WORDS what are the rules.

Regards
Michel
Re: Sql query [message #251727] Mon, 16 July 2007 05:24 Go to previous messageGo to next message
shivakumar_k
Messages: 5
Registered: July 2007
Junior Member
*) To select only the field_a that has more than one field_b
with different values,

for example
field_a    field_b
------------------
a            1
a            2
b            1
b            2
b            3



*) But should not select the field_a has more than one field_b
with same value.

for example 
field_a    field_b
------------------
h           1
h           1
s           2
s           2


*) Also query should not select the field_a has any one record with field_b.

for example
field_a    field_b
------------------

h            5
g            7
u            9


sample record
field_a    field_b
------------------ 
a            1
a            2
b            1
b            2
b            3
h           1
h           1
s           2
s           2
h            5
g            7
u            9

output must be 
field_a    field_b
------------------ 
a            1
a            2
b            1
b            2
b            3
Re: Sql query [message #251798 is a reply to message #251727] Mon, 16 July 2007 12:34 Go to previous messageGo to next message
sdecman
Messages: 20
Registered: July 2005
Location: Ljubljana, Slovenia
Junior Member
It looks like a very simple thing to do, but it's not. There are two key rules that are difficult:
1.) find the duplicates and mark them
2.) use the duplicates, marked for removal

I find both rules together in the same query pretty hard to implement. Assuming the table name holding the data is named t1 and the table is populated with the data in your first post,try this (until someone posts a better solution):

SELECT a, b
  FROM (SELECT a, b, c,
               row_number() OVER (PARTITION BY a ORDER BY a ASC, b ASC) AS rn,
               lead(b) OVER (PARTITION BY a ORDER BY a ASC, b ASC) AS lead
          FROM (SELECT t1.a, t1.b, t2.c
                  FROM t1, 
                       (SELECT a, b, 1 AS c
                          FROM (SELECT a,b,
                                       row_number() OVER (PARTITION BY a, b ORDER BY a) AS rn
                                  FROM t1)
                                 WHERE rn > 1) t2   
                 WHERE t1.a = t2.a(+)
                   AND t1.b = t2.b(+)
                )
       ) 
 WHERE (rn <> 1 OR lead IS NOT NULL)
   AND c IS NULL


Cheers
Re: Sql query [message #251996 is a reply to message #251798] Tue, 17 July 2007 11:13 Go to previous message
shivakumar_k
Messages: 5
Registered: July 2007
Junior Member
Hi sdecman,
Hope this will helpful for me.
Thank you very much..
Previous Topic: Finding second highest salary
Next Topic: Changing column data
Goto Forum:
  


Current Time: Fri Dec 09 19:30:39 CST 2016

Total time taken to generate the page: 0.10149 seconds