We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
I guess you guys are here for hints, but I will not go technical, but give some general pointers to you guys, especially for MS SQL users, if you are stuck somewhere in the rubble. I am pretty sure giving those hints will not be for the faint of heart for many who don't have prior experience of sql in the workplace so I am thinking I am not giving free points as much giving some professor layton hints.
In General to the Requirements
I was able to solve this with MS SQL Server with recursive CTE expressions (if you did the triangles with CTE expressions, then you can connect the dots here - no Postresql here - who doesn't love generate_series function?)
I suggest you find first the non prime list to identify later the prime list. This is the source I used how to identify if a number is a non prime number: http://www.counton.org/explorer/primes/checking-if-a-number-is-prime/ That will be more enough to get you going, you don't need to take the shortcuts, brute force will be enough, unless the exercise asks you for a list of billion prime numbers that is, then yeah, please use the shortcuts (I didn't as the query was already fast)
Note the source of the site says that a "positive" non prime number result needs to not have decimals, then using some ceiling or floor for a division will be equal with the same divison without using ceiling and floor (and be careful how you do the division cause int/int = int, as most database engines do not opt to a decimal)
MS SQL Server Troubleshooting
Now the bigger conundrums is the intricacies that you have to deal with MS SQL Server. As opposed to Oracle having its own easy function called listagg, here we have the monstrosity of using a thing that was not intended originally to be used as part of concatenating values a.k.a. "for xml path('')".
** It is not fun when intentionally the requirements asks the delimeter to be an ampersand character, that by default, in XML tries to translate it with escape character, but as an actual output result, we don't want it to show it like that.
** There is a trick to remove those escape characters without string manipulation (using replace will solve the problem, but is not robust, as you only solve one use case out of the many use cases of an escape character that "could" exist), but you will get an error out of that. You will need instead to put your query in the following syntax instead:
SETQUOTED_IDENTIFIERONGOPUT_YOUR_QUERY_HEREGO
Another intricacy is the output is limited to x characters. In order to solve that (as you need more x characters as output to solve the problem) is to convert it to varchar(8000). Maybe thats me, but that solved the problem for me when solving the problem above:
SELECTCAST(X)ASVARCHAR(8000)
Conclusion
The interesting thing I have to say is that I see here a lot of comments that look the typical imperative language to solve the solution when in fact I only did it with tables. Only 2 CTE (1 recursive - 1 non recursive) and 1 select statement was my solution. I could had it compact as 1 recursive CTE with one select statement, but I want to keep the select statement very simple and clean, so I didn't do that.
I know also that most people use mysql and there is no such things as recursive cte, so I can hear your pain anyways, in the same way, the pain of using xml path() in MS SQL ServerVSlistagg of Oracle.
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Print Prime Numbers
You are viewing a single comment's thread. Return to all comments →
Hints
I guess you guys are here for hints, but I will not go technical, but give some general pointers to you guys, especially for
MS SQL
users, if you are stuck somewhere in the rubble. I am pretty sure giving those hints will not be for the faint of heart for many who don't have prior experience of sql in the workplace so I am thinking I am not giving free points as much giving some professor layton hints.In General to the Requirements
I was able to solve this with
MS SQL Server
with recursive CTE expressions (if you did the triangles with CTE expressions, then you can connect the dots here - noPostresql
here - who doesn't lovegenerate_series
function?)I suggest you find first the non prime list to identify later the prime list. This is the source I used how to identify if a number is a non prime number: http://www.counton.org/explorer/primes/checking-if-a-number-is-prime/ That will be more enough to get you going, you don't need to take the shortcuts, brute force will be enough, unless the exercise asks you for a list of billion prime numbers that is, then yeah, please use the shortcuts (I didn't as the query was already fast)
Note the source of the site says that a "positive" non prime number result needs to not have decimals, then using some
ceiling
orfloor
for a division will be equal with the same divison without using ceiling and floor (and be careful how you do the division causeint/int = int
, as most database engines do not opt to adecimal
)MS SQL Server Troubleshooting
MS SQL Server
. As opposed toOracle
having its own easy function calledlistagg
, here we have the monstrosity of using a thing that was not intended originally to be used as part of concatenating values a.k.a. "for xml path('')".** It is not fun when intentionally the requirements asks the delimeter to be an ampersand character, that by default, in XML tries to translate it with escape character, but as an actual output result, we don't want it to show it like that.
** There is a trick to remove those escape characters without string manipulation (using
replace
will solve the problem, but is not robust, as you only solve one use case out of the many use cases of an escape character that "could" exist), but you will get an error out of that. You will need instead to put your query in the following syntax instead:Another intricacy is the output is limited to x characters. In order to solve that (as you need more x characters as output to solve the problem) is to convert it to
varchar(8000)
. Maybe thats me, but that solved the problem for me when solving the problem above:Conclusion
The interesting thing I have to say is that I see here a lot of comments that look the typical imperative language to solve the solution when in fact I only did it with tables. Only 2 CTE (1 recursive - 1 non recursive) and 1 select statement was my solution. I could had it compact as 1 recursive CTE with one select statement, but I want to keep the select statement very simple and clean, so I didn't do that.
I know also that most people use
mysql
and there is no such things as recursive cte, so I can hear your pain anyways, in the same way, the pain of usingxml path()
inMS SQL Server
VSlistagg
ofOracle
.