Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    12

    Question Unanswered: Dynamic List Filtering - Very Tricky!

    I would like to create a sql statement that filters a list based upon a dynamic "available" quantity. Any thoughts?

    Example...

    VIEW1-LIST WITHOUT FILTER
    UniqueID,Fruit
    1,Apple
    2,Watermelon
    3,Orange
    4,Apple
    5,Apple
    6,Orange
    17,Cherry
    18,Cherry
    20,Apple
    24,Apple
    25,Orange

    SCENARIO 1
    VIEW2-AVAILABLE
    Fruit,Available
    Orange,3
    Watermelon,0
    Apple,2
    Cherry,1

    VIEW3-LIST WITH FILTER
    1,Apple
    3,Orange
    5,Apple
    6,Orange
    17,Cherry
    25,Orange

    SCENARIO 2
    VIEW2-AVAILABLE
    Fruit,Available
    Orange,0
    Watermelon,1
    Apple,0
    Cherry,1

    VIEW3-LIST WITH FILTER
    2,Watermelon
    17,Cherry

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If I have understood correctly, you can do this using the analytic function RANK:
    Code:
    SQL> select * from view1;
    
      UNIQUEID FRUIT
    ---------- --------------------
             1 Apple
             2 Watermelon
             3 Orange
             4 Apple
             5 Apple
             6 Orange
            17 Cherry
            18 Cherry
            20 Apple
            24 Apple
            25 Orange
    
    SQL> select * from view2;
    
    FRUIT                 AVAILABLE
    -------------------- ----------
    Orange                        3
    Watermelon                    0
    Apple                         2
    Cherry                        1
    
    SQL> select v1.uniqueid, v1.fruit
      2  from
      3  ( select uniqueid, fruit, rank() over (partition by fruit order by uniqueid) rnk
      4    from view1
      5  ) v1, view2 v2
      6  where v1.fruit = v2.fruit
      7  and v1.rnk <= v2.available
      8  order by v1.uniqueid;
    
      UNIQUEID FRUIT
    ---------- --------------------
             1 Apple
             3 Orange
             4 Apple
             6 Orange
            17 Cherry
            25 Orange

  3. #3
    Join Date
    Feb 2004
    Posts
    12

    Thumbs up

    That works perfect! Thanks for the quick and easy to understand example.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •