Results 1 to 10 of 10

Thread: what is the command for this ...

  1. #1
    Join Date
    Feb 2010
    Posts
    5

    what is the command for this ...

    Hi all,

    This is my first time posting thread in the forum since i have just registered
    I came up to this forum through sqlcourse2.com where i'm learning SQL. So, it is clear to you that i'm still beginner in SQL

    In this website there are two tables are used for the exercises; items_ordered & customers

    My question: is it possible to know when was the minimum price and what was that

    I thought it should be in the following manner: select min(price), order_date from items_ordered group by order_date;
    But that didn't give me the desired output

    Can anyone guide me to the solution ?
    Thanks

  2. #2
    Join Date
    Jan 2010
    Posts
    37
    What about this:

    select top 1 price, order_date
    from items_ordered
    order by price

  3. #3
    Join Date
    Feb 2010
    Posts
    5
    Thanks J013 for reply

    Actually it didn't work
    In fact i have tried top 1(price) "with brackets" and without but nothing comes in the result

    Any other tip ?
    I believe this is straight forward for the experts in SQL
    Waiting your another tip

  4. #4
    Join Date
    Jan 2010
    Posts
    37
    What platform are you using? MS SQL 2005?

  5. #5
    Join Date
    Feb 2010
    Posts
    5
    I have no idea
    May you try it ?
    click on the link below and scroll down. You find empty box, here where you can type the statement
    http://sqlcourse2.com/having.html

    Note that it may not accept some clauses that are not included in the course

    Check it out and let me know
    Thx

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    It's ANSI SQL, should work with most SQL databases such as Oracle, SQL Server, mySQL, MS Access, Informix, Sybase, or any other ANSI SQL compliant database.

  7. #7
    Join Date
    Jan 2010
    Posts
    37
    TOP does not seem to be supported in the interpreter for the course. This will work:

    select price, order_date from items_ordered order by price;

    But it will bring back every row, ordered by the lowest price first.

  8. #8
    Join Date
    Apr 2010
    Posts
    1

    TOP should work but, ...

    ... in my experience, sometimes I use the parenthesis and sometimes I don't. Whichever way works is good for me.

    select top (1) price, order_date from items_ordered order by price

    WILL WORK!!!

    select top 1 price, order_date ... should give an error

    The above (top (1)) statement will work and will bring up the least (minimum) price paid since the searches are ordered ASCENDING by default anyways and only one row will be returned.

  9. #9
    Join Date
    Mar 2010
    Posts
    7

    Another solution

    with a concealed attempt to bewilder, here is another solution to get the min value without using the TOP keyword

    SELECT price, order_date
    from items_ordered X
    WHERE price <= ALL
    (SELECT price
    FROM items_ordered Y)

  10. #10
    Join Date
    May 2010
    Posts
    3
    note that while both solutions work, using the "top (1)" approach will only return one record. If there are multiple instances when the minimum price was paid, the second approach "<=" will return all instances, not just one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •