# Miscellaneous > Structured Query Language (SQL) >  duplicated records...I want just one row...

## juanc2k

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.

----------


## GaryG

If ANY of the returned records is
satisfactory to you, just constrain
the results using a TOP 1:

select TOP 1 ...

Gary

----------


## Kannan Rangan

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.

----------


## skarasik

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

----------


## nicc777

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:



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

----------

