RE: [sqr-users] MAX() Query

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

Or say "begin-select loops=10"

-----Original Message-----
From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org
[mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org]On
Behalf Of the dragon
Sent: Wednesday, November 24, 2004 12:55 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] MAX() Query


Use the first one, and place an sqr counter in the select statement, then 
have the select end when you get the 10th or last value.

peace,
clark 'the dragon' willis



PSA: Salary <> Slavery. If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul. Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent. Be careful lest you let other people 
spend it for you."

Carl Sandburg
(1878 - 1967)

----Original Message Follows----

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