Can we use ORDER BY clause in a CTE expression? Error message: Msg 1033, Level 15, State 1, Line 14 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. Msg 102, Level 15, State 1, Line 25 Incorrec…
Tag: sql
column “parent_id” referenced in foreign key constraint does not exist when creating SQL table
I am new in SQL and trying to understand Foreign key syntax. I know this was asked in multiple questions but each question I found did not seem to teach me what I am doing wrong here. This is my SQL code: And this is the error I am getting: Answer You need to add fields in your tables to
What’s the default window frame for window functions
Running the following code: The result is: There is no window frame defined in the above code, it looks the default window frame is rowsBetween(Window.unboundedPreceding, Window.currentRow) Not sure my understanding about default window frame is correct Answer From Spark Gotchas Default frame specification de…
Heap big table with performance issues
I have a business case where I need to store all details about invoices from vehicle workshop (invoice number, dealer,vehicle number, peace, nature of service , …). I have more than 50 columns which are all selected in the same query. In my treatment I need to do some specific treatment in order to get …
How to create Audit history table table [closed]
Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 5 years ago. Improve this question I have a stored procedure which update records. Can anyone help me how to …
Adventure Works 2014 SQL Queries
I am writing a SQL query using Adventure Works 2014 database. I want to show all customers and how many orders each customers have. I tried to write each select statement by itself (see below), but I’d like to be able to combine both queries into one. Answer
States where the number of suppliers exceeds the number of consumers
I’m working on trying to get states where the number of suppliers is greater than the number of consumers in those states. Here is the code I’m tryin to get working: When I exclude the having clause I get the following results: The code is giving me no results. Also here is a snapshot of the datab…
Foreign key relationship with composite primary keys in MySQL
Which is the best way to create a relationship between two tables when referenced table has a composite primary key? Answer One way is this As Gordon said, the best option is create an auto incremental ID and make this the primary key.
sql query : show name with all vowels
Equatorial Guinea and Dominican Republic have all of the vowels (a, e, i, o, u) in the name. They don’t count because they have more than one word in the name. You can use the phrase name NOT LIKE ‘%a%’ to exclude characters from your results. The query shown misses countries like Bahamas an…
SQL – Distribution Count
Hi I have the following table: I would like to show the following: Basically I want to count the number crm_ids that have 1,2,3,4,5+ customer_ids. Thanks Answer One approach is to aggregate twice. First, aggregate over crm_id and generate counts. Then, aggregate over those counts themselves and generate a cou…