CODEWITHSUNDEEP
sqloracle-database
Lalith
Lalith

Reputation: 13

How to generate the first values of a specific set of column value in a table..?

Lets take the below example,

SELECT * FROM user_objects WHERE status in ('INVALID','VALID') AND ROWNUM<=2;

By using the above query, I can only get the first two values in a table irrespective of whether the status is 'VALID' or 'INVALID'.

Lets say, If the first & second value of status column are 'INVALID', I may only get the rows having 'INVALID' status. And if the first & second value of status column are both 'VALID' & 'INVALID', I may get rows having both status column. Finally, If the first & second value of status are 'VALID', I may only get the rows having 'VALID' status column.

What I need is, irrespective of the 'status' value present in an order in a column, I require the first values of both 'VALID' & 'INVALID' using a dynamic query.

The output should be something of this sort,

enter image description here

Thanks in advance for your help..

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Your query has nothing to do with the "first rows" in the table. Tables represent unordered sets. Hence, the result set can be the rows in any order.

In any case, if you want the first row -- based on created -- for each status, then use row_number():

SELECT uo.*
FROM (SELECT uo.*,
             ROW_NUMBER() OVER (PARTITION BY status ORDER BY CREATED) as seqnum
      FROM user_objects uo
      WHERE status in ('INVALID', 'VALID') 
     ) uo
WHERE seqnum = 1

Upvotes: 2

Related Questions