How to optimize the subqueries in SQL?

Question

I have a data-set, the columns sample information are like below:

Date     ID     Cost
05/01   1001     30
05/01   1024     19
05/01   1001     29
05/02   1001     28
05/02   1002     19
05/02   1008     16
05/03   1017     89
05/04   1003     28
05/04   1001     16
05/05   1017     28
05/06   1002     44
... etc...

And I want to create a table to display the top one payer(cost the most) on each day, which means there are only two columns in the table, and the output sample should be like this:

Date      ID
05/01    1001
05/02    1001
05/03    1017
05/04    1003
...etc...

I know this question is simple, and my problem is that I want to simplify the queries.

My query:

select Date, ID
from (select Date, ID, max(SumCost)
      from (select Date, ID, sum(cost) as SumCost
            from table1
            group by Date, ID) a
      group by Date, ID) b;

It seems kind of stupid, and I want to optimize the queries. The point is that I want to only output the Date and the Id, these two columns.

Any suggestions?

Your query is incorrect. Can a player have multiple costs on the same day? If yes should the cost be summed up or do we just take the max? — Raunak Thomas
– Raunak Thomas, Commented Apr 18, 2018 at 6:46
Your Query makes no sense. Actually what you expect? According to your query, SELECT DISTINCT Date, ID FROM table1 is enough. — DineshDB
– DineshDB, Commented Apr 18, 2018 at 6:49
why you add max(SumCost), what is the reason because already group by happen in sum(cost) — janith1024
– janith1024, Commented Apr 18, 2018 at 7:22
@ Raunak Thomas A payer can have multiple costs on the same day, so, that is why I need sum up them first and then use max function to select the person who cost the most on each day — Marcus
– Marcus, Commented Apr 18, 2018 at 16:25
@ DineshDB A payer can have multiple costs on the same day, so, that is why I need sum up them first and then use max function to select the person who cost the most on each day — Marcus
– Marcus, Commented Apr 18, 2018 at 16:25

Gordon Linoff · Accepted Answer · 2018-04-18 10:23:19Z

1

Here is a method using a correlated subquery:

select t.*
from t
where t.cost = (select max(t2.cost) from t t2 where t2.date = t.date);

answered Apr 18, 2018 at 10:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marcus Over a year ago

@ Gordon Lincoff Thanks for your guide. However, according to the data, there are multiple payments for one payer on the same day. So, in order to select the person who paid the most on each day, we need sum up first and then use max function. Another thing is that, I need only output two columns, which is Date and ID, the amount of payment is not allowed to show up in the table. In this case, we have to do 3 subqueries. So, how do we optimize the queries?

Gordon Linoff Over a year ago

@Marcus . . . Your data has no examples of two ids on the same day.

Marcus Over a year ago

@ Gordon Lincoff Thanks for your reminds. I just edited my sample. So how do we optimize the queries for such cases?

Raunak Thomas · Accepted Answer · 2018-04-18 06:48:58Z

0

If we take a max cost when there are multiple costs for the player on the same day, then this query will work. The query that you have written above is incorrect.

Select date, ID
from
(
Select Date, ID, row_number() over(partition by date order by cost desc) as rnk
from table
) a
where rnk = 1

answered Apr 18, 2018 at 6:48

Raunak Thomas

1,4251 gold badge14 silver badges29 bronze badges

1 Comment

DineshDB Over a year ago

There is no row_number() functionality in MySQL.

Collectives™ on Stack Overflow

How to optimize the subqueries in SQL?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related