Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (nike.apache.org: domain of hadoopraj@yahoo.com designates
 98.138.90.253 as permitted sender)
Message-ID: <1410374935.9863.YahooMailBasic@web120703.mail.ne1.yahoo.com>
Date: Wed, 10 Sep 2014 11:48:55 -0700
From: Raj Hadoop <hadoopraj@yahoo.com>
Subject: Re: Remove duplicate records in Hive
To: user@hive.apache.org
In-Reply-To: <1410374500.34068.YahooMailBasic@web120704.mail.ne1.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

The

SORT_ARRAY(COLLECT_SET(date))[0] AS latest_date

is returning the lowest date. I need the largest date.


--------------------------------------------
On Wed, 9/10/14, Raj Hadoop <hadoopraj@yahoo.com> wrote:

 Subject: Re: Remove duplicate records in Hive
 To: user@hive.apache.org
 Date: Wednesday, September 10, 2014, 2:41 PM
=20
 Thanks. I will try it.
 --------------------------------------------
 On Wed, 9/10/14, Nishant Kelkar <nishant.k02@gmail.com>
 wrote:
=20
  Subject: Re: Remove
 duplicate records in Hive
  To: user@hive.apache.org,
 hadoopraj@yahoo.com
  Date: Wednesday, September 10, 2014, 1:59
 PM
 =20
  Hi
=20
 Raj,=A0
  You can do something
  along these lines:=A0
 =20
  SELECT
  cno, sqno,
 SORT_ARRAY(COLLECT_SET(date))[0] AS latest_date
  FROM table GROUP BY cno, sqno;
  However, you have to make sure your
  date format is such that sorting it gives you
 the most
  recent date. The best way to do
 that is to have it in
  format:
 YYYY-MM-DD.
  Hope this helps.
  Best Regards,Nishant
=20
 Kelkar
  On Wed, Sep 10, 2014 at
  10:04 AM, Raj Hadoop <hadoopraj@yahoo.com>
  wrote:
 =20
 =20
  Hi,
 =20
 =20
 =20
  I have a requirement in Hive
 to remove duplicate records (
  they differ
 only by one column i.e a date column) and keep
  the latest date record.
 =20
 =20
 =20
  Sample
 :
 =20
  Hive Table :
 =20
  =A0d2 is a higher
 =20
  cno,sqno,date
 =20
 =20
 =20
  100 1 1-oct-2013
 =20
  101 2 1-oct-2013
 =20
  100 1 2-oct-2013
 =20
  102 2 2-oct-2013
 =20
 =20
 =20
 =20
 =20
  Output needed:
 =20
 =20
 =20
  100 1 2-oct-2013
 =20
  101 2 1-oct-2013
 =20
  102 2 2-oct-2013
 =20
 =20
 =20
  I am using
 Hive 0.11
 =20
 =20
 =20
  Any suggestions please ?
 =20
 =20
 =20
  Regards,
 =20
=20
 Raj
 =20
 =20