Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sorting Expand / Collapse
Author
Message
Posted Sunday, April 20, 2014 11:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 4:40 PM
Points: 3, Visits: 29
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 table(
EmployeeName varchar(max),
CustomerID varchar(max),
OrderID int,
OrderDate date,
OrderAmount money
)

Insert into @test(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

Post #1563344
Posted Sunday, April 20, 2014 11:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 23,077, Visits: 31,605
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1563346
Posted Sunday, April 20, 2014 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 4:40 PM
Points: 3, Visits: 29
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.
Post #1563363
Posted Sunday, April 20, 2014 4:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 23,077, Visits: 31,605
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1563364
Posted Monday, April 21, 2014 12:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 4:40 PM
Points: 3, Visits: 29
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.
Post #1563388
Posted Monday, April 21, 2014 5:43 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,133, Visits: 6,295
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?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1563440
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse