June 11, 2018 at 12:05 pm
>> I don't know the English term for this but, in Spanish, I called this combination, a set of elements where the order doesn't matter. <<
Yep! A permutation has an ordering; a combination has no ordering. This is math, not "human language" 🙂 After that we've got circular permutations, derangements and a few other structures. You can probably pick up a used copy from Amazon of "Permutations and Combinations" by Ramesh Chandra (ISBN 978-93-84391-47-8) cheaply.
>> Let's say I have a hotel with rooms. There are rooms for 2 persons, rooms for 3 persons and rooms for 5 persons. For 2 persons there are 2 rooms and the same for 3 persons (2 rooms). With my CTE I've got the combinations for a group of 10 people. <<
This is a partitioning or a bin packing problem. It's a bit different from combinations and permutations. You'll have to be able to distinguish the guest from each other, and it identify the rooms. This means names and room numbers.
>> Now there's an ordered string string is not ordered I'm not trying to redefine maths or relational database theory, my questions was more simple. If you prefer, forget about comma and separated the rooms by - so the combinations will be 2-3-5 or 3-2-5. In other words, I just need to separate those values, get an ordered string and see if the string it's the same. No. This is generic is yes, you know you're actually trying to do know there's <<
I guess having an advanced degree in mathematics makes it hard for me to understand what you're trying to do because I know the terms mean. And since my second advanced degree is in computer science, I don't understand how you're trying to implement this..
My thought about putting guest into rooms is that you obviously the table of rooms, and a table of guests. The guest identifier in the room number will be the keys of those tables respectively. You can then use CROSS JOIN, INTERSECT, UNION, and EXCEPT operators to manipulate sets. Your code would look something like this:
SELECT room_nbr FROM Hotel H1 WHERE room_nbr IN (..)
INTERSECT
SELECT room_nbr FROM Hotel H2 WHERE room_nbr IN (..)
Remember, SQL is not meant to be a language for this kind of thing. I like using the list option with the IN() predicate, since it removes duplicates automatically and in no way implies an ordering in the list.
But a mathematical, symbolic language might be a better choice.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 11, 2018 at 12:13 pm
Name of guest is not important. Think in a group of 10 friends, they can be in any room, grouped as they want. The only condition is to match the maximum number of persons in a room, that's all.
June 12, 2018 at 1:57 am
Steve, thank you for your solution!! It works fine and, regarding performance, it's also ok. That was exactly what I was looking for.
June 12, 2018 at 2:17 pm
Mauricio_ - Tuesday, June 12, 2018 1:57 AMSteve, thank you for your solution!! It works fine and, regarding performance, it's also ok. That was exactly what I was looking for.
Glad I could help. Hopefully, you won't have to apply that solution to planning for, say, 100 guests, whereby the problem and it's performance characteristics might well jump into the stratosphere.
And to Mr. Celko.... maybe you should just take your advanced degrees and burn them. They don't seem to help you solve actual problems, and you keep using them to bash people over the head whenever they deviate from standards you hypocritically break on a regular basis. And after you do that, just go away. You're far more a hindrance than a help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply