While reading this wikipedia article, Brookes has told that there is a difference between "good" designers and "great" designers. What is the difference between them? How can I decide if a designer is good or great?
A good designer can design for simplicity or flexibility or efficiency or robustness. A great designer has a deep understanding of the tradeoffs involved and can effectively balance all of these and come up with a solution that satisfies all of them reasonably well.
A great designer catches the intent of what I want to accomplish and improves on it to my delight. A good designer follows a specification to the letter, but creativity shows in how they did that.
A bad designer is, well, none of the above.
I think 'exceptional' is always in the eye of the client, and the people they hope to reach.
Other things come into play in the 'great' category, such as someone who can articulate why my 'vision' of something might be skewed. I guess it boils down, mostly to how much ownership someone takes in any given project, given a group of people with similar skills.
Great, is also, entirely subjective.
I'm going to assume you're talking about Software Design as opposed to the narrower Ux design.
I'll quote a semi-famous passage from Alan Perlis:
"Fools ignore complexity. Pragmatists suffer it. Some can avoid it. Geniuses remove it."
One aspect of a Great designer is that they reduce complexity.
After The Mythical Man Month came out, Fred Brooks released another collection of essays under the title The Design of Design. There's a lot of information in this book about how to design, the design process and methodologies, what makes a good design, methods of collaboration in designing. There are also case studies that range from designing a beach hours to the IBM Operating System/360.
A few passages stand out as being especially relevant to your question:
A chief service of a designer is helping clients discover what they want designed.
There's also an entire essay devoted to where great designers come from. This mentions teaching design in addition to performing prescribed experiments or tasks, co-op programs to provide real-world, practical experience as part of education, managing them to prevent distractions or managers who do things that stifle creativity, and continued study of things considered to be good designs or good practice.
As @Caleb notes in the comments, The Design of Design was published in 2010. No Silver Bullet was written in 1986, and The Mythical Man Month was published in 1975, updated in 1982, and republished with extra essays in 1995 (which is when the No Silver Bullet and commentary on it was added to the book). So there is almost a quarter century between the essay you are referring to and the release of the book that contains the essays that I'm citing.
It should also be noted that The Design of Design, as far as I can tell, doesn't identify the date of writing of any of the individual essays, although some do cite works as late as 2008 and 2009 as resources/references. I'd have to do further research to find out when "What's Wrong With This Model?" and "Where Do Great Designers Come From?" were written.
A good designer creates something that does the job well. A great designer also makes the user say "wow!!!".
A good designer knows the established standards, and applies them well. A great designer goes beyond that and creates new solutions that become new standards.
If you want to know what Fred Brooks was saying in No Silver Bullet you should read the paper itself and not a Wikipedia article about the paper. Brooks isn't specific as to the the precise difference but he nevertheless makes it clear that the distinction will be pretty easy to spot:
The differences are not minor--they are rather like the differences between Salieri and Mozart. Study after study shows that the very best designers produce structures that are faster, smaller, simpler, cleaner, and produced with less effort... The differences between the great and the average approach an order of magnitude.
Depends on what kind of design. For web page designers a good designer can make something that looks good but a great designer can make something that looks good and works well (ie intuitive and easy to use).
A good designer writes code that works.
A great designer writes code that good designers borrow.
Great designers can make 2 + 2 = 5. They change the art of the possible.