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

Question

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,

Thanks in advance for your help..

You need only first 2 rows or you need first row with status 'Invalid' and 'valid'? — Jibin Balachandran, Dec 15 '15 at 12:15
See [**How ROWNUM works**](http://stackoverflow.com/a/30321788/3989608) — Lalit Kumar B, Dec 15 '15 at 12:25
You need to use **ROW_NUMBER()** analytic function to get your desired output. — Lalit Kumar B, Dec 15 '15 at 12:25

score 2 · Accepted Answer · answered Dec 15 '15 at 12:24

2

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

answered Dec 15 '15 at 12:24

Gordon Linoff

1,122,135
50
484
624

I need the rows having the first occurrence of the status 'VALID' or 'INVALID' in the table. Your query is giving me random occurrences – Lalith Dec 16 '15 at 05:43
@Lalith . . . The rows should not be random. They should be the ones with the minimum values of `CREATED`. – Gordon Linoff Dec 17 '15 at 03:10

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

1 Answers1