Selecting the last 3 dates

Databases
1

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

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 :slight_smile:

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 :slight_smile:

3

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 
SELECT country
     , COUNT(*) AS howmany
  FROM orders
GROUP
    BY country

:slight_smile:

5

It worked like a charm. Wonderful. Thank you Rudy. You have been helpful.

6

you’re welcome and thanks for the kind words :slight_smile:

7

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 

SELECT country
     , COUNT(*) AS howmany
  FROM orders
GROUP
    BY country
ORDER BY
    howmany

I think that should work.

9

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.