April 20, 2014 at 11:12 am
Hi, this is going to be my first time posting in this forum and I'm currently studying SQL at the moment, please forgive me if I have done something wrong on my first post.
I've been trying to sort out that all of the data will appear in ascending order. I already have the tables joined in but whenever I try to order them using order by I'm unable to do so. Am I missing something?
Here is my query using the northwind sample database:
Declare @test-2 table(
EmployeeName varchar(max),
CustomerID varchar(max),
OrderID int,
OrderDate date,
OrderAmount money
)
Insert into @test-2(EmployeeName,CustomerID,OrderID,OrderDate,OrderAmount)
Select (e.FirstName + ' ' + e.LastName) as EmployeeName,
c.CustomerID,
o.OrderID,
o.OrderDate,
SUM((r.UnitPrice * r.Quantity) - (r.UnitPrice * r.Quantity * r.Discount)) as OrderAmount
FROM Orders o
Inner Join Employees e
On e.EmployeeID = o.EmployeeID
Inner Join Customers c
On c.CustomerID = o.CustomerID
Inner Join [Order Details] r
On r.OrderID = o.OrderID
Group by e.FirstName,e.LastName,c.CustomerID,o.OrderID,o.OrderDate,e.EmployeeID
Order by EmployeeName,c.CustomerID,o.OrderDate,o.OrderID
Select * from @test-2
April 20, 2014 at 11:45 am
Does this return what you expect?
Select (e.FirstName + ' ' + e.LastName) as EmployeeName,
c.CustomerID,
o.OrderID,
o.OrderDate,
SUM((r.UnitPrice * r.Quantity) - (r.UnitPrice * r.Quantity * r.Discount)) as OrderAmount
FROM Orders o
Inner Join Employees e
On e.EmployeeID = o.EmployeeID
Inner Join Customers c
On c.CustomerID = o.CustomerID
Inner Join [Order Details] r
On r.OrderID = o.OrderID
Group by e.FirstName,e.LastName,c.CustomerID,o.OrderID,o.OrderDate,e.EmployeeID
Order by EmployeeName,c.CustomerID,o.OrderDate,o.OrderID
April 20, 2014 at 4:27 pm
Lynn Pettis (4/20/2014)
Does this return what you expect?
Select (e.FirstName + ' ' + e.LastName) as EmployeeName,
c.CustomerID,
o.OrderID,
o.OrderDate,
SUM((r.UnitPrice * r.Quantity) - (r.UnitPrice * r.Quantity * r.Discount)) as OrderAmount
FROM Orders o
Inner Join Employees e
On e.EmployeeID = o.EmployeeID
Inner Join Customers c
On c.CustomerID = o.CustomerID
Inner Join [Order Details] r
On r.OrderID = o.OrderID
Group by e.FirstName,e.LastName,c.CustomerID,o.OrderID,o.OrderDate,e.EmployeeID
Order by EmployeeName,c.CustomerID,o.OrderDate,o.OrderID
No. It does return but what I want is that every column should be in an ascending order.
April 20, 2014 at 4:45 pm
Let me get this straight, you want the data in each column sorted in ascending order independent of the relationship of the data.
Please explain the rational for this please.
April 21, 2014 at 12:03 am
Lynn Pettis (4/20/2014)
Let me get this straight, you want the data in each column sorted in ascending order independent of the relationship of the data.Please explain the rational for this please.
Yeah, I'm just wondering if that would be possible? Probably not because the tables that I have had something related to them.
April 21, 2014 at 5:43 am
The thing about ordering is that it never works quite the way you expect. The individual columns are all associated with one record, so you can't get every column to list in ascending order unless you break that connection.
Think of a phone book. You have the following entries in the book:
LastName FirstName Phone#
Sager Tony 555-8972
Smith Dana 555-1234
Smith Joe 555-1111
Smyth John 555-9958
Sovan Alice 555-7762
Now, the phone book lists the order of LastName first so we can easily find the person we're calling. Then it orders by FirstName (within the subset of LastName). It does not order by Phone#. But if we do what you want to do, order by each column ascending, this is what we get:
LastName FirstName Phone#
Sager Alice 555-1111
Smith Dana 555-1234
Smith Joe 555-7762
Smyth John 555-8972
Sovan Tony 555-9958
Now I ask you. Aside from Dana, are we ever going to get ahold of the people we want to talk to if the phone book ordered its records this way? @=)
When ordering, you have to pick the column that is most important to you as the first order. Then each subsequent column is going to be ordered within the constraints of that first column. It's a sort of order grouping without really being grouping. The more columns you add to that grouping, the less likely the eventual results sorting will look like your mental image of how it should look like (not to mention the potential performance issues with sorting that many columns). So it's always a good idea to sort with as few columns as you can so that the results remain readable to you.
Does that help?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply