Results 1 to 4 of 4

Thread: Simple query?

  1. #1
    Join Date
    Jan 2002
    Posts
    16

    Unanswered: Simple query?

    I have a table that looks like this:
    c1 c2
    1 a
    1 b
    1 c
    2 a
    2 b
    2 c
    2 d
    3 a
    3 b
    3 c
    3 d
    3 e


    I want a result set that looks like this:
    c1 c2 c3
    1 a 1
    1 b 2
    1 c 3
    2 a 1
    2 b 2
    2 c 3
    2 d 4
    3 a 1
    3 b 2
    3 c 3
    3 d 4
    3 e 5

    Basically, grouping/numbering the rows by column c1. The number of distinct c1 columns and distinct c1,c2 columns is varying.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    Code:
    CREATE TABLE tGeorgio (
       c1		INT		NOT NULL
    ,  c2		CHAR(1)		NOT NULL
       PRIMARY KEY (c1, c2)
       )
    GO
    
    INSERT INTO tGeorgio (c1, c2)
       SELECT           1, 'a'
       UNION ALL SELECT 1, 'b'
       UNION ALL SELECT 1, 'c'
       UNION ALL SELECT 2, 'a'
       UNION ALL SELECT 2, 'b'
       UNION ALL SELECT 2, 'c'
       UNION ALL SELECT 2, 'd'
       UNION ALL SELECT 3, 'a'
       UNION ALL SELECT 3, 'b'
       UNION ALL SELECT 3, 'c'
       UNION ALL SELECT 3, 'd'
       UNION ALL SELECT 3, 'e'
    
    SELECT c1, c2
    , (SELECT Count(*)
          FROM tGeorgio AS b
          WHERE  b.c1 = a.c1
             AND b.c2 <= a.c2) AS c3
       FROM tGeorgio AS a
       ORDER BY 1, 2, 3
    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    357

    Thumbs up

    Hi,
    Other possible way of Pat Phelan method is
    Code:
    select t1.c1,t1.c2,count(*) as count from tGeorgio t1, tGeorgio t2 
    where t1.c1=t2.c1 and t1.c2>=t2.c2  group by t1.c1,t1.c2
    Madhivanan
    Last edited by Madhivanan; 11-30-04 at 01:01.

  4. #4
    Join Date
    Jan 2002
    Posts
    16
    Thanks. The mental block has ben lifted.

Posting Permissions

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