Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sorting


Sorting

Author
Message
dougal_vins
dougal_vins
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24280 Visits: 37987
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




Cool
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)
dougal_vins
dougal_vins
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24280 Visits: 37987
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.

Cool
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)
dougal_vins
dougal_vins
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7826 Visits: 8757
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search