Help with Homework - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Homework

Author

Topic

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-12 : 10:24:15

I asked this question earlier, and maybe you guys and girls can help me a little on this.

I have two questions. First, What is the question the text is actually asking?? Here is the question.

"For each order, list the order ID, customer ID, order date, and most recent date among all orders."

After you help me determine what is being asked, and then how do I code it. I have some tries I won't bore you with now, but i'll provide it after the question definition has been defined.

Here is the Order_t table definition

CREATE TABLE Order_T(
OrderID COUNTER NOT NULL,
OrderDate DATETIME,
CustomerID INTEGER,
FulfillmentDate DATETIME,
SalesPersonID INTEGER,
ShipAdrsID INTEGER);

Sample table information:


OrderID	OrderDate			CustomerID	FulfillmentDate		SalesPersonID	ShipAdrsID
1		2009-09-08 00:00:00	4			2009-11-25 00:00:00	3				(null)
2		2009-10-04 00:00:00	3			(null)				3				(null)
3		2009-07-19 00:00:00	1			(null)				2				(null)
4		2009-11-01 00:00:00	6			(null)				5				(null)
5		2009-07-28 00:00:00	4			(null)				3				(null)
6		2009-08-27 00:00:00	4			(null)				3				(null)
7		2009-09-16 00:00:00	1			(null)				2				(null)
8		2009-09-16 00:00:00	4			(null)				3				(null)
9		2009-09-16 00:00:00	6			(null)				5				(null)
19		2010-03-05 00:00:00	4			(null)				3				(null)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-12 : 10:37:21

The question is bit od.
It is asking for the order id, customer id and order date for each order and also the maximum order date from the table.
I would have expected it to ask for the maximum order date for the customer.

Have a go at it and post what you get. I assume you can do the bit without the max order date.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-12 : 11:05:36

Ok, here are a few I came up with. This one only produces the record with the most recent date.

SELECT OrderID, CustomerID, OrderDate
FROM Order_t
WHERE OrderDate IN (SELECT MAX(OrderDate)FROM Order_t);

If you take the where statement off, you get the first three columns, but how do I put that max date into every row of the new column. I will also add it should be able to be done without using a join(this is an exercise from Chapter 6, and we don't learn joins until Chapter 7).

SELECT OrderID, CustomerID, OrderDate
FROM Order_t;

Is there a way to do this with using a join??

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-12 : 11:07:28

Another solution someone gave me was...

Declare @Maxorder int
Select @Maxorder = max(orderID)
from Order_t

SELECT OrderID, CustomerID, OrderDate, @Maxorder AS MostRecentDate
FROM Order_t;

But i can't get that to work in MYSQL...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-12 : 11:54:38

Oh - MyQSL - not used that.
Has your course introduced derived tables?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 11:58:12

quote:
Originally posted by kbreitsprecher

Another solution someone gave me was...

Declare @Maxorder int
Select @Maxorder = max(orderID)
from Order_t

SELECT OrderID, CustomerID, OrderDate, @Maxorder AS MostRecentDate
FROM Order_t;

But i can't get that to work in MYSQL...

MYSQL? this is ms sql server. you need to post in MYSQL forums if you want solution for MySQL
if it was sql server, it should have been like


SELECT OrderID, CustomerID, OrderDate,max(OrderDate) over () AS MostRecentDate
FROM Order_t;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-12 : 12:05:02

I'm really sorry I just googled SQL forum. I'll find a MY SQL Server.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-12 : 12:24:35

If we're giving solutions
It mighr be something like

select OrderID, customerID, orderdate, (select max(OrderDate) from Orders)
from Orders

select OrderID, customerID, orderdate, maxdate
from Orders
cross join (select maxdate = max(OrderDate) from Orders) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources