RE: [sqr-users] fetch value in array

["Steve Cavill" <steve.cavill@infoclarity.com.au>]

Load the table into a temp table and use on-break to identify the last record 
on each employee group.
Steve. 

-----Original Message-----
From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org
[mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On Behalf 
Of Bob Stone
Sent: Saturday, 7 January 2006 3:59 AM
To: This list is for discussion about the SQR database reporting languagefrom 
Hyperion Solutions.
Subject: RE: [sqr-users] fetch value in array

wow.  convoluted example....nice use of the same 3 letters all caps for
5 different employees...real readable.

So, I'm going to pretend the first guy is ABC, second is LMN, and third is XYZ. 
 Or maybe we got Jones, Smith, and Grady.  ...XYZ
ZYX YZX YXZ, not so good.

select *
from YOURTABLE a
where (a.emplid, a.company, a.count) in 
       (select b.emplid, b.company, b.count from yourtable b
        where a.emplid = b.emplid and a.company = b.company
          and b.count = ( select max(c.count) from YOURTABLE c where

                      b.emplid = c.emplid and b.company = c.company)

Is there an easier way?  probably.  do you have to join 3 times?  maybe not.  
It's just what I would try.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of prashanth 
reddy
Sent: Friday, January 06, 2006 10:43 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] fetch value in array

Hi,
 
 I'm using array in sqr and data in array looks like
this:
 
Emplid  Company Count
XYZ       A      1
XYZ       A      2
XYZ       A      3
YXZ       B      1
YXZ       B      2
YXZ       B      3
ZYX       A      1
YZX       B      1

>From this array I'm supposed to get max count for each
emplid, i.e for emplid XYZ have to get line 3 (XYZ   A
 3), for emplid YXZ (YXZ  B 3) and for emplid ZYX (ZYX  A  1).

When i compare row value, I'm not able to get the correct results.
Any help or suggestion would be appreciated.

Thanks,
Prashanth      


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users