Query of orders - 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

Query of orders

Author

Topic

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-09 : 20:55:00

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 - 2011-11-09 : 21:04:00

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/

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-09 : 21:10:04

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.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:13:43

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/

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-09 : 21:20:45

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?

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:26:56

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/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:33:30

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/

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-09 : 21:34:33

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?

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:44:52

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/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:59:34

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/

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-09 : 22:03:01

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.

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-09 : 22:06:30

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!

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 22:09:01

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/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-10 : 10:07:07

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/

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-10 : 10:35:56

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.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-10 : 11:25:44

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/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-10 : 11:48:52

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/

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2011-11-10 : 13:11:00

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

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-10 : 13:24:38

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/

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2011-11-10 : 13:28:56

Yes I know Brett just though they might want to see what it looks like for a real database.

Jim
Users <> Logic

kbreitsprecher
Starting Member

13 Posts

Posted - 2011-11-10 : 16:08:15

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...

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources