0

Is there any way to find the last 500 records from a table with out using union, union all or minus function?

Can we achieve this using rank, row_number or dense_rank functions in sql.

Thanks Rakesh

MT0
  • 143,790
  • 11
  • 59
  • 117
rakesh
  • 1
  • 4
    Define "last 500" - SQL result sets are unordered unless an `ORDER BY` clause is used. Also, you have this tagged for both Teradata and Oracle - which are you actually using? – Bob Jarvis - Слава Україні Mar 14 '20 at 15:35
  • What do you mean by "last 500 records"? I assume you mean "last 500 rows" - but what do you mean by "last 500"? –  Mar 14 '20 at 15:36
  • example : select * from emp; this result table will have 1000 records. i have to get last 500 records of this table with out using any set operators and we should also not use order by clause. is there any way to get the result? – rakesh Mar 15 '20 at 12:52
  • I am using Oracle. I tagged teradata, because i thought both would be more or less same – rakesh Mar 15 '20 at 12:57

2 Answers2

1

Teradata uses TOP

SELECT TOP 500 * FROM table ORDER BY your_column

Oracle 12c+ uses FETCH:

SELECT * FROM TABLE ORDER BY your_column DESC FETCH FIRST 500 ROWS ONLY

Older oracle uses rownum, and the orderby must be done in a subquery:

SELECT * FROM (SELECT * FROM TABLE ORDER BY your_column DESC) WHERE rownum <= 500

You could use ROW_NUMBER in a DB that supports it:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY your_column DESC) rn FROM TABLE) WHERE rn <= 500

your_column is used to determine "last"ness.. It needs to be something that sorts sensibly, like a numeric id, date etc

Edit:

Your interviewer expected you to use analytical functions. Here's what it would look like:

SELECT * 
FROM
  (
    SELECT *, ROW_NUMBER() OVER(ORDER BY your_column DESC) as rn
    FROM table
  ) x
WHERE x.rn < 501

Not that it still needs an order by; here's what happens when you skip it:

enter image description here

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Thank you for the reply. But we should not use 'order by' clause also. Just we have to retrieve last 500 records from a table. – rakesh Mar 15 '20 at 13:15
  • Where in your question does it state "not allowed to use order by" ? It's quite bad etiquette to ask a question, get a detailed answer and then say "oh, your answer is wrong because of something I never told you" - if you make a mistake in your question and trying to change it would seriously affect the answers, it is better to just accept one of the answers and ask a new question. If you don't use order by then there is no such thing as "last". This question is now impossible to answer, i'm afraid, probably because you cannot have a notion of "last" in SQL DB without ordering the rows – Caius Jard Mar 15 '20 at 15:31
  • I was asked this question in interview and he told that not to use set operators and order by.he told we can acheive this through analytical functions.I tried but i didn't get the solutions. Thought like i would get some solution from this group.So i posted here. Sorry if the question is completely wrong. – rakesh Mar 16 '20 at 03:45
  • Analytical functions use ORDER BY, so he's wrong I'm afraid – Caius Jard Mar 16 '20 at 07:09
  • I made an edit anyway. Next time you're asking here for advice on an interview question, please clearly state that it is the case. We end up second guessing your misunderstanding of a question that *someone else* asked (and also may not have understood) - it's better to be up front about it because we are dealing not only with the actual question but a potential misunderstanding of it driven by lack of knowledge in the subject area (both yours and possibly the interviewer's) – Caius Jard Mar 16 '20 at 07:23
-2

You can use the SQL limit with orderby desc to get the last N number of records.

select * from tbl_name order by id desc limit N;
Nagender Gupta
  • 27
  • 12