Results 1 to 9 of 9

Thread: SQL Help in DB2

  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: SQL Help in DB2

    Hi,

    I'm a beginner in SQL and just started studying recently. I found this problem on the net and it's been bugging me for sometime. The problem is, How to return top 2 rows for each category.

    Here is the table (Only 1 column)

    Table - Letters

    Alphabets
    A
    C
    B
    B
    B
    G
    A
    A
    C

    and i want it to look like this

    Alphabets
    A
    A
    B
    B
    C
    C
    G

    could someone help? it has given me countless of sleepless nights . Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fakeid147 View Post
    The problem is, How to return top 2 rows for each category.
    do a google or bing search for that phrase and you will find lots of results

    Quote Originally Posted by fakeid147 View Post
    Here is the table (Only 1 column)
    that example is over-simplified to the point where it has lost its meaning

    if there is only one column, then that column must be the primary key

    but since there are duplicates, it cannot be

    therefore this "table" is in 0NF

    problems on 0NF tables are trivial

    and if "countless sleepless nights" is true, you are in deeper trouble than you think

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    1. i did a lot of googling of "How to select top N rows per category" but most results show they're using PARTITION function in SQL and like i said I'm just a beginner. (I'm still googling for solutions while checking my post from time to time)

    2. Never mind the primary key, it's just a problem I'm using to enhance my knowledge about SQL but if you're uncomfortable with it, then let's just say there are 2 columns, the other is for the primary key, but still, i liked to prioritize in making an SQL which only uses the COLUMN "Alphabets" (But if this is impossible, by all means, use the primary key)

    3. "Countless Sleepless Nights", haven't you ever had that feeling where in you can't sleep because you can't fine the solution to a problem?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by fakeid147 View Post
    ..haven't you ever had that feeling where in you can't sleep because you can't fine the solution to a problem?
    Only if it is a homework assignment due the next day.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, here's the general pattern --
    Code:
    SELECT primary_key
         , grouping_column
         , value_column
      FROM daTable AS t
     WHERE ( SELECT COUNT(*) 
               FROM daTable  
              WHERE grouping_column = t.grouping_column
                AND value_column > t.value_column ) < 2
    i still foresee difficulty for you, as you don't appear to have a grouping column ("top 2 rows for each category")
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2010
    Posts
    4
    i solved it, it's just very simple

    i just used UNION ALL and union 2 queries

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    i just used UNION ALL and union 2 queries
    What query did you used?

    The query using UNION ALL which I could make was something tricky.
    Code:
    (
    SELECT DISTINCT
           Alphabets
      FROM Letters
    UNION ALL
    SELECT Alphabets
      FROM Letters
     GROUP BY
           Alphabets
    HAVING COUNT(*) >= 2
    )
     ORDER BY
           Alphabets
    ;
    ------------------------------------------------------------------------------
    
    ALPHABETS
    ---------
    A        
    A        
    B        
    B        
    C        
    C        
    G        
    
      7 record(s) selected.
    I think using OLAP specification is more simple and easy to understand.
    Code:
    SELECT Alphabets
      FROM (SELECT Alphabets
                 , ROW_NUMBER() OVER(PARTITION BY Alphabets) rn
              FROM Letters
           ) s
     WHERE rn <= 2
     ORDER BY
           Alphabets
    ;
    ------------------------------------------------------------------------------
    
    ALPHABETS
    ---------
    A        
    A        
    B        
    B        
    C        
    C        
    G        
    
      7 record(s) selected.

  8. #8
    Join Date
    Mar 2010
    Posts
    4
    @tonkuma - yeah that's exactly the query i used... and thanks for giving me another way (OLAP) appreciated it very much

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example
    (It might be not so effective. My curiosity only.)
    Code:
    (
    SELECT DISTINCT
           Alphabets
      FROM Letters
    UNION ALL
    SELECT DISTINCT
           *
      FROM (
           SELECT Alphabets
             FROM Letters
           EXCEPT ALL
           SELECT DISTINCT
                  Alphabets
             FROM Letters
           ) AS q
    )
     ORDER BY
           Alphabets
    ;
    ------------------------------------------------------------------------------
    
    ALPHABETS
    ---------
    A        
    A        
    B        
    B        
    C        
    C        
    G        
    
      7 record(s) selected.

Posting Permissions

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