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.
  1. Prepare
  2. SQL
  3. Alternative Queries
  4. Print Prime Numbers
  5. Discussions

Print Prime Numbers

  • asokratis
     + 4 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

    1. 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?)

    2. 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)

    3. 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:

    SET QUOTED_IDENTIFIER ON 
GO 
PUT_YOUR_QUERY_HERE 
GO

    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:

    SELECT CAST(X) AS VARCHAR(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 Server VS listagg of Oracle.