Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my MYSQL Database COMPANY, I have a Table: Worker :

mysql> DESC `Worker`;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| SSN    | varchar(64) | NO   |     | NULL    |       |
| name   | varchar(64) | YES  |     | NULL    |       |
| salary | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)  

I created using following MySQL's command:

CREATE TABLE `Worker` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;  

The table having following data:

mysql> SELECT * FROM `Worker` ORDER BY salary ASC;
+-----+---------+--------+
| SSN | name    | salary |
+-----+---------+--------+
| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |
| 2   | Rahul   |    300 |
| 4   | Harsh   |    500 |
| 7   | THREE   |   1000 |
+-----+---------+--------+
7 rows in set (0.00 sec)  

its insert query:

INSERT INTO Worker(SSN, name, salary) VALUES ("1", "Grijesh", 200), ('2','Rahul',300),('3','Sumit',250),('4','Harsh',500),('5','ONE','100'),('6','TWO',50),('7','THREE',1000);

[QUESTION]

I wants to select first n workers (in asc list)those sum of salary is just more than 500 (> 500). for example:

following four workers are first low salary employees and having SUM of salary 600. (600 > 500)

| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |    

I need something like SELECT * FROMWorkerWHERE SUM(salary) < 501 ASC; (its incorrect query)

An assumption can be made that all having different salary.

its something like cost cutting: remove employee those are relatively unimportant

A suggestion or link to read will be helpful

share|improve this question

2 Answers 2

up vote 3 down vote accepted

After playing around with this some more, I came up with this query:

SELECT ssn, name, salary FROM (
  SELECT ssn, name, salary, (@total:=@total+salary) as total
  FROM worker, (select @total:=0) t
  ORDER BY salary ASC
) as foo
WHERE (total-salary) <=500

This seems to do what you want on this limited data, but could be very slow on large datasets.

share|improve this answer
    
@MartinSmith is it available in MySQL? –  Grijesh Chauhan Jan 24 '13 at 17:37
    
@grijeshChauhan updated my answer...hopefully that's more what you wanted. –  Derek Downey Jan 24 '13 at 18:26
    
Thanks Dtest its working and understood to me! –  Grijesh Chauhan Jan 26 '13 at 4:27
    
Yes slow but I am interested only for learning purpose :) I was trying to answer this qestion that time I wrote is question : thanks! –  Grijesh Chauhan Jan 26 '13 at 4:30
    
Derek Downey can you suggest me a book to improve my query skill. I mostly like to work in MySQL. –  Grijesh Chauhan Mar 21 at 9:46

Here is one solution, but it looks ugly. There may be a better solution without a subquery.

SELECT ssn, name, salary, prev_accum, accum from
(
    SELECT *, @a AS prev_accum, @a:= @a + salary AS accum 
    FROM Worker, (select @a :=0) as dummy
    ORDER BY salary
) AS dummy2
WHERE prev_accum < 500;

If you run this query in phpmyadmin, there is a display error, which shows BLOB for variable. You will need the following casting.

SELECT ssn, name, salary, CAST(prev_accum as decimal) prev_accum, accum from
(
    SELECT *, @a AS prev_accum, @a:= @a + salary AS accum 
    FROM Worker, (select @a :=0) as dummy
    ORDER BY salary
) AS dummy2
WHERE prev_accum < 500;
share|improve this answer
    
I had something similar in my answer, but updated it to its current form to avoid the potential issue described in the docs of reading/writing from the same user variable and adhering to the recommendation "As a general rule, you should never assign a value to a user variable and read the value within the same statement." Like SELECT @a, @a:=@a+1 –  Derek Downey Jan 24 '13 at 18:54
    
@user1931858 thanks your answer, it is very helpful for me :) –  Grijesh Chauhan Jan 26 '13 at 4:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.