SQL Help in DB2

fakeid147 · #1 (**permalink**) 03-01-10, 21:05

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

r937 · #2 (**permalink**) 03-01-10, 22:31

Quote:

Originally Posted by fakeid147

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

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

fakeid147 · #3 (**permalink**) 03-01-10, 22:46

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?

Marcus_A · #4 (**permalink**) 03-01-10, 23:26

Quote:

Originally Posted by fakeid147

..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.

r937 · #5 (**permalink**) 03-02-10, 05:17

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")

fakeid147 · #6 (**permalink**) 03-07-10, 22:41

i solved it, it's just very simple

i just used UNION ALL and union 2 queries

tonkuma · #7 (**permalink**) 03-08-10, 06:17

Quote:

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.

fakeid147 · #8 (**permalink**) 03-12-10, 20:41

@tonkuma - yeah that's exactly the query i used... and thanks for giving me another way (OLAP) appreciated it very much

tonkuma · #9 (**permalink**) 03-14-10, 17:07

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech