Results 1 to 5 of 5

Thread: duplicated records...I want just one row...

  1. #1
    Join Date
    Mar 2003
    Posts
    1

    Exclamation 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.

  2. #2
    Join Date
    Jan 2003
    Location
    FL
    Posts
    13
    If ANY of the returned records is
    satisfactory to you, just constrain
    the results using a TOP 1:

    select TOP 1 ...

    Gary

  3. #3
    Join Date
    Apr 2003
    Location
    NYC
    Posts
    1
    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.

  4. #4
    Join Date
    Jan 2003
    Posts
    8

    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.

  5. #5
    Join Date
    Feb 2003
    Location
    Johannesburg, South Africa
    Posts
    145
    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
  •