-
duplicated records...I want just one row...
Hi all...
I got this small problem...
which it's taking time..
I have this tables:
ESC_SUPP
(PK = SUPP_CODE)
SUPP_CODE GSL_CODE
S1 110
S2 110
S4 111
S5 111
ESC_SUP_PART
(PK = SUPP_CODE, PART)
SUPP_CODE PART
S1 A1
S2 A1
S4 A1
S5 A1
Then, when I submit this query:
select b.part, a.gsl_code , count(1)
from esc_supp a,
esc_sup_part b
where a.supp_code = b.supp_code
and b.part = 'A1'
group by a.gsl_code, b.part
result:
PART GSL_CODE COUNT(1)
A1 110 2
A1 111 2
Goal: I need to get Only One row, any
of the above, since I will put the query inside a PL/SQL code.
How can do this without using
subqueries?
I hope you can help me...
Thanks in adcance!
JC.
-
If ANY of the returned records is
satisfactory to you, just constrain
the results using a TOP 1:
select TOP 1 ...
Gary
-
Hey,
The question is
1) do really care about the count?
2)do you really care about gsl_code?
if NOT then try the following.
SELECT b.PART, Count(1) AS Expr1
FROM esc_supp AS a INNER JOIN esc_sup_part AS b ON a.supp_code = b.supp_code
WHERE ((a.supp_code)=[b].[supp_code])
GROUP BY b.PART
If you already found a way please let me know. thanks.
-Kannan Rangan.
-
try this.
Just add distinct to your select statement. That will eliminate exact duplicates. (select distinct field1, field2 from tableA)
BUT -- you are NOT getting duplicate records here. You asked for ALL the GSL_codes for part A1. and that is what you got.
If what you want to know is "How many GSL_codes are there for part A1", then ask this:
select b.part, count(a.gsl_code)
from esc_supp a,
esc_sup_part b
where a.supp_code = b.supp_code
and b.part = 'A1'
group by b.part
Last edited by skarasik; 04-30-2003 at 09:34 AM.
-
I am not sure what you are trying to accomplish, but I can see from the queries that you will never get just one row. Here's why:
Code:
1 SELECT b.part, a.gsl_code , COUNT(1)
2 FROM esc_supp a, esc_sup_part b
3 WHERE a.supp_code = b.supp_code AND b.part = 'A1'
4 GROUP BY a.gsl_code, b.part
Line 1 : The COUNT(1) will simply return the count for field 1 for each row returned, so we can take this out. This of course depends on whether you actually want to use this count.
Line 2 : This line is OK
Line 3 : I am not to sure what you are trying to accomplish, which makes it difficult to say what this line should look like.
Line 4 : I think this is the actual part that may confuse you - I know I strugled a long time with this before I grasped it... The results are grouped by the destincted values of GSL_CODE, of which there are only 2 namely 110 and 111. Now, one would think that the second supplied field would limit the returned line to one, but it doesn't - and this has to do with how various servers optimise the GROUP BY clause. In this instance, if you think about it, it's confusing - because even I ( as a human ) can figure out how you want your results returned.
As you can see by the other comments - we are all fishing in the dark trying to figure out what you are trying to accomplish.
Hope to hear from you again so that we can help.
Cheer.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|