Howdy, I've got a query which runs forever and am wondering if there's a
more efficient way to write it.

Select id, plan_name from table/view1 where plan_name = 'A' and rownum = 1
union
Select id, plan_name from table/view1 where plan_name = 'B' and rownum = 1
union
Select id, plan_name from table/view1 where plan_name = 'C' and rownum = 1
union.....etc

There are about 100 plan names, and I want one ID/owner for each plan_name.

On Feb 14, 9:59 am, "Ray Bannon" <raymond.w.ban...@boeing.com> wrote:
> Howdy, I've got a query which runs forever and am wondering if there's a
> more efficient way to write it.
>
> Select id, plan_name from table/view1 where plan_name = 'A' and rownum = 1
> union
> Select id, plan_name from table/view1 where plan_name = 'B' and rownum = 1
> union
> Select id, plan_name from table/view1 where plan_name = 'C' and rownum = 1
> union.....etc
>
> There are about 100 plan names, and I want one ID/owner for each plan_name.

wouldn't
SELECT DISTINCT id, plan_name from table/view1 ;
work?
I dislike DISTINCT since many folks use it for the wrong reasons, but
this seems a perfect place for it.

Ed

On Feb 14, 9:59 am, "Ray Bannon" <raymond.w.ban...@boeing.com> wrote:
> Howdy, I've got a query which runs forever and am wondering if there's a
> more efficient way to write it.
>
> Select id, plan_name from table/view1 where plan_name = 'A' and rownum = 1
> union
> Select id, plan_name from table/view1 where plan_name = 'B' and rownum = 1
> union
> Select id, plan_name from table/view1 where plan_name = 'C' and rownum = 1
> union.....etc
>
> There are about 100 plan names, and I want one ID/owner for each plan_name.

Maybe I am not reading the question correctly, but this seems to be
very easy to accomplish. You can speed it up a bit by changing UNION
to UNION ALL. Or, you can write the SQL statement so that it may be
executed efficiently. Example:
CREATE TABLE T3 (
ID NUMBER(10),
PLAN_NAME VARCHAR2(10));

Fill with a known sequence of numbers from 1 to 100:
INSERT INTO
T3
SELECT
ROWNUM,
TO_CHAR(ROWNUM)
FROM
DBA_OBJECTS
WHERE
ROWNUM<=100;

Fill in a second set of 100 numbers with somewhat random values:
INSERT INTO
T3
SELECT
TRUNC(ROWNUM*DBMS_RANDOM.VALUE(0,5)),
TO_CHAR(ROWNUM)
FROM
DBA_OBJECTS
WHERE
ROWNUM<=100;

Now, a simple GROUP BY:
SELECT
MAX(ID) ID,
PLAN_NAME
FROM
T3
GROUP BY
PLAN_NAME
ORDER BY
PLAN_NAME;

ID PLAN_NAME
========== ==========
2 1
10 10
385 100
41 11
12 12
18 13
21 14
17 15
58 16
51 17
23 18
19 19
5 2
83 20
27 21
22 22
73 23
71 24
49 25
49 26
124 27
113 28
29 29
6 3
30 30
40 31
32 32
33 33
149 34
168 35
177 36
37 37
39 38
39 39
16 4
168 40
57 41
209 42
104 43
123 44
57 45
69 46
116 47
48 48
56 49
7 5
77 50
172 51
212 52
53 53
84 54
232 55
254 56
275 57
107 58
181 59
22 6
151 60
287 61
172 62
287 63
98 64
289 65
238 66
67 67
145 68
178 69
10 7
147 70
204 71
321 72
95 73
211 74
147 75
196 76
163 77
78 78
276 79
35 8
250 80
278 81
244 82
261 83
102 84
219 85
86 86
407 87
88 88
112 89
9 9
259 90
222 91
92 92
357 93
94 94
95 95
96 96
350 97
268 98
490 99

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.