SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query of orders
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

kbreitsprecher
Starting Member

Canada
13 Posts

Posted - 11/09/2011 :  20:55:00  Show Profile  Reply with Quote
This is a school assignment, but i'm out of ideas, so I thought I would come ask people who know way more about this than I do. The question asks
"For each order, list the orderID, CustomerID, OrderDate, and most recent date among all the orders."

I can easily list the OrderId, CustomerId and OrderDate; I even know how to calculate the most recent date MAX(OrderDate), but where I am lacking the knowledge is how to put it all together. I assume that I need 4 columns.

I thought something like

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

Would work, but I get an error. So I simplified it to see where I get the error, and it happens as soon as I add the MAX(OrderDate) in.

I think maybe I need to use a second select statement, or a having statement, but I don't see how that would help.

keep in mind that this should be done without the use of a JOIN of any sort. That comes in Chapter 7, and we are getting to that on Monday.

Any Help would be appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 11/09/2011 :  21:04:00  Show Profile  Reply with Quote
Well..since you gave it a shot..

Break down what its says

For each Order...so that would be....what? an OrderId

So you are right that you need to look for the MAX, but then you need the details, right?

So what would you write to JUST Find the MAX OrderDate

Write that..JUST the MAX orderdate..ONLY..then get back to us



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

kbreitsprecher
Starting Member

Canada
13 Posts

Posted - 11/09/2011 :  21:10:04  Show Profile  Reply with Quote
To find the Max OrderDate, the statement would be
SELECT MAX(OrderDate)
FROM Order_t;

I have tried that and it works, and it displays the most recent order date on the screen and obviously nothing else.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/09/2011 :  21:13:43  Show Profile  Reply with Quote
OK Good...

Now write a query that selects the data you want..

THEN say

Where OrderDate IN (..put your query here)

Post that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

kbreitsprecher
Starting Member

Canada
13 Posts

Posted - 11/09/2011 :  21:20:45  Show Profile  Reply with Quote
SELECT OrderID, CustomerID, OrderDate
FROM Order_t
WHERE OrderDate IN (SELECT MAX(OrderDate));

I know this doesn't work, because I tried it, but It has to be close no?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/09/2011 :  21:26:56  Show Profile  Reply with Quote
Damn Close..so close, and because you did due dilegence

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

Simple right?

Time for another round..damn this wireless sheet in the bar was a good idea



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/09/2011 :  21:33:30  Show Profile  Reply with Quote
Is that what you need?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

kbreitsprecher
Starting Member

Canada
13 Posts

Posted - 11/09/2011 :  21:34:33  Show Profile  Reply with Quote
Thank you very much.

However is that what the question was asking I assumed that I would have a list of multiples entries, with a new column that would show the most recent order for every customer..

Am I reading the question wrong?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/09/2011 :  21:44:52  Show Profile  Reply with Quote
OK, OI assumed OrderIOD was a unique key..how could it not be?

Do you want to know the latest order for a customer?

You need post EXACTLY What is asked of you..because I believe that's what it said


>> "For each order, list the orderID, CustomerID, OrderDate, and most recent date among all the orders."


For each Order..you sure you got that part right?

can you post the DDL with all the constraints

in any case..if it IS CustomerID..we need to change this to EXISTS

Do you have SQL Server installed?

Do you know what Books Online (BOL) is?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/09/2011 :  21:59:34  Show Profile  Reply with Quote
IF it's for a Customer

SELECT o.OrderID, o.CustomerID, o.OrderDate
FROM Order_t o
WHERE EXISTS (
SELECT * FROM Order_t i
WHERE o.CustomerID = i.CustomerID
GROUP BY i.CustomerID
HAVING o.OrderDate = MAX(i.OrderDate));

Did they teach you this?

Always label all your columns when there more than 2 tables involved..a best practice bit of advice..even if the column names are unique


IF it really is OrderID just change CustomerID to OrderID..but that doesn't make sense..should only be 1 OrderDT per OrdeID




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/



Edited by - X002548 on 11/09/2011 22:00:37
Go to Top of Page

kbreitsprecher
Starting Member

Canada
13 Posts

Posted - 11/09/2011 :  22:03:01  Show Profile  Reply with Quote
Ok, I'm sorry I can't answer all your questions because I don't know what some of them mean. But I will try my best.

The question is from Chapter 6, Question 40 from Modern Database Management, tenth edition by Hoffer, Ramesh and Topi. And it is exactly:

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

the Order_t table has the following fields, OrderID, OrerDate, FullfillmentDate,Salesperson and ShipAdrsID.

I assumed the question wanted a list that would look something like this.

OrderId CustomerId OrderDate MostRecentDate
1 1 10/10/1990 10/21/2001
2 1 10/21/2001 10/21/2001
4 2 1/12/1998 1/12/1998
3 3 5/26/2010 4/30/2011
6 3 4/30/2011 4/30/2011
etc

I don't know what you mean by DDL and all the constraints. I know DDL stands for Data Definition Language, something I learned from this course, but I don't know exactly what your asking me.

I do know what books online is, and I don't have SQL server installed. I am using Access(it is what my teacher is using), and Universal SQL Editor.

So I don't know exactly what the question is asking, only my interpretation of it.
Go to Top of Page

kbreitsprecher
Starting Member

Canada
13 Posts

Posted - 11/09/2011 :  22:06:30  Show Profile  Reply with Quote
quote:
Originally posted by kbreitsprecher

Ok, I'm sorry I can't answer all your questions because I don't know what some of them mean. But I will try my best.

The question is from Chapter 6, Question 40 from Modern Database Management, tenth edition by Hoffer, Ramesh and Topi. And it is exactly:

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

the Order_t table has the following fields, OrderID, OrerDate, FullfillmentDate,Salesperson and ShipAdrsID.

I assumed the question wanted a list that would look something like this.

OrderId CustomerId OrderDate MostRecentDate
1 1 10/10/1990 10/21/2001
2 1 10/21/2001 10/21/2001
4 2 1/12/1998 1/12/1998
3 3 5/26/2010 4/30/2011
6 3 4/30/2011 4/30/2011
etc

I don't know what you mean by DDL and all the constraints. I know DDL stands for Data Definition Language, something I learned from this course, but I don't know exactly what your asking me.

I do know what books online is, and I don't have SQL server installed. I am using Access(it is what my teacher is using), and Universal SQL Editor.

So I don't know exactly what the question is asking, only my interpretation of it.



However looking back at the question it could look more like this
OrderId CustomerId OrderDate MostRecentDate
1 1 10/10/1990 4/30/2011
2 1 10/21/2001 4/30/2011
4 2 1/12/1998 4/30/2011
3 3 5/26/2010 4/30/2011
6 3 4/30/2011 4/30/2011

But Again this is all speculation based on what the question is saying. This Professor doesn't respond to emails, so I can't ask for clarification, and even if he did respond, i'm not sure he would offer any guidance. Welcome to University!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/09/2011 :  22:09:01  Show Profile  Reply with Quote
I gotta bail..and this is a sql server board..BUT

I believe it means for each customer..ask the teach..or is due tomorrow?

India should be online soon

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/10/2011 :  10:07:07  Show Profile  Reply with Quote
Morning

You say this

>> the Order_t table has the following fields, OrderID, OrerDate, FullfillmentDate,Salesperson and ShipAdrsID.

Then you say you want


OrderId CustomerId OrderDate   MostRecentDate
1       1          10/10/1990  10/21/2001
2       1          10/21/2001  10/21/2001
4       2           1/12/1998   1/12/1998
3       3           5/26/2010   4/30/2011
6       3           4/30/2011   4/30/2011



Even in Access you can use the documentor option to get a copy of the table layout

And Post sample data as it is EXACTLY in your table..like

INSERT INTO Order_t (which did your teacher set up that name???: Or was it from the book??)

SELECT 'data', 'data', 1, '1/1/2011' UNION ALL
SELECT 'data', 'data', 1, '1/1/2011' UNION ALL
ect

Did you ask the teach what the expect results are suppose to look like? Since the test exercise wording is confusing


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

kbreitsprecher
Starting Member

Canada
13 Posts

Posted - 11/10/2011 :  10:35:56  Show Profile  Reply with Quote
Here is the table definition:

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

Index Name Index Type Indexed Column / Expression
PrimaryKey Unique OrderID
Customer_Id Non Unique CustomerID
CUSTOMER_tORDER_t Non Unique CustomerID
Order_Id Non Unique OrderID
SalesPerson_Id Non Unique SalesPersonID
ShipAdrs_Id Non Unique ShipAdrsID

And here is some 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)

I'm not sure how you got your table to format properly, sorry for the way this one looks. My teacher said he didn't know what it was supposed to look like, his exact response was "I don't know, just do what the question asks." When I asked a couple follow up questions, would this be acceptable, i got another "I don't know."

The Database is set up from the book, and downloaded from the book website. I forgot to mention the Customer ID is also in Order_t.

When I open the database in access I don't get to manipulate the data. A "switchboard" comes up that lets me only view and add information from series of forms, so that is why I am using Universal SQL Editor to write the queries. I agree the question the book is asking isn't clear and that could be part of the problem I am having with finding the correct solution. It is due on Monday so I have all weekend to try and figure it out. This also happens to be the last question I have for this assignment.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/10/2011 :  11:25:44  Show Profile  Reply with Quote
Ask what the HELL You're paying him for...He doesn't know

Then How the HELL Does he know if you got it right?

To format the data, use [ code] ..data [ /code] tags..with out the space

hit reply with quote to see what I did here


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)





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/10/2011 :  11:48:52  Show Profile  Reply with Quote
I can do it as a hack in ACCESS

SELECT o1.OrderID, o1.CutomerID, o1.OrderDate, MAX_OrderDate.MaxOfOrderDate
FROM Order_t AS o1, MAX_OrderDate;

Where MAX_OrderDate is a query that is the one you wrote before

Which is

SELECT Max(Order_t.OrderDate) AS MaxOfOrderDate
FROM Order_t;

This is because access 2007 doesn't do CROSS JOINS or derived tables

So I don't know what this guy wants..are you sure the data is in Access? or is it in SQL Server and they are just using Access as a data entry for to sql server?

IF it's SQL SERVER

SELECT o1.OrderID, o1.CutomerID, o1.OrderDate, MAX_OrderDate.MaxOfOrderDate
FROM Order_t AS o1
CROSS JOIN (
SELECT Max(Order_t.OrderDate) AS MaxOfOrderDate
FROM Order_t);


Anyone else want to chime in?


I believe this is Exactly what that crappy question is stating

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/



Edited by - X002548 on 11/10/2011 11:49:53
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1535 Posts

Posted - 11/10/2011 :  13:11:00  Show Profile  Visit JimL's Homepage  Reply with Quote
well if I was to answer the question exactly how it is written and not interpeting what would be remotely normal.
(but then whoever wrote it has a problem being clear)

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

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


Sorry this wont work in a access db but will work through access to a SQL db (access.adp).

Jim
Users <> Logic

Edited by - JimL on 11/10/2011 13:16:24
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/10/2011 :  13:24:38  Show Profile  Reply with Quote
Well that's the hack I wrote in Access

And that's what it says..WHICH I think is WRONG

It probably should say the MAX Date for the Customer

What school is this for?

I would complain to the head of the Dean of IT or whatever they call themselves

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1535 Posts

Posted - 11/10/2011 :  13:28:56  Show Profile  Visit JimL's Homepage  Reply with Quote
Yes I know Brett just though they might want to see what it looks like for a real database.



Jim
Users <> Logic
Go to Top of Page

kbreitsprecher
Starting Member

Canada
13 Posts

Posted - 11/10/2011 :  16:08:15  Show Profile  Reply with Quote
I would love to complain to the Department Chair, but it never does anything. This teacher must be doing some fantastic research otherwise he is completely useless. I have submitted 4 assignments, this will be number 5, and have only received the second one back. I had question about one of the answers he said I had wrong, because I was positive it was right. He said "well it looks right to me", so then can i get the marks that were taken off? and he said no... LOL

The school is Lakehead University.

I will submit the SQL version and see what he says.. It seems to work fine in Universal SQL editor. Thanks for the help guys...
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000