Re: problem with selecting my max bid ..

[Gregory Machin]

Thanks for the further input, It works perfectly ...
If you wouldn't mind please explain, the use  of the b1 and b2 are they
intended as varaibles ..


On 3/17/06, Michael Stassen <[EMAIL PROTECTED]> wrote:
>
> Gregory Machin wrote:
> > Ok I tried the following
> > SELECT dealer_id, auto_id, bid_amount FROM bids WHERE bid_amount=(SELECT
> > MAX(bid_amount) FROM bids WHERE auto_dealer_id='3');
> > which gives
> > +---+-++
> > | dealer_id | auto_id | bid_amount |
> > +---+-++
> > | 3 |  12 |  9 |
> > +---+-++
> > 1 row in set (0.00 sec)
> >
> > wich is the max bid overall, what I want is the max bid for each auto_id
> ...
>
> No, it's the max bid received by auto_dealer number 3.  It's a coincidence
> if
> that's also the max bid overall.
>
> > How would I go about this ?
>
> By following the example in the link I sent.
>
>SELECT dealer_id, auto_id, bid_amount
>FROM bids b1
>WHERE bid_amount=(SELECT MAX(b2.bid_amount)
>  FROM bids b2
>  WHERE b1.auto_id = b2.auto_id);
>
> You see?  Rows are selected if they have the max bid of all rows with the
> same
> auto_id.
>
> You keep saying you want the max bid per auto_id, but your examples always
> include restrictions on auto_dealer_id.  That's fine, but it's a separate
> issue.
>   You can just add any additional restrictions to the main query's WHERE
> clause:
>
>SELECT dealer_id, auto_id, bid_amount
>FROM bids b1
>WHERE bid_amount=(SELECT MAX(b2.bid_amount)
>  FROM bids b2
>  WHERE b1.auto_id = b2.auto_id)
>  AND auto_dealer_id = '3' AND Bid_Status = '1';
>
> Michael
>



--
Gregory Machin
[EMAIL PROTECTED]
[EMAIL PROTECTED]
www.linuxpro.co.za
www.exponent.co.za
Web Hosting Solutions
Scalable Linux Solutions
www.iberry.info (support and admin)

+27 72 524 8096

Re: problem with selecting my max bid ..

[Michael Stassen]

Gregory Machin wrote:

Ok I tried the following
SELECT dealer_id, auto_id, bid_amount FROM bids WHERE bid_amount=(SELECT
MAX(bid_amount) FROM bids WHERE auto_dealer_id='3');
which gives
+---+-++
| dealer_id | auto_id | bid_amount |
+---+-++
| 3 |  12 |  9 |
+---+-++
1 row in set (0.00 sec)

wich is the max bid overall, what I want is the max bid for each auto_id ...


No, it's the max bid received by auto_dealer number 3.  It's a coincidence if 
that's also the max bid overall.



How would I go about this ?


By following the example in the link I sent.

  SELECT dealer_id, auto_id, bid_amount
  FROM bids b1
  WHERE bid_amount=(SELECT MAX(b2.bid_amount)
FROM bids b2
WHERE b1.auto_id = b2.auto_id);

You see?  Rows are selected if they have the max bid of all rows with the same 
auto_id.


You keep saying you want the max bid per auto_id, but your examples always 
include restrictions on auto_dealer_id.  That's fine, but it's a separate issue. 
 You can just add any additional restrictions to the main query's WHERE clause:


  SELECT dealer_id, auto_id, bid_amount
  FROM bids b1
  WHERE bid_amount=(SELECT MAX(b2.bid_amount)
FROM bids b2
WHERE b1.auto_id = b2.auto_id)
AND auto_dealer_id = '3' AND Bid_Status = '1';

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: problem with selecting my max bid ..

[Gregory Machin]

Ok I tried the following
SELECT dealer_id, auto_id, bid_amount FROM bids WHERE bid_amount=(SELECT
MAX(bid_amount) FROM bids WHERE auto_dealer_id='3');
which gives
+---+-++
| dealer_id | auto_id | bid_amount |
+---+-++
| 3 |  12 |  9 |
+---+-++
1 row in set (0.00 sec)

wich is the max bid overall, what I want is the max bid for each auto_id ...

How would I go about this ?

On 3/17/06, Michael Stassen <[EMAIL PROTECTED]> wrote:
>
> Gregory Machin wrote:
> > Hi.
> > I have the following table
> >
> > | bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp
> |
> > Bid_Status |
> >
> +-+-+--++-++-+
> > |   1 |3 | 3 |12 |
> 2
> > | NULL |  1 |
> > |   2 |3 | 3 |12 |
> 3
> > | NULL |  1 |
> > |   3 |  24 | 3 |12 |
> 4
> > | NULL |  1 |
> > |   4 |  24 | 3 |12 |
> 5
> > | NULL |  1 |
> > |   5 |  24 | 3 |12 |
> 6
> > | NULL |  1 |
> > |   6 |3 |   24 |14 |
> 4
> > | NULL |  1 |
> > |   7 |3 | 3 |13 |
> 4
> > | NULL |  1 |
> > |   8 |  24 | 3 | 12
> |7
> > | NULL |  1 |
> > |   9 |  24 | 3 |13 |
> 59000
> > | NULL |  1 |
> > | 10 |  24 | 3 |12 |
> 8
> > | NULL |  1 |
> > | 11 |  24 | 3 |13 |
> 6
> > | NULL |  1 |
> >
> >
> > where auto_dealer_id is the dealer who put the car on auction ,
> > auto_id is the id of the car on auction,
> > bid_amount is the amount did on the car but dealer_id
> >
> >
> > what I want is to get the max bid placed for each car and the dealer_id
> who
> > placed it.
> > I currnetly have the following:
> > mysql> SELECT dealer_id, auto_id, bid_id ,MAX(bid_amount) AS
> 'bid_amount'
> > FROM bids  WHERE auto_dealer_id = '3' AND Bid_Status = '1' GROUP BY
> auto_id;
> > +---+-+++
> > | dealer_id | auto_id | bid_id | bid_amount |
> > +---+-+++
> > | 3 |  12 |  1 |  8 |
> > | 3 |  13 |  7 |  6 |
> > +---+-+++
> >
> > But this output is wrong because if you refer to the source table above
> the
> > dealer_id should be 24 in both cases..
> > What have I missed ?
> >
> > Many Thanks
> > Gregory Machin
>
> GROUP BY does not return rows.  It returns group names and aggregate
> stats.  You
> are grouping on auto_id, so you get random (actually, the first found)
> values
> for dealer_id and bid_id for each group.  Many systems wouldn't even allow
> this
> query.  Mysql does allow you to select columns not present in the GROUP BY
> clause as a convenience, but you are warned only to use columns with
> unique
> values per group
> .
>
> Yours is a FAQ, however, with 3 solutions in the manual
> <
> http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
> >.
>
> Michael
>



--
Gregory Machin
[EMAIL PROTECTED]
[EMAIL PROTECTED]
www.linuxpro.co.za
www.exponent.co.za
Web Hosting Solutions
Scalable Linux Solutions
www.iberry.info (support and admin)

+27 72 524 8096

Re: problem with selecting my max bid ..

[Michael Stassen]

Gregory Machin wrote:

Hi.
I have the following table

| bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp |
Bid_Status |
+-+-+--++-++-+
|   1 |3 | 3 |12 | 2
| NULL |  1 |
|   2 |3 | 3 |12 | 3
| NULL |  1 |
|   3 |  24 | 3 |12 | 4
| NULL |  1 |
|   4 |  24 | 3 |12 | 5
| NULL |  1 |
|   5 |  24 | 3 |12 | 6
| NULL |  1 |
|   6 |3 |   24 |14 | 4
| NULL |  1 |
|   7 |3 | 3 |13 | 4
| NULL |  1 |
|   8 |  24 | 3 | 12 |7
| NULL |  1 |
|   9 |  24 | 3 |13 | 59000
| NULL |  1 |
| 10 |  24 | 3 |12 | 8
| NULL |  1 |
| 11 |  24 | 3 |13 | 6
| NULL |  1 |


where auto_dealer_id is the dealer who put the car on auction ,
auto_id is the id of the car on auction,
bid_amount is the amount did on the car but dealer_id


what I want is to get the max bid placed for each car and the dealer_id who
placed it.
I currnetly have the following:
mysql> SELECT dealer_id, auto_id, bid_id ,MAX(bid_amount) AS 'bid_amount'
FROM bids  WHERE auto_dealer_id = '3' AND Bid_Status = '1' GROUP BY auto_id;
+---+-+++
| dealer_id | auto_id | bid_id | bid_amount |
+---+-+++
| 3 |  12 |  1 |  8 |
| 3 |  13 |  7 |  6 |
+---+-+++

But this output is wrong because if you refer to the source table above the
dealer_id should be 24 in both cases..
What have I missed ?

Many Thanks
Gregory Machin


GROUP BY does not return rows.  It returns group names and aggregate stats.  You 
are grouping on auto_id, so you get random (actually, the first found) values 
for dealer_id and bid_id for each group.  Many systems wouldn't even allow this 
query.  Mysql does allow you to select columns not present in the GROUP BY 
clause as a convenience, but you are warned only to use columns with unique 
values per group 
.


Yours is a FAQ, however, with 3 solutions in the manual 
.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

problem with selecting my max bid ..

[Gregory Machin]

Hi.
I have the following table

| bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp |
Bid_Status |
+-+-+--++-++-+
|   1 |3 | 3 |12 | 2
| NULL |  1 |
|   2 |3 | 3 |12 | 3
| NULL |  1 |
|   3 |  24 | 3 |12 | 4
| NULL |  1 |
|   4 |  24 | 3 |12 | 5
| NULL |  1 |
|   5 |  24 | 3 |12 | 6
| NULL |  1 |
|   6 |3 |   24 |14 | 4
| NULL |  1 |
|   7 |3 | 3 |13 | 4
| NULL |  1 |
|   8 |  24 | 3 | 12 |7
| NULL |  1 |
|   9 |  24 | 3 |13 | 59000
| NULL |  1 |
| 10 |  24 | 3 |12 | 8
| NULL |  1 |
| 11 |  24 | 3 |13 | 6
| NULL |  1 |


where auto_dealer_id is the dealer who put the car on auction ,
auto_id is the id of the car on auction,
bid_amount is the amount did on the car but dealer_id


what I want is to get the max bid placed for each car and the dealer_id who
placed it.
I currnetly have the following:
mysql> SELECT dealer_id, auto_id, bid_id ,MAX(bid_amount) AS 'bid_amount'
FROM bids  WHERE auto_dealer_id = '3' AND Bid_Status = '1' GROUP BY auto_id;
+---+-+++
| dealer_id | auto_id | bid_id | bid_amount |
+---+-+++
| 3 |  12 |  1 |  8 |
| 3 |  13 |  7 |  6 |
+---+-+++

But this output is wrong because if you refer to the soucrce table above the
dealer_id should be 24 in both cases..
What have I missed ?

Many Thanks
Gregory Machin







--
Gregory Machin
[EMAIL PROTECTED]
[EMAIL PROTECTED]
www.linuxpro.co.za
www.exponent.co.za
Web Hosting Solutions
Scalable Linux Solutions
www.iberry.info (support and admin)

+27 72 524 8096