# Thread: top 3 records per field X

1. Registered User
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. Registered User
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
•