Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: SELECT Query syntax to display only the top record for duplicate records

    Good day!

    I just can't figure out how I can display only the top record for the duplicate records in my table.

    Example:

    Table1
    Code Date
    01 10/1/05
    01 10/2/05
    01 10/3/05
    02 9/9/05
    02 9/9/05
    02 9/10/05

    My desired result would be:
    Table1
    Code Date
    01 10/1/05
    02 9/9/05

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In this particular case, you don't seem to be looking for the "top" record (a concept undefined in an unordered dataset anyway...) but rather then minimum value for each Code. Look up aggregate queries, and specifically the MIN() function in Books Online.
    Code:
    select	Code,
    	min(Date) as Date
    from	[YourTable]
    group by Code
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    19

    SELECT Duplicate Top * FROM TABLE

    As Salam Alakum !

    Suppose that ur table name is 'CodeDate'.

    Having the fields Code, Date.
    Then this query will work for u.


    SELECT Counter,Date from
    (
    SELECT Count(*) AS Counter,a.date FROM CodeDate a INNER Join CodeDate b
    On a.Date = b.Date Group by a.Date
    )
    AS TEST WHERE Counter > 1

  4. #4
    Join Date
    Oct 2005
    Posts
    119
    The select syntax here that I tried doesn't seem to work.

    I only want to display the first record for each code. My order of preference is the code. My primary key for my display query is the code. I don't mind the value of the other fields in the table. I just want to get one record for each code.

    Thanks for taking the time to answer my question.
    God bless.

  5. #5
    Join Date
    Mar 2004
    Posts
    19

    Cool This works

    Hi !


    Please check the table name which u r giving.
    i have checked it once again, and it is flying.

    Best Regards,
    Shabber.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    shabber, unfortunately your query does not work

    try it yourself:
    Code:
    create table Table1
    ( Code char(2) 
    , Date datetime
    )
    insert into Table1 values ('01','10/1/05')
    insert into Table1 values ('01','10/2/05')
    insert into Table1 values ('01','10/3/05')
    insert into Table1 values ('02','9/9/05')
    insert into Table1 values ('02','9/9/05')
    insert into Table1 values ('02','9/10/05')
    
    SELECT Counter,Date from 
    (
    SELECT Count(*) AS Counter,a.date FROM Table1 a INNER Join Table1 b 
    On a.Date = b.Date Group by a.Date
    ) 
    AS TEST WHERE Counter > 1
    your query produces this result:
    Code:
    Counter Date
       4   2005-09-09 00:00:00.000
    here are the results of blindman's query:
    Code:
    Code Date
     01  2005-10-01 00:00:00.000
     02  2005-09-09 00:00:00.000
    LimaCharlie, if you have any other columns in the table that you wish to return, i.e. if you want complete rows rather than just the lowest date value per code, then you need a different query:
    Code:
    drop table Table1
    
    create table Table1
    ( Code char(2) 
    , Date datetime
    , Stooge varchar(9)
    )
    insert into Table1 values ('01','10/1/05','curly')
    insert into Table1 values ('01','10/2/05','larry')
    insert into Table1 values ('01','10/3/05','moe')
    insert into Table1 values ('02','9/9/05', 'shemp')
    insert into Table1 values ('02','9/9/05', 'joe')
    insert into Table1 values ('02','9/10/05','curly joe')
    
    select Code  
         , Date 
         , Stooge
      from Table1 as ZZ
     where Date
         = ( select min(Date)
               from Table1
              where Code = ZZ.Code )
    this query returns the following:
    Code:
    Code Date                    Stooge
     01  2005-10-01 00:00:00.000 curly
     02  2005-09-09 00:00:00.000 shemp
     02  2005-09-09 00:00:00.000 joe
    notice anything? there are two rows that have the lowest date for code 02, and this is as it should be, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2005
    Posts
    119
    Good Day!

    Hi r937.
    My desired result would be:
    Code Date Stooge
    01 2005-10-01 curly
    02 2005-09-09 joe (or shemp)

    1 record only for Code 02. I'll just use ORDER BY to get Stooge joe or Stooge shemp specifically.

    Blindman query is ok only if i wanted to display the 2 fields only. But i have other fields to display.

    To get my desired result, I just used LOOP within my Visual Basic code. My primary key in the table is the Code. So i have a condition that queries the existing code in the table, so it will not display the same code more than once. But this is a little slow.

    Now, I have another idea. Is it possible to get this result?
    What would be the select query for this? Note that Ctr is not a field in the table.

    Ctr Code Date Stooge
    1 01 2005-10-01 curly
    1 02 2005-09-09 joe
    2 02 2005-09-09 shemp

    I'll just put it in a view. So when I query...
    my query would be
    "SELECT * FROM view WHERE Ctr=1"

    Thanks for taking the time to answer my questions.
    God bless you all

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    set nocount on
    
    declare @YourTable table
    	(Code char(2),
    	Date datetime,
    	Stooge varchar(50))
    
    insert into @YourTable values ('01','10/1/05', 'Curly')
    insert into @YourTable values ('01','10/2/05', 'Moe')
    insert into @YourTable values ('01','10/3/05', 'Larry')
    insert into @YourTable values ('02','9/9/05', 'Joe')
    insert into @YourTable values ('02','9/9/05', 'Shemp')
    insert into @YourTable values ('02','9/10/05', 'Larry')
    
    
    select	Count(*) as Ctr,
    	YourTable1.Code,
    	YourTable1.Date,
    	YourTable1.Stooge
    from	@YourTable YourTable1
    	inner join @YourTable YourTable2
    		on YourTable1.Code = YourTable2.Code
    		and YourTable1.Date = YourTable2.Date
    		and YourTable1.Stooge >= YourTable2.Stooge
    group by YourTable1.Code,
    	YourTable1.Date,
    	YourTable1.Stooge
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2011
    Posts
    1

    Query the top of the duplicate entries

    SELECT BankName, MAX(amount) FROM
    (SELECT BankName, amount, interest
    FROM BankRecord
    WHERE amount<= 5000 AND interest> 0)
    AS Record GROUP BY BankName

    - sample db record
    {BankName} {amount} {interest}
    A 1000 13%
    A 5000 16%
    A 3000 14%
    B 1000 11%
    B 2000 12%
    - result after query
    A 5000 16%
    B 2000 12%

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dear archerv, that's moderately helpful, but it's six years late!!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd say it is, at best, moderately misleading and six years late.
    His query is needlessly complex, and could simply be written as
    Code:
    SELECT	BankName,
    	MAX(amount)
    FROM	BankRecord
    WHERE	amount<= 5000 AND interest> 0
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    ... and could simply be written as ...
    i would agree with you, except for your missing GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...oops. Score one for Rudy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •