How to select the first unique data type in a table

oraclesyntax

Sorry for the messy layout question. This is my first post.

I have the following table structure under ORACLE database

List item

  Row     A      B        Date
  --------------------------------
  1       X      X        01-2012
  2       X      X        01-2013
  3       Null   Null     01-2013
  4       New    Apple    01-2016
  5       New    Orange   08-2015 
  6       Null   Null     09-2015

I want to be able to query for the first time that "New" is in the database entry and get the value for both A, B and Date

Tried using syntax first_row but i get an invalid identifier result. Also attempted to create subqueries to limit and group by 'New' data but the issue I'm having is to only look at the "first" ever row per Date

Best Answer

Due to the bad naming convention on your question, I will use ColumnA as A, ColumnB as B and ColumnDate as Date.

I am assuming that you require the first item as it's fetched from storage, no particular order. And you have to know that if the row gets updated it can be migrated from the current block and so, the query result will eventually change.

This queries are examples only and you have to edit them to get exactly what you want.

You may try the following query:

SELECT ColumnA, ColumnB, ColumnDate
FROM YOURTABLE
WHERE ColumnA='New'
AND rownum = 1;

In Oracle every result set have an implicit row number that you can use for limiting output.

If, by any chance, you're using oracle 12c you can use the brand new feature for top n queries

SELECT ColumnA, ColumnB, ColumnDate
FROM YOURTABLE
WHERE ColumnA='New'
FETCH FIRST 1 ROWS ONLY;

I hope this helps!

Related Question