Out on HF and heard nobody, but didn't call CQ? Nobody heard you either. 73 de N7GH
words_list | sorted_words |
---|---|
"ball blue clock" | "ball blue clock" |
"clock blue ball" | "ball blue clock" |
"computer" | "computer" |
"computer macbook" | "computer macbook" |
"radio" | "radio" |
sorted_words | COUNT(*) |
---|---|
"ball blue clock" | 2 |
words_list |
---|
"ball blue clock" |
"clock blue ball" |
No more Blub for me, thank you, Vicar.
chris webster wrote:As Les and Roel pointed out, this is a bit tricky with plain SQL.
One work-around would be to create an extra column sorted_words on your table, and populate that with the sorted list of words from the words_list column. You might also want to make sure all the words are converted to the same case e.g. lower-case.
Brian Tkatch wrote:Note to moderators: Because of the complexity inherent in recursive queries and analytical functions, especially here where there are more than one, and they work together, i do not think i can offer simple suggestions. Therefore, i am providing a solution. If this is not allowed, please edit the post accordingly, or send me suggestions. I will be happy to comply.
Roel De Nijs wrote:But it might be very helpful to the OP (and other ranchers) if you could provide some explanations about the different parts of this complex query. That will probably make it easier to digest and understand such an enormous query
Brian Tkatch wrote:I'll be happy to. Could you give me some pointers on what to explain? Chris Webster provided the idea for the solution, which is what i implemented.
Roel De Nijs wrote:After having a closer look at this massive query myself and trying to understand it myself I think it might be useful to explain a bit about what's the purpose of ROW_NUMBER and PARTITION. I think other functions like INSTR and SUBSTR are self-explanatory based on their names.
And because not everyone has an Oracle environment available to run this query (like me ), it might be useful (if possible) to show (a part of) the result table after executing the different parts of this query. The result of the first step (Data(Words)) is quite obvious. But what's the result if you execute the next step (Separate_The_Words)? And similar for the next steps. I think that might be useful for 2 reasons. First because a concrete example with actual data makes it easier to understand the code. And secondly because if a rancher wants to convert this into a similar query for MySQL or SQL Server, he/she knows the different temporary results which probably makes it easier to troubleshoot as you can verify if your temporary result is similar or completely different.
No more Blub for me, thank you, Vicar.
chris webster wrote:My concern would be around indexing and query performance.
Words |
---|
ball blue clock |
clock blue ball |
computer |
computer macbook |
radio |
WORDS | THE_REST | ONE_WORD |
---|---|---|
ball blue clock | ball blue clock | (NULL) |
ball blue clock | blue clock | ball |
ball blue clock | clock | blue |
ball blue clock | (NULL) | clock |
clock blue ball | clock blue ball | (NULL) |
clock blue ball | blue ball | clock |
clock blue ball | ball | blue |
clock blue ball | (NULL) | ball |
computer | computer | (NULL) |
computer | (NULL) | computer |
computer macbook | computer macbook | (NULL) |
computer macbook | macbook | computer |
computer macbook | (NULL) | macbook |
radio | radio | (NULL) |
radio | (NULL) | radio |
Words | One_Word | Id | Total |
---|---|---|---|
ball blue clock | ball | 1 | 4 |
ball blue clock | blue | 2 | 4 |
ball blue clock | clock | 3 | 4 |
ball blue clock | 4 | 4 | |
clock blue ball | ball | 1 | 4 |
clock blue ball | blue | 2 | 4 |
clock blue ball | clock | 3 | 4 |
clock blue ball | 4 | 4 | |
computer | computer | 1 | 2 |
computer | 2 | 2 | |
computer macbook | computer | 1 | 3 |
computer macbook | macbook | 2 | 3 |
computer macbook | 3 | 3 | |
radio | radio | 1 | 2 |
radio | 2 | 2 |
Words | Id | Total | Concatenation |
---|---|---|---|
ball blue clock | 1 | 4 | ball |
ball blue clock | 2 | 4 | ball blue |
ball blue clock | 3 | 4 | ball blue clock |
ball blue clock | 4 | 4 | ball blue clock |
clock blue ball | 1 | 4 | ball |
clock blue ball | 2 | 4 | ball blue |
clock blue ball | 3 | 4 | ball blue clock |
clock blue ball | 4 | 4 | ball blue clock |
computer | 1 | 2 | computer |
computer | 2 | 2 | computer |
computer macbook | 1 | 3 | computer |
computer macbook | 2 | 3 | computer macbook |
computer macbook | 3 | 3 | computer macbook |
radio | 1 | 2 | radio |
radio | 2 | 2 | radio |
Words | Concatenation | Duplicates |
---|---|---|
ball blue clock | ball blue clock | 2 |
clock blue ball | ball blue clock | 2 |
computer | computer | 1 |
computer macbook | computer macbook | 1 |
radio | radio | 1 |
Words |
---|
clock blue ball |
ball blue clock |
Brian Tkatch wrote:FWIW, the first draft of this reply was saved by my browser at 9:30. It's now 11:20. That's almost 2 hours. You better appreciate this!
Roel De Nijs wrote:it's very similar to GROUP BY but where GROUP BY will (normally) reduce the number of returned records, PARTITION BY doesn't.
Roel De Nijs wrote:So I awarded you with 5 cows and 2 pies. Take good care of your new cattle and enjoy the pie! And this topic might appear in the February edition of the CodeRanch journal as well.
Dave Tolls wrote:You are Tom Kyte
Dave Tolls wrote:and I claim my 5 pounds!
No more Blub for me, thank you, Vicar.
chris webster wrote:This exactly the kind of thing that Tom Kyte was referring to in one of his Oracle books a few years ago, where he basically recommended the following approach to solving data problems on Oracle:
1. Do it in SQL if you can.
2. If you can't do it in SQL, do it in PL/SQL if you can.
3. If you really can't do it in SQL or PL/SQL, then try C or Java etc.
Brian Tkatch wrote:Yes. Except, that's a terrible way to look at it. You're example is correct, but let me give a quick explanation.
original_words | invidual_words |
---|---|
ball blue clock | ball |
ball blue clock | blue |
ball blue clock | clock |
clock blue ball | ball |
clock blue ball | blue |
clock blue ball | clock |
computer | computer |
computer macbook | computer |
computer macbook | macbook |
radio | radio |
original_words | sorted_words |
---|---|
ball blue clock | ball blue clock |
clock blue ball | ball blue clock |
computer | computer |
computer macbook | computer macbook |
radio | radio |
original_words | sorted_words |
---|---|
ball blue clock | ball blue clock |
clock blue ball | ball blue clock |
Don't get me started about those stupid light bulbs. |