Post reply

Repeate values

  • November 17, 2010 at 11:06 am

    #229341

    I have the following. If you execute this it will return the values from the temp table and the count.

    I will like to retrieve the values but not repeating values.

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    --===== Create the test table with

    CREATE TABLE #mytable

    (

    Codigo INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table

    Nombre CHAR(10),

    Apellido CHAR(10),

    Tipo INT,

    )

    --===== Setup any special required conditions especially where dates are concerned

    SET DATEFORMAT DMY

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT #mytable ON

    --===== Insert the test data into the test table

    INSERT INTO #mytable

    (Codigo, Nombre, Apellido, Tipo)

    SELECT '4','JUAN','PEREZ','1' UNION ALL

    SELECT '5','JUAN','PEREZ','2' UNION ALL

    SELECT '6','CARLOS','FLORES','1' UNION ALL

    SELECT '7','PEDRO','AGUILAR','2' UNION ALL

    SELECT '8','PEDRO','AGUILAR','1' UNION ALL

    SELECT '9','SANTIAGO','SANTOS','2'

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT #mytable OFF

    select * from #mytable

    select Count(*) from #mytable v group by v.Nombre

    I will like my results like this:

    '4' , JUAN','PEREZ','1'

    '6','CARLOS','FLORES','1'

    '8','PEDRO','AGUILAR','1'

    '9','SANTIAGO','SANTOS','2'

  • November 17, 2010 at 11:30 am

    #1252059

    look at aggregating the result set with min() and grouping on the name.

    You can also create a CTE and query against that for your results.

    The probability of survival is inversely proportional to the angle of arrival.

  • November 17, 2010 at 11:47 am

    #1252077

    This might work for you, although it will not present the results in the order you require

    select MIN(Codigo)AS 'Codigo' ,Nombre, Apellido,MIN(Tipo)AS 'Tipo' from #mytable GROUP BY Nombre, Apellido

    CodigoNombre Apellido Tipo

    7PEDRO AGUILAR 1

    6CARLOS FLORES 1

    4JUAN PEREZ 1

    9SANTIAGO SANTOS 2

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 17, 2010 at 12:40 pm

    #1252105

    Table 1:

    Codigo Nombre Apellido Tipo

    7 PEDRO AGUILAR 1

    8 PEDRO AGUILAR 2

    6 CARLOS FLORES 1

    4 JUAN PEREZ 1

    2 JUAN PEREZ 2

    9 SANTIAGO SANTOS 2

    10 PABLO MARMOL 3

    Table 2:

    Tipo Descripcion

    1 ABCD

    2 DBCE

    3 DEGF

    I want the list of persons. I use the Joins for making the results. But the are some persons with different Tipo, So I will like to make like Count(Nombre)=1, like in where clause.

  • November 17, 2010 at 1:34 pm

    #1252137

    Hi

    create table #mytable1

    (Tipo int, Descripcion varchar(20))

    Insert into #mytable1

    Select 1,'ABCD'

    union Select 2,'DBCE'

    union Select 3,'DEGF'

    select * from #mytable1

    select MIN(Codigo)AS 'Codigo' ,Nombre, Apellido,MIN(a.Tipo)AS 'Tipo',b.Descripcion from #mytable as a

    join #mytable1 as b on a.Tipo=b.Tipo

    GROUP BY Nombre, Apellido ,b.Descripcion

    having MIN(a.Tipo)=1

    Order by MIN(Codigo)

    Is this you are looking for

    Thanks

    Parthi

    Thanks
    Parthi

  • November 17, 2010 at 1:42 pm

    #1252143

    I just check my table, and I can't use min() because there is a value greater and I have others less.

    And I can use min on Tipo because is not integer, is a Char(1). I was thinking on something like count(Nombre)=1

  • November 17, 2010 at 3:26 pm

    #1252189

    Just to clear up some thing

    camiloaguilar1 (11/17/2010)

    I just check my table, and I can't use min() because there is a value greater and I have others less.

    And I can use min on Tipo because is not integer, is a Char(1). I was thinking on something like count(Nombre)=1

    When you say something like count(Nombre)=1 do you plan to filter or display?

    Are you trying to Join to other table for Tipo value?

    Scott
    I am a Senior Data Analyst/DBA
    I work with MS SQL, Oracle & several ETL Tools

    @thewolf66[/url]

  • November 17, 2010 at 3:49 pm

    #1252197

    Filter

  • November 18, 2010 at 5:04 am

    #1252408

    I suspect the second bit of code below is exactly what you want...

    --===== Without Codigo

    SELECT Nombre, Apellido, MinTipo = MIN(Tipo)

    FROM #MyTable

    GROUP BY Nombre, Apellido

    ;

    --===== With Codigo

    WITH

    cteFindMin AS

    (

    SELECT Nombre, Apellido, MinTipo = MIN(Tipo)

    FROM #MyTable

    GROUP BY Nombre, Apellido

    )

    SELECT t.*

    FROM #MyTable t

    INNER JOIN cteFindMin AS cte

    ON t.Nombre = cte.Nombre

    AND t.Apellido = cte.Apellido

    AND t.Tipo = cte.MinTipo

    ;

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply