CODEWITHSUNDEEP
oracle-database
Echo
Echo

Reputation: 3029

Compare first row to other rows

The issue I face that I need after selecting multiple rows,to loop over each row and fetch those who have some related information to the first row.

Example:

select NAME,ENGLISH_GRADE,FRANCE_GRAE
from (some complex query that have order by and returns 100 rows) WHOLE_ROWS
where

//Here I need to loop over WHOLE_ROWS and make something like that:
//if(currentRow.ENGLISH_GRADE==WHOLE_ROWS(0).ENGLISH_GRADE)
//fetch this row

Upvotes: 0

Views: 331

Answers (1)

eaolson
eaolson

Reputation: 15094

Basically, you need to join your query to itself. You can do with with a subquery factoring clause:

WITH complex_query AS
    ( ... complex query here ... ) 
SELECT 
  FROM complex_query cq1
 WHERE cq1.english_grade = ( SELECT english_grade FROM cq1
                              WHERE rownum = 1 )

Here is a SQL Fiddle. You could also do this with analytics, but those seem to me more difficult to understand.

Upvotes: 1

Related Questions