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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-18 : 08:59:12
|
Camilo Angulo writes "Some info:
*Database Server = SQL Server 7 / WinNT 4.0 SERVER (SP 6)
*WebPage Server = Win2000 / IIS 5.0 / ASP opening Database with ADO from the DB Server.
Ok... Hope you can help me out.....
I've a got an 'Orders' table (Order#, Product, DateUpdated, Status, Comments) where I record every status for each order.... Suppose order # 123 of product X can have status 10, 20, 30 and so on... this status are codes that simplifies the tracking of what happened with an Specific order. For example:
* OrderN --> Product --> Store --> DateUp --> Status --> Comments
* 123 --> X --> AAA --> May-30-2001 --> 10 --> 'No comments'
* 123 --> X --> AAA --> Jun-05-2001 --> 20 --> 'any comments'
* 124 --> X --> ABB --> May-31-2001 --> 10 --> 'Urgent'
* 124 --> X --> ABB --> Jun-02-2001 --> 20 --> ' etc'
* 124 --> X --> ABB --> Jun-03-2001 --> 30 --> 'Done.'
And so on. Now, for the sake of the server performance I created a view where I only can see those that do not have a '30' status to know which orders are not finished and find out what happened to them, it goes something like: "select * from orders where orderN+product Not In(select orderN+product from orders where status = '30')" and with that I get only those orders that are not finished. OK. Now comes my real question: How do I query the table so that it tells me the OrderN, The Product, The store, The last Status (based on the last update date but not repeating the order number???. My actual query is "Select OrderN, Product, Status, UpdateDate from orders order by OrderN" And I get something like:
* OrderN --> Product --> Store --> DateUp --> Status --> Comments
* 123 --> X --> AAA --> May-30-2001 --> 10 --> 'No comments'
* 123 --> X --> AAA --> Jun-05-2001 --> 20 --> 'any comments'
What I actually would like to get is:
* OrderN --> Product --> Store --> DateUp --> Status --> Comments
* 123 --> X --> AAA --> Jun-05-2001 --> 20 --> 'any comments'
I tried using Distinct but was not useful, I tried with group by but then I have to add an agregate function to each of the other fields and I don't know which can/should I use.
Hoping you can help me out, thanks in advance.
CAMILO." |
|
|
|
|
|
|
|
How to Eliminate Duplicate Rows (not DISTINCT)??