Thread: Limit View

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Tibet
    Posts
    1
    Rep Power
    0

    Limit View


    Hi Friends,

    I have some problem, maybe someone in this forum can solve it.

    This is my syntax :

    SELECT HS.BRANCH_ID BRANID,
    HS.DIVISION_ID DIVID,
    HS.SALESMAN_ID SALESMAN,
    HS.CUSTOMER_ID CUSTID,
    CUST.CUSTOMER_NAME CUSTNAME,
    SUM(HS.GROSS_SALES)GS,
    SUM(HS.DISCOUNT)DISC
    FROM H_SALES HS,
    CUSTOMER CUST
    WHERE HS.TRANS_MM=:TMONTH
    AND HS.TRANS_YY=:TYEAR
    AND HS.BRANCH_ID=:TORG
    AND HS.DIVISION_ID=:TDIV
    AND CUST.BRANCH_ID=:TORG
    AND CUST.DIVISION_ID=:TDIV
    AND CUST.CUSTOMER_ID=HS.CUSTOMER_ID
    GROUP BY HS.BRANCH_ID,
    HS.DIVISION_ID,
    HS.CUSTOMER_ID,
    CUST.CUSTOMER_NAME,
    HS.SALESMAN_ID
    ORDER BY HS.SALESMAN_ID,SUM(HS.GROSS_SALES)DESC

    My syntax result

    Branid Divid Salesman Custid GS Disc
    ------- ----- ----------- ------- ----------- --------
    01 01 John 2022 1,025 102.5
    01 01 John 2015 915 91.5
    01 01 John 2036 850 85.0
    Until 100 records

    01 01 Adam 2045 2,226 222.6
    01 01 Adam 2071 1,524 152.4
    Until 150 records

    01 01 Smith 2039 1,315 131.5
    01 01 Smith 2085 750 75.0
    Until 175 records

    01 01 Kevin 3156 2,423 242.3
    01 01 Kevin 3189 1,450 145.0
    Until 75 records

    I want to view only 20 % records of salesman :

    Branid Divid Salesman Custid GS Disc
    ------- ----- ----------- ------- ----------- --------
    01 01 John 2022 1,025 102.5
    01 01 John 2015 915 91.5
    01 01 John 2036 850 85.0
    Until 20 records ( 20 % of 100 records )

    01 01 Adam 2045 2,226 222.6
    01 01 Adam 2071 1,524 152.4
    Until 30 records ( 20 % of 150 records )

    01 01 Smith 2039 1,315 131.5
    01 01 Smith 2085 750 75.0
    Until 35 records ( 20 % of 175 records )

    01 01 Kevin 3156 2,423 242.3
    01 01 Kevin 3189 1,450 145.0
    Until 15 records ( 20 % of 75 records )

    what is a syntax for the result above ?

    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    I think you can do it in one SQL statement. The key thing is to assign consecutive numbers to the rows that you are grouping together using the rank function.
    For example, here is rank applied over the tab view:

    select
    tname, tabtype, rank() over (partition by tabtype order by tname) d
    from tab


    My result set looks like:

    mysynoym1 SYNONYM 1
    mytable1 TABLE 1
    mytable2 TABLE 2
    mytable3 TABLE 3
    mytable4 TABLE 4
    myview1 VIEW 1
    myview2 VIEW 2

    Hope it helps,
    Dan

IMN logo majestic logo threadwatch logo seochat tools logo