SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    selecting the last 3 dates

    Hello everybody,

    I have an order table which names and order dates/times. There are so many records in the table.

    A sample from the orders table:

    J Goodwin 15-12-2009 18:05:05
    J Dillord 05-02-2010 22:00:45
    P Dillord 28-02-2010 02:02:12
    C Diatto 01-03-2010 13:52:00
    A Parlour 04-03-2010 12:12:12
    K Abbot 05-03-2010 02:20:08

    I want to select the newest 3 orders for example. How do you do that?

    Thanks in advance
    ------------------

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,069
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    SELECT ... FROM ... WHERE ... ORDER BY order_date DESC LIMIT 3

    Where order_date is the name of the column indicating the date. You didn't specify column names, so you should substitute order_date with your own column date

    For future reference, it always good to add the result(s) of SHOW CREATE TABLE query/queries of all tables involved in your question to your post, so we can understand the problem better

  3. #3
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much ScallioXTX.

    I feel stupid not realizing the DESC, whihc made all the difference. I was using just before I posted order by fieldname limit 1, which showed the first record. I thank you nevertheles.

    Another related question is that I have many countries in the country column indicating where the order was placed from. for example, US is recorded in 8 times and UK 6 and Sweden 3. In other words, I have 8 occurances of USA and 6 of UK and so on. How can I simply say like....8 times were ordered from USA and 6 times from UK and so on and forth?

    Am I making any sense?
    ------------------

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT country
         , COUNT(*) AS howmany
      FROM orders
    GROUP
        BY country
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It worked like a charm. Wonderful. Thank you Rudy. You have been helpful.
    ------------------

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you're welcome and thanks for the kind words
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have one question. The result of the SQL above is as follows:

    country | howmany
    Russian Federation 5
    Sweden 1
    United Kingdom 7
    United States 4

    Is it possible to order it by the column howmany? In other words, the result starts with UK and Russia second and USA third and so on. I tried order by howmany but it added up to one number. And Group by howmany returns an error.

    Could you be of help here again?
    ------------------

  8. #8
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT country
         , COUNT(*) AS howmany
      FROM orders
    GROUP
        BY country
    ORDER BY
        howmany
    I think that should work.

  9. #9
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Risoknop. Your solution worked ok but it showed the results the other way around. That is, to say the country with the most orders was at bottom while the country with the least numbers of orders were at top. I wanted the reverse. But I just added the keyword DESC at the end and it fixed the problem.

    Thank you again.
    ------------------


Bookmarks

Posting Permissions

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