Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: top 3 records per field X

    Hi all,

    I'm working on Sybase IQ 12.6.

    I'm trying to write a nice query that finds the top 3 values of field X.
    I will give an example:
    lets say this is the data:
    KITA_A 300
    KITA_A 345
    KITA_A 234
    KITA_A 550
    KITA_B 112
    KITA_B 237
    KITA_B 987
    KITA_B 900

    so I want the output to be:
    KITA_A 550
    KITA_A 345
    KITA_A 300
    KITA_B 987
    KITA_B 900
    KITA_B 237

    Do you have any idea?

    Thanks!!

  2. #2
    Join Date
    Aug 2007
    Posts
    2
    Hi,

    I'm afraid it can't be done with a single query, but the snippet below should do the trick. Be aware though, that it requires at least 3 values per group.

    Grtz,

    Jeroen

    CREATE TABLE C (A CHAR(10), B INT)
    go
    INSERT INTO C (A, B) VALUES ('KITA_A', 300)
    INSERT INTO C (A, B) VALUES ('KITA_A', 345)
    INSERT INTO C (A, B) VALUES ('KITA_A', 234)
    INSERT INTO C (A, B) VALUES ('KITA_A', 550)
    INSERT INTO C (A, B) VALUES ('KITA_B', 112)
    INSERT INTO C (A, B) VALUES ('KITA_B', 237)
    INSERT INTO C (A, B) VALUES ('KITA_B', 987)
    INSERT INTO C (A, B) VALUES ('KITA_B', 900)
    go
    select * from C
    SELECT A,B INTO D FROM C
    DELETE D WHERE B = (SELECT MAX(B) FROM D D2 WHERE D.A = D2.A)
    DELETE D WHERE B = (SELECT MAX(B) FROM D D2 WHERE D.A = D2.A)
    DELETE D WHERE B < (SELECT MAX(B) FROM D D2 WHERE D.A = D2.A)
    SELECT A,B
    FROM C
    WHERE B >= (SELECT B
    FROM D
    WHERE D.A = C.A
    )
    ORDER BY A, B DESC

    DROP TABLE D

    go

Posting Permissions

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