| Sql query [message #251693] |
Mon, 16 July 2007 02:00  |
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 #251700] |
Mon, 16 July 2007 02:34  |
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 #251707 is a reply to message #251693] |
Mon, 16 July 2007 02:50 |
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 |
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 #251727] |
Mon, 16 July 2007 05:24 |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
