May 7, 2004 at 1:08 am
What I want to display is TOP INSTRUMENTS. Here is what I want to do. Lets say my instrument and performance table look like this:
Instrument Perfomance
AAA 10
BBB 200
CCC 300
DDD 40
EEE 50
FFF 6000
GGG 700
HHH 80
III 90
JJJ 100
KKK 20
LLL 3000
MMM 400
NNN 5000
OOO 60
PPP 700
If BIN = 1 then I want to display Top4 performing Instruments. (I shortened my performance to 4)
I want my results to be
FFF 6000
NNN 5000
LLL 3000
PPP 700
If BIN = 2 then I want to display Top4 (next level) performing Instruments. (I shortened my performance to 4)
I want my results to be
MMM 400
CCC 300
BBB 200
JJJ 100
And so on and so on
May 10, 2004 at 8:00 am
This was removed by the editor as SPAM
May 10, 2004 at 9:54 am
If I get you right, you want something like this in a stored procedure:
/* CREATE TABLE AND INSERT VALUES FOR TEST*/
CREATE TABLE TABPERF
(INSTRUMENT NVARCHAR(100),
PERFORMANCE INTEGER)
INSERT INTO TABPERF VALUES ('AAA', 100)
INSERT INTO TABPERF VALUES ('BBB', 50)
INSERT INTO TABPERF VALUES ('CCC', 450)
INSERT INTO TABPERF VALUES ('DDD', 800)
INSERT INTO TABPERF VALUES ('EEE', 200)
INSERT INTO TABPERF VALUES ('FFF', 180)
INSERT INTO TABPERF VALUES ('GGG', 600)
INSERT INTO TABPERF VALUES ('HHH', 650)
INSERT INTO TABPERF VALUES ('III', 250)
INSERT INTO TABPERF VALUES ('JJJ', 10)
/* TEST OUT SCENARIOS - CHANGE BIN TO 1 OR 2 TO SEE DIFFERENCE*/
DECLARE @BIN INT
SET @BIN = 1
If @BIN = 1
BEGIN
SELECT TOP 4 (PERFORMANCE), INSTRUMENT
FROM TABPERF
ORDER BY PERFORMANCE DESC
END
IF @BIN = 2
BEGIN
SELECT TOP 4 (PERFORMANCE), INSTRUMENT
FROM TABPERF
WHERE PERFORMANCE NOT IN(
SELECT TOP 4 (PERFORMANCE)
FROM TABPERF
ORDER BY PERFORMANCE DESC)
ORDER BY PERFORMANCE DESC
END
--------------------------------------------
BIN = 1 gives you the top 4
BIN = 2 gives you the next four - effectively top 5-8
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply