GROUP BY CLAUSE w/ aggregate in sqlcourse2

[askalako]

Hello I'm a very fresh newbie in the world of sql. I have gone through sqlcourse 1 and now I think I am stuck with a question in the GROUP BY section of sql course2. Here's the question:

(I am using the website's built in table "items_ordered")

I am trying to display the item (which is a tent), minimum price and order date when that tent was ordered with that min price.

Somehow, it seems that when I query -
select item, max(price), order_date from items_ordered where item = 'tent' group by item;

or

select item, min(price), order_date from items_ordered where item = 'tent' group by item;

the results show the same order_date for both queries. It seems to only show the date when the tent was ordered with the max price. The order_date doesn't seem to be corresponding with the min(price). Is this how the sql database works or am I missing something in my statement to link these two together?

Appreciate anyone who can help as I try to self-learn more about sql.

[Rawhide]

Are you sure you've posted your query in here correctly? That query should give you an error. All fields in the the select clause must also be in the group by clause or an aggregate.

It sounds like the queries that you are running are:

select item, min(price), min(order_date)
from items_ordered
where item = 'tent'
group by item;

and

select item, max(price), min(order_date)
from items_ordered
where item = 'tent'
group by item;

[askalako]

Hello.

Thank you for your reply. The sql interpreter in sqlcourse2.com didn't give me an error. Instead, the query

select item, min(price), order_date
FROM items_ordered
WHERE item = 'tent'
GROUP BY item;

gave me a result with the table

Tent 79.99 18-Sep-1999 (note that the date is the date for the max price and not the min price.)

So now at least I know that my query is incorrect. How do I correct this so that I get the item, minimum price and date when the tent was ordered with the minimum price?

Again, thanks for the patience and the answers.

[askalako]

Hello. Please disregard my last post. I tried the corrections in your post and they worked perfectly.