Skip to Content

What is the SQL to delete the first N records from a table?

Sep 27, 2017 at 06:41 AM


avatar image

I saw in discussions a solution as below :

delete from MY_TABLE where (<key_field1>, <key_field2>, <...>) in (select top <N> <key_field1>, <key_field2>, <...> from MY_TABLE order by <sort_field_list>);

But in my case I can't compare the fields as it can delete other duplicate records which are in table after first N rows. I want to delete only first N rows.

10 |10000 characters needed characters left characters exceeded

Are you using a table without a primary or why the statement does not work for you?


Besides Florian's question: what is the scenario that you want to solve with "deleting the first N records"?

* Please Login or Register to Answer, Follow or Comment.

3 Answers

Jyoti Senapati Sep 27, 2017 at 08:25 AM


You can modify your query as below.

delete from MY_TABLE where (<key_field1>, <key_field2>, <...>) in

(select top <N> <key_field1>, <key_field2>, <...> from

(select distinct * from MY_TABLE order by <sort_field_list>));



10 |10000 characters needed characters left characters exceeded
Bilen Cekic Oct 03, 2017 at 03:01 AM

you can use "$rowid$" . (don't use capital or it won't work)
no one mentioned but each table in sap_hana has a hidden column which is "$rowid$"
you can use below code;

NOTE : Please use $rowid$ at your own risk. it is undocumented, unsupported and totally not recommended. ( Lars Breddemann has already explained technically as below why we should not use it)

delete from "TABLE_NAME" where "$rowid$" < 1000

The value of the $rowid$ column is related to the partition ID. For a table without a partition, the value ranges from 1 to 18014398509481984. On a partitioned table, the value for partition 1 starts at 18014398509481985; partition 2 starts at 36028797018963969; partition 3 starts at 54043195528445953; and so on. Means from $rowid$ you can get your partition ID as well (if you need that info ). Lars Breddemann can give more info on that.

I am telling again; $rowid$ column is not officially supported by SAP. Therefore, this option is to be used with caution and at your own risk. Also, due to triggering some internal processes, you will incur a performance penalty on queries that contain the hidden column. In general, it is better to choose features that SAP HANA officially support. To be honest changing the behaviour of $rowid$ inside the sap hana landscape is probably harder than shutting down whole company. Even putting an extra comma inside any of the HANA source code probably requires board meetings, 500-600 internal discussions, views of sap technical architects and some democratic votings inside. Anyway none of those should courage us to ise $rowid$. Better go for standard approaches which already exists inside.

Show 8 Share
10 |10000 characters needed characters left characters exceeded

The reason for nobody mentioning this is that the usage of "$rowid$" is not supported.

And nobody guarantees that the order of rowid values has anything to do with the order of records which should be deleted. "First N records of a table" by definition only makes sense if you provide an ORDER BY condition. Using "$rowid$" does not provide you with some sort of "natural" order of the records, it provides you with no order at all.


what does it mean it is not supported ? You can use it how you want based on your scenario.

"first N record" based on what first ?. if it is just "select * from table" (As long as you don't use CE functions), then the order will be same as $rowid$


"not supported" means exactly that: there is no support for this.

Whatever happens when you use this internal column is all on you. SAP won't help here if it doesn't do what you want or delete your data ...

And your intuition about the sort order as well as the influence of CE functions in this context is wrong, as well. We don't perform any sorting by $rowid$, so the order, in fact, is accidental. It is mostly based on the order HANA reads data from the main and the delta store of a column table.

E.g. if you have two overlapping transactions, with one finishing and triggering a delta merge while the other transaction is still open, you can see rowids out of order. Are $rowid$ values fetched internally for every changed record in a monotonous fashion? Yes! Does that mean that this determines the reading order? Nope, not at all.

As always: no order by, no guarantee for sorting !


first of all whatever you are thinking about "$rowid$" is wrong. it doesn't work based on how HANA reads.
If you were right, when you delete rows from a table and use "$rowid$" it should give you new id's right ? But it won't!

You will exactly get whatever you receive on your previous result and also a new created id for the new inserted member. If you have several partitions on a table, again it will be given in same logic.
means row id can go like that 1,2,3,4,8,9,25 not like a straight ascending order which is ALWAYS starting from 1.

Delete the first row and your ROWID will start from 2.

In your EXTREME example, rowid will be given to the first row created on the table thats all.

Second title says "Deleting first N records" and says table has duplicates. Can you tell me how will you decide which duplicate is first which duplicate is second ? I am giving an alternate solution, please don't approach like i give a solution "Truncate database". Don't overreact. ROWID may not be a very correct way (for me having duplicate in a table is another issue), you can even generate dynamic rownumber based on select statement and start from 1 for each unique row.


"first of all whatever you are thinking about "$rowid$" is wrong"... that's a bold statement, especially considering my background in HANA and access to the source code. But hey, your opinion, I give you that. What I don't endorse is the tone of that comment. Dialing it back a bit would be very much appreciated.

I think you got my comment about the effect of reading wrong. Yes, rows get their rowids assigned, when they are created. But the output order, when no ORDER BY is provided, is not necessarily in order of those rowids values.

See the difference here?

Concerning what you call an extreme example: for transactional systems this is the normal state. Multiple transaction run overlapping data changes against the same set of tables. If your notion of a database table is that of a one-statement for one user at a time, then I'd say this is a rather special case.

Coming back to the question you put at the end of your comment: how do you de-duplicate tables without any primary key and without using rowid? There are multiple ways available (creating a key column, copy a distinct dataset into a temp table, replacing the table with a view that does a distinct projection) to de-duplicate the data, but more importantly, the data model needs to be fixed.

You wrote that your solution doesn't delete the whole table. Likely not, but if it would do that, you couldn't complain to anyone, as using rowid is not supported. See how this works? Using this column takes away all guarantees you would get with supported columns. You may also want to check SAP note if you don't like the way I write it...


calm down champion, i understood :D but even Hasso Plattner and Sikka comes and tells me the same things, my views won't change. i am not insisting rowid is a proper way, i am telling it is another workaround. Even in the blogposts they are using rowid to populate data. HANA might change rowid in the future, they can drop that column or generate differently. I remember they change their UPDATE table syntax whenever there is an inner join (i think at Sp8 ) in the query and all my procedures which has update with inner join started giving syntax error. "HANA doesn't support" does not seem so dangerous :) but of course should be discouraged to use rowid.

Sorry for the tone of my comment, i am not telling in a rude way. What i don't get it, don't react like i give a solution which will reveal hana secrets or explode user database


Look, this is not about being the "HANA police" or anything like that. It's about that you go and present a solution that is not documented or supported, and readers of this solution have no way to judge whether or not it is the right thing to use it.

The Q&A section of SAP Community is still the primary place where people working with SAP solutions come to look for guidance and answers to their problems. Putting up a hacky solution like yours without any disclaimers, warnings or explanations makes it look like it were equally suitable and safe to use as the other ones.

If you personally take on advice or not - nothing I can do about that. But calling out a hack and provide some background information to other readers: totally within my options.


Alright man :) after i saw your reaction also for that blog : i know your allergy for rowid now and respect as well, I will put a special note for my solution above.

Srinivas Kakarla Oct 02, 2017 at 02:26 PM


I regularly scan few SAP HANA system tables like M_BACKUP_CATALOG for top (latest) entries in the table using below SQL. It should work for DELETE as well:

select TOP 2 ENTRY_ID, SYS_START_TIME, SYS_END_TIME, STATE_NAME from M_BACKUP_CATALOG where entry_type_name like 'complete%' order by sys_start_time desc

Modify the query to meet the conditions you are looking for and give it a try. Please remember to take backup before you try anything :)


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hello Srinivas,

unfortunately, it is not that easy. There is no "DELETE TOP 2 FROM...." in HANA.

The option mentioned by Joyti was already proposed recently and seems to be a valid one.

BR, Bojan