• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 732
  • Last Modified:

How to sort and existing table in SQL (SMMS 2005)

I currently have a table already in production that would like to add a sort to (GROUP BY field ASC).  The table currently is just sorted by the PK in an ascending order.  i would like to sort on another field.  

The table is used as a backend storage for a web app.  So by the sorting the table on the backend, I would like the usernames sorted on the front end web app.
0
szadroga
Asked:
szadroga
1 Solution
 
8080_DiverCommented:
Unless you change the primary key, which I would not advise, you cannot "sort" the table.  Also, "GROUP BY" is the wrong terminology, you should be thinking "ORDER BY".
So, essenitally, what yyou probably want to do is to use a query that looks something like the following SQL1.  Now if you want to provide some flexibility so that users can select a sort order (e.g. LastAccessDate instead of UserName), you could put your code into a stored procedure sort of like SQL_2.

SQL_1:
 
SELECT {list your columns here and don't use an asterisk}
FROM   {your table}
ORDER BY {whatever your columns are that you want the data "sorted by"}
 
 
 
SQL_2:
 
CREATE PROCEDURE dbo.SelectSortedUsers
   @SortOrder    VarChar(300)
AS
BEGIN
 
   SET NOCOUNT ON;
 
   DECLARE   @SQL_Statement   AS   VARCHAR(8000);
 
   SET @SQL_Statement = 'SELECT {list your columns here and don't use an asterisk}' +
                        'FROM   {your table} ' +
                        'ORDER BY ' + @SortOrder + ';';
 
   EXEC @SQL_Statement;
 
   SET NOCOUNT OFF
 
END

Open in new window

0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
My basic question here is why you need to store the Grouped and Sorted values in the table itself.
Instead trying to sort the table you can have the current Primary Key which sorts the data physically in that table and then whenever you fetch the records from the table, query using appropriate GROUP BY and Sortings.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now