Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL please

 
Mohamed Farouk
Ranch Hand
Posts: 249
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Friends
A bit of puzzle , taught it is a easy one, but struggling, SQL required for getting the max amount customer order grouped on date

Order Table
date customer_id order_id amount
1/1/2013 1 1 1500
1/1/2013 2 2 1500
3/1/2013 1 1 1000
4/1/2013 3 1 700 -- Will not be in the output of sql as the amount is less than 1000 which is from another record on the same date
4/1/2013 1 1 1000


Answer should be

date customer_id order_id amount
1/1/2013 1 1 1500
1/1/2013 2 2 1500
3/1/2013 1 1 1000
4/1/2013 1 1 1000




 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
grouped by, or ordered by?
 
James Boswell
Bartender
Posts: 1051
5
Chrome Eclipse IDE Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What SQL have you tried so far?
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If only SQL had something like a GROUP BY clause...
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Unfortunately neither the description of the requirements nor the example provide an unambiguous description of the problem.

From the example it appears that the data might already consist of orders grouped by date, but we can't tell that for sure. And the description isn't clear what this "max amount" is supposed to be. The example shows a lot of things other than a "max amount", which is also confusing.
 
Mohamed Farouk
Ranch Hand
Posts: 249
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Required SQL Group the orders according to date for with max amount for each date

Order Table
date customer_id order_id amount
1/1/2013 1 1 1500
1/1/2013 2 2 1500
3/1/2013 1 1 1000
4/1/2013 3 1 700 -- Will not be in the output of sql as the amount is less than 1000 which is from another record on the same date
4/1/2013 1 1 1000


Required result
date customer_id order_id amount
1/1/2013 1 1 1500
1/1/2013 2 2 1500
3/1/2013 1 1 1000
4/1/2013 1 1 1000

I tried: select date, customer_id, order_id, max(amount) group by date
returns:
date customer_id order_id amount
1/1/2013 1 1 1500
3/1/2013 1 1 1000
4/1/2013 1 1 1000

Missing for below order record for customer 2 with same amount 1500
1/1/2013 2 2 1500
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mohamed Farouk wrote:Required SQL Group the orders according to date for with max amount for each date


Well, I'm sorry. English is my native language and I still can't figure out what that means. "Group the orders according to date" should mean that the result is a list of orders. But then "with max amount for each date" implies grouping by date, not by order. So if I were asked this question, I would be asking what the requirements were rather than trying to answer it.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
  • Figure out what your output should look like. This is what you want to SELECT.
  • In a GROUP BY query, there will be some column in the output that you are doing some kind of group operation on e.g. MAX(...).
  • Everything else in the output is therefore what you want to group these values by, and needs to be in your GROUP BY clause.

  • I think you need to look at your "required" output and check if that's really what is required. If so, then you should be able to figure out how to change your SQL to get the required results. I recommend you take a few minutes to look at some examples of how to write SQL GROUP BY queries and try to figure it out for yourself. It's really not that hard, and you'll learn more that way than simply posting the same question here and expecting us to answer it for you.
     
    Mohamed Farouk
    Ranch Hand
    Posts: 249
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    OK Sorry I apologize English is not my first language!

    Let me put it like this in numbers

    Order Table
    date customer_id order_id amount
    1/1/2013 1 1 1500
    1/1/2013 2 2 1500
    3/1/2013 1 1 1000
    4/1/2013 3 1 700
    4/1/2013 1 1 1000


    What will be the SQL to to get the below answer
    date customer_id order_id amount
    1/1/2013 1 1 1500
    1/1/2013 2 2 1500
    3/1/2013 1 1 1000
    4/1/2013 1 1 1000

    Let me be clear i tried and I failed cant get it, lets see what you guys come up with!
     
    Bear Bibeault
    Author and ninkuma
    Marshal
    Pie
    Posts: 65335
    97
    IntelliJ IDE Java jQuery Mac Mac OS X
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Looks like a simple order by date,customer_id would work to me.
     
    Mohamed Farouk
    Ranch Hand
    Posts: 249
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Looks like a simple order by date,customer_id would work to me.

    it will bring all the records we want only 4 out of 5 (4/1/2013 3 1 700 should not appear as it has 700 for 4/1/2013 which is less than 1000 for 4/1/2013

    Thanks for trying
     
    Paul Clapham
    Sheriff
    Posts: 21572
    33
    Eclipse IDE Firefox Browser MySQL Database
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Mohamed Farouk wrote:What will be the SQL to to get the below answer
    date customer_id order_id amount
    1/1/2013 1 1 1500
    1/1/2013 2 2 1500
    3/1/2013 1 1 1000
    4/1/2013 1 1 1000



     
    Bear Bibeault
    Author and ninkuma
    Marshal
    Pie
    Posts: 65335
    97
    IntelliJ IDE Java jQuery Mac Mac OS X
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Include a where clause that sets the minimum for that column. That has nothing to do with the order.
     
    Sresh Rangi
    Ranch Hand
    Posts: 54
    5
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Mohamed Farouk wrote:OK Sorry I apologize English is not my first language!

    Let me put it like this in numbers

    Order Table
    date customer_id order_id amount
    1/1/2013 1 1 1500
    1/1/2013 2 2 1500
    3/1/2013 1 1 1000
    4/1/2013 3 1 700
    4/1/2013 1 1 1000


    What will be the SQL to to get the below answer
    date customer_id order_id amount
    1/1/2013 1 1 1500
    1/1/2013 2 2 1500
    3/1/2013 1 1 1000
    4/1/2013 1 1 1000

    Let me be clear i tried and I failed cant get it, lets see what you guys come up with!


    To clarify, do you want the same results as:


    This is different from the simple "group by" query because it allows multiple orders on the same date with equal amounts.

    The actual query shouldn't use a subselect in the where clause. It can be written using a join instead.
     
    Mohamed Farouk
    Ranch Hand
    Posts: 249
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi thanks for trying

    Perfect Answer: select * from order o where amount = (select max(amount) from order where date=o.date);

    Thanks Sresh Rangi your spot on.!
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic