Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Post Unanswered: Removing Duplicates with condition - Help

    I have following data

    ACNO,Date,Nodename
    1,2001-06-15,A1
    2,2002-02-07,A2
    3,2001-01-22,A3
    1,2003-06-22,A1,
    4,2007-12-22,A4
    1,2008-01-23,A1
    5,2000-11-23,A1

    What result i am looking for is

    ACNO,Date,Nodename
    5,2000-11-23,A1
    4,2007-12-22,A4
    3,2001-01-22,A3
    2,2002-02-07,A2
    1,2008-01-23,A1(latest row on NodeName)

    Please help on T-SQL.

    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    T-SQL is used with SQL Server, not PostgreSQL. Are you in the right forum?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    'date' is a horrible name for a database field, as DATE is a reserved word in every database I've ever used, and in every programming language I've ever used. The name should be more descriptive. TestDate, or OrderDate, for instance.

    that being said, this should work:
    Code:
    select t.acno, t.thedate, t.nodename from testtable t
    
     join (select t2.acno, max(t2.thedate) as thedate from testtable t2 group by t2.acno) t3 
    	on (t.acno=t3.acno and t.thedate=t3.thedate)
    
    order by t.acno desc, t.thedate desc
    (you'll obviously have to change the table name to match yours.)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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