0

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

5
  • I tried using the following: select first_row(New) from table where A <> 'W' and B <> 'W" but no luck
    – Isaiah3015
    Oct 28 '15 at 19:03
  • What RDBMS are you using? MySQL, MSSQL, PostgreSQL or some other?
    – jkavalik
    Oct 28 '15 at 20:15
  • How do you define "first"? With the oldest Date or some ID?
    – jkavalik
    Oct 28 '15 at 20:16
  • Thanks for the response. I am using ORACLE. Also by first i mean the first row of a specific data entry. In the case above it's row 4 because i am looking for "New"
    – Isaiah3015
    Oct 28 '15 at 20:34
  • 2
    Table is a SET of rows, in theory unsorted unless you define some sorting, so "first" does not have any meaning by itself, you have to say "first by Date" or "first by ID" or maybe "first by random". There is no implicit "row number" unless you explicitly added some auto incrementing column and want to use that to decide what came "first".
    – jkavalik
    Oct 28 '15 at 20:51
1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.