SUBSELECT QUESTION?

2003-10-24 Thread Prashant Pai 
Hi,

I have a table SalaryInfo as below

Salary | Department | Level
50 | RD| Director
3  | Maintenance| Groundsman
...

I want to know what level in each department makes the highest salary
and how much that salary is? Something like:

SELECT Salary, Level, Department FROM SalaryInfo WHERE
Salary=Max(Salary)

Would using MySQL 4.1 that has support for nested select help my case?

Thanks in advance
prashant

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

Re: SUBSELECT QUESTION?

2003-10-24 Thread Mojtaba Faridzad 
First of all, you query is not correct and this is not a subselect query.
you can try this:

SELECT Salary, Level, Department FROM SalaryInfo ORDER BY Salary DESC LIMIT
1;

second of all, for subselect MySQL 4.1 can help you.

Mojtaba

- Original Message - 
From: Prashant Pai [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 24, 2003 12:00 PM
Subject: SUBSELECT QUESTION?


 Hi,

 I have a table SalaryInfo as below

 Salary | Department | Level
 50 | RD| Director
 3  | Maintenance| Groundsman
 ...

 I want to know what level in each department makes the highest salary
 and how much that salary is? Something like:

 SELECT Salary, Level, Department FROM SalaryInfo WHERE
 Salary=Max(Salary)

 Would using MySQL 4.1 that has support for nested select help my case?

 Thanks in advance
 prashant

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



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

Re: subselect question... shouldn't this work?

2003-07-17 Thread Victoria Reznichenko 
Ben Margolin [EMAIL PROTECTED] wrote:
 
 I am new to subselects, and what I really want is to do them in an update,
 but the following shows a simpler select, that also doesn't seem to work as I
 think it should. Advice? Do I just misunderstand how subselects are actually
 executed?
 
 (This is on mysql version 4.1.0-alpha-max-nt.)
 
 First, here's the tables in question:
 
 mysql describe m;
 +---+-+---+--+-+-+---+
 | Field | Type| Collation | Null | Key | Default | Extra |
 +---+-+---+--+-+-+---+
 | toid  | int(11) | binary| YES  | | NULL|   |
 | rd| int(11) | binary| YES  | | NULL|   |
 +---+-+---+--+-+-+---+
 
 mysql describe p;
 +-+-+---+--+-+-+---+
 | Field   | Type| Collation | Null | Key | Default | Extra |
 +-+-+---+--+-+-+---+
 | userid  | int(11) | binary|  | PRI | 0   |   |
 | pmnew   | int(11) | binary| YES  | | NULL|   |
 | pmtotal | int(11) | binary| YES  | | NULL|   |
 +-+-+---+--+-+-+---+
 
 and the data in the tables...
 
 mysql select * from p;
 ++---+-+
 | userid | pmnew | pmtotal |
 ++---+-+
 |  1 | 0 |   0 |
 |  2 | 0 |   0 |
 ++---+-+
 2 rows in set (0.00 sec)
 
 mysql select * from m;
 +--+---+
 | toid | rd|
 +--+---+
 |1 | 0 |
 |1 | 0 |
 |1 | 0 |
 |1 |12 |
 |1 |15 |
 |1 |   123 |
 |1 | 12312 |
 |1 | 12312 |
 |1 |   123 |
 |2 | 0 |
 |2 | 0 |
 |2 | 1 |
 |2 | 2 |
 +--+---+
 13 rows in set (0.00 sec)
 
 mysql select userid,pmtotal,pmnew, (select count(rd) from m where
 toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
 toid=p.userid) calc_new from p where userid in (select distinct toid from m);
 ++-+---++--+
 | userid | pmtotal | pmnew | calc_total | calc_new |
 ++-+---++--+
 |  1 |   0 | 0 |  9 |3 |
 |  2 |   0 | 0 |   NULL | NULL |
 ++-+---++--+
 Now, the first row has what I want and expect, in calc_total and calc_new...
 but the second row doesn't. Why? Shouldn't the subselects in the field
 selector part (not the where part) be re-executed for each value in the
 IN()? If so, I'd expect calc_total to be '4', and calc_new to be '2' for the
 second row. For example, if I manually fudge the WHERE ... IN, I get:
 
 mysql select userid,pmtotal,pmnew, (select count(rd) from m where
 toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
 toid=p.userid) calc_new from p where userid in (2);
 ++-+---++--+
 | userid | pmtotal | pmnew | calc_total | calc_new |
 ++-+---++--+
 |  2 |   0 | 0 |  4 |2 |
 ++-+---++--+
 
 which is exactly what I want, but all at once :-)
 
 Ideas? Misunderstanding on my part? Bug?
 
 (By the way, what I eventually want to do is an update to set pmtotal and
 pmnew to be the calc_total and calc_new; in the real schema this is a
 simplified version of, they are essentially 'caches' of the new/total
 counts...)

Thanks for the report. I added your report to the MySQL bug database:
http://bugs.mysql.com/bug.php?id=860


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





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

subselect question... shouldn't this work?

2003-07-16 Thread Ben Margolin 

I am new to subselects, and what I really want is to do them in an update,
but the following shows a simpler select, that also doesn't seem to work as I
think it should. Advice? Do I just misunderstand how subselects are actually
executed?

(This is on mysql version 4.1.0-alpha-max-nt.)

First, here's the tables in question:

mysql describe m;
+---+-+---+--+-+-+---+
| Field | Type| Collation | Null | Key | Default | Extra |
+---+-+---+--+-+-+---+
| toid  | int(11) | binary| YES  | | NULL|   |
| rd| int(11) | binary| YES  | | NULL|   |
+---+-+---+--+-+-+---+

mysql describe p;
+-+-+---+--+-+-+---+
| Field   | Type| Collation | Null | Key | Default | Extra |
+-+-+---+--+-+-+---+
| userid  | int(11) | binary|  | PRI | 0   |   |
| pmnew   | int(11) | binary| YES  | | NULL|   |
| pmtotal | int(11) | binary| YES  | | NULL|   |
+-+-+---+--+-+-+---+

and the data in the tables...

mysql select * from p;
++---+-+
| userid | pmnew | pmtotal |
++---+-+
|  1 | 0 |   0 |
|  2 | 0 |   0 |
++---+-+
2 rows in set (0.00 sec)

mysql select * from m;
+--+---+
| toid | rd|
+--+---+
|1 | 0 |
|1 | 0 |
|1 | 0 |
|1 |12 |
|1 |15 |
|1 |   123 |
|1 | 12312 |
|1 | 12312 |
|1 |   123 |
|2 | 0 |
|2 | 0 |
|2 | 1 |
|2 | 2 |
+--+---+
13 rows in set (0.00 sec)

mysql select userid,pmtotal,pmnew, (select count(rd) from m where
toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
toid=p.userid) calc_new from p where userid in (select distinct toid from m);
++-+---++--+
| userid | pmtotal | pmnew | calc_total | calc_new |
++-+---++--+
|  1 |   0 | 0 |  9 |3 |
|  2 |   0 | 0 |   NULL | NULL |
++-+---++--+
Now, the first row has what I want and expect, in calc_total and calc_new...
but the second row doesn't. Why? Shouldn't the subselects in the field
selector part (not the where part) be re-executed for each value in the
IN()? If so, I'd expect calc_total to be '4', and calc_new to be '2' for the
second row. For example, if I manually fudge the WHERE ... IN, I get:

mysql select userid,pmtotal,pmnew, (select count(rd) from m where
toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
toid=p.userid) calc_new from p where userid in (2);
++-+---++--+
| userid | pmtotal | pmnew | calc_total | calc_new |
++-+---++--+
|  2 |   0 | 0 |  4 |2 |
++-+---++--+

which is exactly what I want, but all at once :-)

Ideas? Misunderstanding on my part? Bug?

(By the way, what I eventually want to do is an update to set pmtotal and
pmnew to be the calc_total and calc_new; in the real schema this is a
simplified version of, they are essentially 'caches' of the new/total
counts...)

Any comments appreciated.

Ben Margolin


=
[ Ben Margolin -- [EMAIL PROTECTED] -- [EMAIL PROTECTED]  ]

__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

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

RE: subselect question... shouldn't this work?

2003-07-16 Thread Rudy Metzger 
These are not real subselects. You speak of a subselect when you are
define a select in the FROM clause of a kwiri. If you define them in the
WHERE clause, you speak of derived tables. Putting them even into the
SELECT clause is something specific to MySQL (as far as I know).

Ok, now this is theory and does not help you much :) Thus you can solve
this without subselects:

SELECT userid, pmtotal, pmnew, 
   count(*) calc_total, 
   sum(if(rd=0,1,0) calc_new
  FROM m,
   p
 WHERE userid = toid
 GROUP BY userid

OR ( if you insist on derived tables and subselects )

SELECT userid, pmtotal, pmnew, 
   m2.calc_total calc_total, 
   m1.calc_new calc_new
  FROM m,
   p,
   ( SELECT toid, count(*) calc_new
   FROM m
  WHERE rd = 0
  GROUP BY toid ) m1,
   ( SELECT toid, count(*) calc_total
   FROM m
  GROUP BY toid ) m2
 WHERE userid IN ( SELECT distinct toid
 FROM m )
   AND p.userid = m2.toid
   AND p.userid = m1.toid

But I would use the first one...

/rudy

-Original Message-
From: Ben Margolin [mailto:[EMAIL PROTECTED] 
Sent: woensdag 16 juli 2003 1:36
To: [EMAIL PROTECTED]
Subject: subselect question... shouldn't this work?


I am new to subselects, and what I really want is to do them in an
update,
but the following shows a simpler select, that also doesn't seem to work
as I
think it should. Advice? Do I just misunderstand how subselects are
actually
executed?

(This is on mysql version 4.1.0-alpha-max-nt.)

First, here's the tables in question:

mysql describe m;
+---+-+---+--+-+-+---+
| Field | Type| Collation | Null | Key | Default | Extra |
+---+-+---+--+-+-+---+
| toid  | int(11) | binary| YES  | | NULL|   |
| rd| int(11) | binary| YES  | | NULL|   |
+---+-+---+--+-+-+---+

mysql describe p;
+-+-+---+--+-+-+---+
| Field   | Type| Collation | Null | Key | Default | Extra |
+-+-+---+--+-+-+---+
| userid  | int(11) | binary|  | PRI | 0   |   |
| pmnew   | int(11) | binary| YES  | | NULL|   |
| pmtotal | int(11) | binary| YES  | | NULL|   |
+-+-+---+--+-+-+---+

and the data in the tables...

mysql select * from p;
++---+-+
| userid | pmnew | pmtotal |
++---+-+
|  1 | 0 |   0 |
|  2 | 0 |   0 |
++---+-+
2 rows in set (0.00 sec)

mysql select * from m;
+--+---+
| toid | rd|
+--+---+
|1 | 0 |
|1 | 0 |
|1 | 0 |
|1 |12 |
|1 |15 |
|1 |   123 |
|1 | 12312 |
|1 | 12312 |
|1 |   123 |
|2 | 0 |
|2 | 0 |
|2 | 1 |
|2 | 2 |
+--+---+
13 rows in set (0.00 sec)

mysql select userid,pmtotal,pmnew, (select count(rd) from m where
toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
toid=p.userid) calc_new from p where userid in (select distinct toid
from m);
++-+---++--+
| userid | pmtotal | pmnew | calc_total | calc_new |
++-+---++--+
|  1 |   0 | 0 |  9 |3 |
|  2 |   0 | 0 |   NULL | NULL |
++-+---++--+
Now, the first row has what I want and expect, in calc_total and
calc_new...
but the second row doesn't. Why? Shouldn't the subselects in the field
selector part (not the where part) be re-executed for each value in
the
IN()? If so, I'd expect calc_total to be '4', and calc_new to be '2' for
the
second row. For example, if I manually fudge the WHERE ... IN, I get:

mysql select userid,pmtotal,pmnew, (select count(rd) from m where
toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
toid=p.userid) calc_new from p where userid in (2);
++-+---++--+
| userid | pmtotal | pmnew | calc_total | calc_new |
++-+---++--+
|  2 |   0 | 0 |  4 |2 |
++-+---++--+

which is exactly what I want, but all at once :-)

Ideas? Misunderstanding on my part? Bug?

(By the way, what I eventually want to do is an update to set pmtotal
and
pmnew to be the calc_total and calc_new; in the real schema this is a
simplified version of, they are essentially 'caches' of the new/total
counts...)

Any comments appreciated.

Ben Margolin


=
[ Ben Margolin -- [EMAIL PROTECTED] -- [EMAIL PROTECTED]  ]

__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

-- 
MySQL General Mailing List
For list archives: http