This is exactly the kind of comment I've been getting from everyone here.
No one can help the simple guy get a simple answer

Mayte skrev:

G'day Mayte,

Thanks for a bit of trolling, I got some laughs out of it :)

I just wonder who you are, you're not CELKO, are you, trying to be on
the other side of the fence?

    /impslayer, nope, I couldn't figure out your query either ;)

Nope.  There are enough clueless newbies that nobody has to impersonate
them to try and get some kind of balance.

Mayte:

SQL and relational databases simply aren't designed for these types of
queries on these types of tables.  So let's see just how we might
alternately structure your data, then see what we can do to give you the
answers you're looking for.

Here's an example of the table you gave.

        Bob     Mike    Sally
Lamp   10      30      40
Chair  20      50      90
Sofa   19      42      73
Bed    10      19      400

If I were doing the design of this database, I would structure it like
this instead:

Item   Name Bid
----   ---- ---
Lamp    Bob  10
Chair   Bob  20
Sofa    Bob  19
Bed     Bob  10
Lamp   Mike  30
Chair  Mike  50
Sofa   Mike  42
Bed    Mike  19
Lamp  Sally  40
Chair Sally  90
Sofa  Sally  73
Bed   Sally 400

Hmm.  If it were structured this way, it would be really easy to write
some SQL like this:

SELECT TOP 1 Name, Bid
FROM table
ORDER BY Bid DESC

or

SELECT TOP 1 WITH TIES Name, Bid
FROM table
ORDER BY Bid DESC

So let's write some SQL to transform your table structure into a more
proper structure (you might want to consider adding this as a view)...

SELECT Item, 'Bob' AS Name, Bob AS Bid
UNION ALL
SELECT Item, 'Mike' AS Name, Mike AS Bid
UNION ALL
SELECT Item, 'Sally' AS Name, Sally AS Bid

with this, we can now write:

SELECT TOP 1 WITH TIES Name, Bid
FROM (
    SELECT Item, 'Bob' AS Name, Bob AS Bid
    UNION ALL
    SELECT Item, 'Mike' AS Name, Mike AS Bid
    UNION ALL
    SELECT Item, 'Sally' AS Name, Sally AS Bid)

and you have your answer.


NOW THEN....

Do you notice how much more involved this query is because of your table
design?  Do you notice how much simpler it would have been had you
simply designed it as in the second example there?  Of course, you see
that it's going to be quite difficult to add a new bidder, and you will
have to redefine all of your queries.  Now then, you say that you will
not want to do this.  Perhaps.  But I'll bet that there is someone who
will come along after you are surely fired (a table design like this
would constitute "for cause" if you were working for me), and have to
add a new bidder, or add a new property about a bid, say, a comment, or
a bid increment, or whatever.  In a proper schema, this involves adding
a single column for each new property of a bid.  In your design, you
will have to add three columns for each additional property.  See where
this is going wrong?  See how you solution might not be optimal?

Also, if you get to know the query optimizer just a bit, you'll realize
that queries on tables such as yours are going to be incredibly slow
compared to queries executed on properly designed tables.  Don't believe
me?  Try it out in Query Analyzer or SQL Server Management studio, and
take a look at the query plans.

In addition, I would strongly recommend corresponding with the people on
this newsgroup (in fact, any newsgroup) with a bit of common courtesy.
In fact, you should read some of the messages on this group other than
those in your own thread, get to realize that others have a bit more
experience than you, realize who these people are, and treat them with
the respect they deserve.

-Dave

Mayte wrote:
Show quoteHide quote

arg. Not again.

There will be no new bidders.
These were just made-up column names, in order to hide corporate secrets.

Why can't any of you think out of the box.
Please see my alternative example of column names.

Count Occurances in a column
Who has the highest bid?
Need Help Please...
How to compare strings for equality in Transact SQL
Unique index on joined view for fulltext index