Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 91D53C472 for ; Thu, 12 Apr 2012 20:44:28 +0000 (UTC) Received: (qmail 59405 invoked by uid 500); 12 Apr 2012 20:44:27 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 59358 invoked by uid 500); 12 Apr 2012 20:44:27 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 59350 invoked by uid 99); 12 Apr 2012 20:44:27 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 12 Apr 2012 20:44:27 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=5.0 tests=RCVD_IN_DNSWL_NONE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of saurabhex@live.com designates 65.54.190.150 as permitted sender) Received: from [65.54.190.150] (HELO bay0-omc3-s12.bay0.hotmail.com) (65.54.190.150) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 12 Apr 2012 20:44:18 +0000 Received: from BAY151-W35 ([65.54.190.189]) by bay0-omc3-s12.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Thu, 12 Apr 2012 13:43:58 -0700 Message-ID: X-Originating-IP: [208.95.100.4] From: Saurabh S To: Subject: Hive equivalent of row_number() Date: Thu, 12 Apr 2012 13:43:58 -0700 Importance: Normal Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-OriginalArrivalTime: 12 Apr 2012 20:43:58.0112 (UTC) FILETIME=[FBE61200:01CD18EC] X-Virus-Checked: Checked by ClamAV on apache.org I have a table with three columns=2C A=2C B=2C and Score=2C where A and B a= re some items=2C and Score is some kind of affinity between A and B. There = are N number of items of each A and B=2C so that the total number of rows i= n the table are N^2. Is there a way to fetch "top 5 items in B" for each item in A? So=2C for ea= ch distinct item in A=2C I want to look up 5 items in B which have the high= est value in Score. If this were to be done in DB2=2C I would probably use some kind of windowi= ng function using row_number(). =