• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL please

 
Ranch Hand
Posts: 249
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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




 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
grouped by, or ordered by?
 
Bartender
Posts: 1051
5
Hibernate Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What SQL have you tried so far?
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If only SQL had something like a GROUP BY clause...
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
    Sheriff
    Posts: 67746
    173
    Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
    Marshal
    Posts: 28193
    95
    Eclipse IDE Firefox Browser MySQL Database
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
    Sheriff
    Posts: 67746
    173
    Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Include a where clause that sets the minimum for that column. That has nothing to do with the order.
     
    Ranch Hand
    Posts: 59
    6
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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.!
    reply
      Bookmark Topic Watch Topic
    • New Topic