Basic MySQL query not giving expected results?

MySQL database
#1

Hello

I’ve made a basic fiddle: http://sqlfiddle.com/#!9/1da0f3/13 for testing purposes. I have this output which is perfect:

buyer vendor percentage
1 2 5
1 3 3
1 4 2
2 3 2
2 4 1
2 8 4
3 1 6

However I need to get the highest ‘percentage’ for each ‘buyer’. I’ve tried using MAX() and a variety of other syntax. This is the output I need:

buyer vendor percentage
1 2 5
2 8 4
3 1 6

(5 was the highest percentage for buyer 1, 4 was the highest percentage for buyer 2, 6 was the highest (only) percentage for buyer 3.

Can someone point me in the right direction please?

#2

sqlfiddle isn’t playing nice for me today, but have you tried a group by on the buyer id?

#3

Thanks for responding. The select query looks like this:
SELECT buyer, vendor, COUNT(product) percentage FROM log GROUP BY buyer, vendor;

If I could do something like
MAX(COUNT(product) percentage) max_percentage
it would solve the whole thing. The query already has a couple of group-bys as you would notice.

#4

can you give me a dump of the data?

I need the data from the table to run it somewhere else

#5 
create table log (
id int not null primary key auto_increment,
buyer int not null, 
vendor int not null, 
product int not null
); 

insert into log (buyer, vendor, product) 
values 
(1,2,1),
(1,2,2),
(1,2,3),
(1,2,4),
(1,2,5),
(1,3,6),
(1,3,7),
(1,3,8),
(1,4,9),
(1,4,10),
(2,3,10),
(2,3,11),
(2,4,11),
(2,8,11),
(2,8,12),
(2,8,13),
(2,8,14),
(3,1,15),
(3,1,16),
(3,1,17),
(3,1,18),
(3,1,19),
(3,1,20)
;

Just a basic psuedo table :slight_smile:

#6

http://sqlfiddle.com/#!9/1da0f3/70

SELECT * FROM (
SELECT 
  buyer
  , vendor
  , percentage
FROM (
SELECT 
  buyer,
  vendor,
  count(product) percentage 
FROM 
  log 
group by buyer, vendor
  ORDER BY percentage DESC
  ) as t
GROUP BY buyer DESC
ORDER BY percentage DESC
) as t1
 ORDER BY buyer ASC
1 Like
#7

Ah a Subquery! I started looking for a Max() clause in your solution, but you used a completely different strategy, I’m surprised.

This is stunning and perfect. :slight_smile:

Can you do me the honor of sharing how you came to this solution, say, a brief hint on how you figured that multiple subqueries would be the solution for this sort of query?

#8

Break it down piece by piece. Each query does something to give you the results you were after. And that is the second iteration, the first used a limit, but you don’t want that because it means when other vendors are added, you need to change it to generate the report.

Run the queries independently and see what each does and piece them together step by step.

SQL as a whole isn’t that complicated, it is just asking the database a specific question and formatting your results.

1 Like
#10

It would work better if the values were correct.

https://www.db-fiddle.com/f/h3kBU1sQdvJj2C1JqnhQEE/0

I still need to tweak it so the limit isn’t used, but it does give the expected values.


select buyer, vendor, percentage FROM (
SELECT 
  buyer,
  vendor,
  count(product) percentage,
  ROW_NUMBER() OVER (
          PARTITION BY vendor
          ORDER BY buyer DESC) row_num  
FROM 
  log 
group by buyer, vendor
 ORDER BY row_num, percentage DESC
  LIMIT 3
) as t
ORDER BY buyer
1 Like
#11

@astonecipher, Try it in < Mysql 8.x

I am a bit confused as to what results the OP actually wants. In the first post, the OP is exactly clear what the result should be from the example data in post #1 but you seem to be going for some other result. OP’s other posts are not clear. The sql import now has product instead of percentage.

OP, what is the expected result from the SQL data you posted?

#12

As was already stated

#13

Try it in a version less than 8? What are you after? I did queries for each… row over doesn’t exist lower than 8, which was your original argument, that the query wouldn’t work in it.

People face the same issues in SQL Server, queries that work in one version don’t in higher or lower… Is the OP’s database version 5.6? If it is then the argument that it won’t work in 8 is pointless.

#14

No argument, just pointing out it is version specific. Since you didn’t mention it I was not sure if you/the post readers were aware.

Edit: I finally understood the OP’s post. I was off track.:sleeping:

1 Like
#15

Mine uses MySQL version 5.6 yes… Do you recommend all mysql users should use the latest version?

I never considered upgrading yet, I use Xampp for all local testing. The latest xampp uses version 5.6. And our local web hosting server also uses something between 5.6/5.7.

#16

For the database no. The version your at is suitable

#17

@astonecipher, the first query you posted just wasn’t sitting right with me with the three selects. Try as I might, I couldn’t get my head around it for the reason you know in PM so I consulted with one that is greater than I in SQL. The following is the feedback I got and the corrected query which does yield the correct results.

From he who is greater than I…

The outer SELECT is redundant.

The main subquey is wrong. You select buyer, vendor, count() but group only by buyer. The value of vendor is therefore meaningless and could be any of values from a record in that group (ie 3, 4,or 8). Your ORDER BY percentage arbitrarily changes which value you see.

SELECT
   buyer
   , vendor
   , percentage
FROM (
      SELECT
          buyer
        , vendor 
        , count(product) percentage
      FROM
        log
      GROUP BY buyer, vendor
      -- ORDER BY percentage DESC                            
      ) as t
GROUP BY buyer DESC
ORDER BY buyer
      
+-------+--------+------------+
| buyer | vendor | percentage |
+-------+--------+------------+
|     1 |      2 |          5 |
|     2 |      3 |          2 |   without the order by (vendor:3)
|     3 |      1 |          6 |
+-------+--------+------------+

CORRECTED QUERY

SELECT
   buyer
   , vendor
   , percentage
FROM (
      SELECT
          buyer
        , vendor 
        , count(product) percentage
      FROM
        log
      GROUP BY buyer, vendor
      ORDER BY percentage DESC                            
      ) as t
GROUP BY buyer DESC
ORDER BY buyer

+-------+--------+------------+
| buyer | vendor | percentage |
+-------+--------+------------+
|     1 |      2 |          5 |
|     2 |      8 |          4 |   with the order by (vendor:8)
|     3 |      1 |          6 |
+-------+--------+------------+
1 Like
#18

This debate is way out of my league right now :zipper_mouth_face:

Sponsor our Newsletter | Privacy Policy | Terms of Service