RE: [sqr-users] MAX() Query

[Bob Stone <bstone@fastenal.com>]

I think the first one is what you want, except you should order by a.bal_amt
desc.  The order 'descending' will start with the highest.  

There could possibly be something wrong with the rest of it, but I dont know
your tables and couldn't say whether or not you're joining them correctly.



SELECT  a.BAL_amt, A.CUST_ID, B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL,
C.BAL_CURRENT
FROM PS_CUST_DATA A, PS_CUSTOMER B, PS_DAILY_AG_VW_L C
WHERE A.BUSINESS_UNIT = 'LATIS'
AND A.CUST_ID = B.CUST_ID
AND A.CUST_ID = C.CUST_ID
AND substr(B.SEGMENT_ID,1,1) NOT IN ('W','A')
AND  rownum < 11
and  a.BAL_amt <= (select max(bal_amt) from ps_cust_data)
order by a.bal_amt DESC





-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Turner, Ivan
Sent: Wednesday, November 24, 2004 2:05 PM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: [sqr-users] MAX() Query


I am trying to get the ten highest balance amounts from the db.  None of
these queries do the job.  Does anyone know how I can do this?

SELECT  a.BAL_amt, A.CUST_ID, B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL,
C.BAL_CURRENT
FROM PS_CUST_DATA A, PS_CUSTOMER B, PS_DAILY_AG_VW_L C
WHERE A.BUSINESS_UNIT = 'LATIS'
AND A.CUST_ID = B.CUST_ID
AND A.CUST_ID = C.CUST_ID
AND substr(B.SEGMENT_ID,1,1) NOT IN ('W','A')
AND  rownum < 11
and  a.BAL_amt <= (select max(bal_amt) from ps_cust_data)
order by a.bal_amt

SELECT A.CUST_ID, max(a.bal_amt), B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL,
C.BAL_CURRENT
FROM PS_CUST_DATA A, PS_CUSTOMER B, PS_DAILY_AG_VW_L C
WHERE A.CUST_ID = B.CUST_ID
AND A.CUST_ID = C.CUST_ID
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.BUSINESS_UNIT = 'LATIS'
AND substr( B.SEGMENT_ID,1,1) NOT IN ('W','A')
AND  rownum < 11
group by A.CUST_ID, B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL, C.BAL_CURRENT,
C.BAL_30, C.BAL_60, C.BAL_90, C.BAL_120, C.BAL_150, C.BAL_180
/

SELECT A.CUST_ID, max(a.bal_amt), B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL,
C.BAL_CURRENT
FROM PS_CUST_DATA A, PS_CUSTOMER B, PS_DAILY_AG_VW_L C
WHERE A.CUST_ID = B.CUST_ID
AND A.CUST_ID = C.CUST_ID
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.BUSINESS_UNIT = 'LATIS'
AND substr( B.SEGMENT_ID,1,1) NOT IN ('W','A')
AND  rownum < 11
group by A.CUST_ID, B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL, C.BAL_CURRENT,
C.BAL_30, C.BAL_60, C.BAL_90, C.BAL_120, C.BAL_150, C.BAL_180
having max(a.bal_amt) > 100000
/

_______________________________________________
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