0

I am currently writing a Java program.

Brief description of the component:

I have an "Entries" table. This table has the following columns: Date (which is entered automatically when the user makes a double entry) Input (This is the double number from the user)

With 5 entries, for example, the program should now access the last 2 entries made by the user and reflect them in the program.

For example, the table looks like this:

Date --------- Entry

21.01.2022 -- 500

01.03.2022 -- 551

04.05.2022 -- 629

30.06.2022 -- 701

15.07.2022 -- 781

Then the program should give me the 701 and the 781.

What is the most sensible way to do this? It makes no sense to use the following "SQL statement": Select where date 06/30/2022 because it is no longer useful when the user makes a new entry.

Please help!!

Improve this question
3
  • 1
    Does this answer your question? SQL Server SELECT LAST N Rows Commented Aug 11, 2022 at 11:48
  • @sorifiend, MS SQL Server (which is discussed in link you attached) is not Oracle (which is database the OP is using) so ... I wouldn't say that it answers the question. "SQL" tag is related to the language, not database vendor. Commented Aug 11, 2022 at 12:12
  • No this question is not helping. Thanks tho Commented Aug 11, 2022 at 13:32

2 Answers 2

Reset to default
0 
select Entry from your_table order by date desc -- show most recent entries above in the results fetch first 2 rows only; -- show first 2 records only

You might be running on an old DB (version less than 12) so "fetch" might not be introduced.

Try this then

select * from (select Entry from your_table order by date desc) where rownum <= 2;

Answering to your question how to get a penultimate value

select * from (select Entry, row_number() over(order by date_col desc) rn from your_table) where rn = 2;

the "rn = 2" condition will get you not the last date but a date before the last. Setting it to 1 will get you the row with most recent date

Improve this answer
Sign up to request clarification or add additional context in comments.

18 Comments

This also gives me the error: "ORA-00936: Expression missing". I assume "Where" is missing. A select must always have SELECT, FROM, WHERE. In your example we only have Select and From ...?
@singhKirat Nope, WHERE clause is not required. It has to have SELECT (to know what to select) and FROM (to know where to select from) What db version are you on? Another reason might be the column name. "date" is a reserved word and it would be better to rename it to, say, "date_col"
I am using Oracle SQL Developer Version 21.4.2.018 Build 018.1706. Nope "date" was just an example, i acctually am using another name.
@singhKirat SQL Developer is just a tool for connecting to the DB and performing queries. Please perform "select version from v$instance;" in order to get database version
Could you please repeat the statement maybe there is a typo. It says "ORA-00942: table or view does not exist"
|
0

You can use the following SQL statement to select the last two rows:

select * from Entries order by date desc limit 2;
Improve this answer

3 Comments

This gives me the error: "ORA-00936: Expression missing". I assume "Where" is missing. A select must always have SELECT, FROM, WHERE. In your example we only have Select and From ...?
Oh? Since when is WHERE obligatory? select * from dual works perfectly fine, @singhKirat. Problem with this code is in limit clause - Oracle doesn't support it. Which database version do you use?
I am using Oracle SQL Developer Version 21.4.2.018 Build 018.1706.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.