First record which meets GROUP BY and HAVING clauses

Question

I have a SQL Server query which takes data from Oracle 8i server using linked server connection. My question here is completely on Oracle SQL.

There is a StyleSizes table. One style can have one or more sizes associated with it. I need to find any one Style_CD which has more than one Size_CD. This can be achieved by GROUP BY and HAVING. But I need only one such style. The volume of the table is so big that I cannot wait till the query execution is complete – so I need only one style.

How to get only one record quickly after applying GROUP BY and HAVING clauses in Oracle?

SELECT STYLE_CD
FROM OPENQUERY(LinkedORAServer,  
   'SELECT STYLE_CD
   FROM DA.StyleSize M  
   GROUP BY STYLE_CD
   HAVING COUNT(SIZE_CD) > 1
    ')

FURTHER READING

@Veljko89 Do you mean TOP 1 in SQL Server or Oracle? It's no use to do it in SQL Server becuase it is an OPENQUERY. — LCJ, Mar 14 '16 at 13:44
What are you trying to get? One style that has multiple sizes? — Gordon Linoff, Mar 14 '16 at 13:44

score 2 · Answer 1 · answered Mar 14 '16 at 13:44

You should use ROWNUM which is similar to MySQL limit and SQL-SERVER top :

SELECT STYLE_CD
FROM OPENQUERY(LinkedORAServer,  
   'SELECT STYLE_CD FROM(SELECT STYLE_CD
                         FROM DA.StyleSize M  
                         GROUP BY STYLE_CD
                         HAVING COUNT(SIZE_CD) > 1)
    WHERE rownum = 1
    ')

Thomas · Answer 2 · 2016-03-14T13:46:19.533

2

SELECT * FROM (SELECT SIZE_CD, COUNT(STYLE_CD) OVER (PARTITION BY STYLE_CD) CNT FROM STYLESIZE) WHERE CNT > 1

edited Mar 14 '16 at 13:46

answered Mar 14 '16 at 13:44

Thomas

203
2
9

Oracle 8i didn't have analytic functions I'm sure. – Tony Andrews Mar 14 '16 at 13:45

score 2 · Accepted Answer · answered Mar 14 '16 at 13:46

2

If you want one style that has more than one size, quickly, then you can use exists:

select m.*
from da.stylesize m
where exists (select 1 from da.stylesize m2 where m2.style_cd = m.style_cd and m2.size_cd <> m.size_cd) and
      rownum = 1;

Then, you want to be sure you have an index on da.stylesize(style_cd, size_cd).

With this query, you don't even need to do the aggregation, which should be a big savings.

answered Mar 14 '16 at 13:46

Gordon Linoff

1,122,135
50
484
624

Thanks, this works. However in SQL Server, I can apply TOP 1 in a query with GROUP BY (and it uses different and efficient query plan). Why is Oracle not allowing TOP 1 on GROUP BY? [Your suggestion works fast enough - but it is difficult to read compared to GROUP BY aproach when more tables are involved]. – LCJ Mar 15 '16 at 17:17
@Lijo . . . Oracle doesn't support `TOP` in any query. – Gordon Linoff Mar 15 '16 at 21:26
Agreed - I already know that. I was referring to the approach. I consider `rownum = 1` in `where` and `Top 1` as logically same. SQL Server works fast with TOP 1 and GROUP BY - but Oracle is not working fast with `GROUP BY` and `rownum`. Is it a shortcoming of Oracle? – LCJ Mar 16 '16 at 02:31
1

@Lijo . . . They are not quite equivalent. `TOP` is evaluated *after* `ORDER BY`. `WHERE` is evaluated before `ORDER BY`. – Gordon Linoff Mar 16 '16 at 02:42
1

@Lijo . . . Oracle 12c supports the `fetch` clause which is the ANSI equivalent of `TOP` (actually better because it also allows an offset). – Gordon Linoff Mar 16 '16 at 02:46

score 1 · Answer 4 · answered Mar 14 '16 at 15:02

1

SELECT STYLE_CD
FROM OPENQUERY(LinkedORAServer,  
   'select VAL from (
select VAL,COUNT(*) from SANDEEP24FEB2016_2 group by VAL having COUNT(*)>1 
) where rownum=1
    ') ;

answered Mar 14 '16 at 15:02

Sandeep

716
3
8

First record which meets GROUP BY and HAVING clauses

4 Answers4