| Author |
Topic |
|
chmilz
Starting Member
4 Posts |
 Posted - 2005-03-10 : 17:34:01
|
| Hello Everyone,I have a 3rd party application that populates a Table on a Webpage by pulling one record at a time from a Database.The problem is is that this Table sorts user's by a unique ID Number, instead of by name. This makes it very difficult when someone is looking for a specific user in a list of 200 and they are NOT sorted by name.The third party app does no sorting on it's own... it simply steps through each record in the table one by one.Since I am not allowed to alter the code of the application, what I was wondering is if it is possible to write a stored procedure that will change the sorting of the table in question to sort by the users first name instead of their unique ID's. I would also like to schedule this procedure to run once a day.Any help is greatly appreciated. Thanks to you all!Cheers,Curtis |
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-03-10 : 17:55:44
|
Lots of questions here, chmilz:Is the user first name in a column by itself?Are you sure you want to use first name instead of last name?(think of how many people have the same first name and different last names)If the app does no sorting, what is the table structure?The table is probably sorting on the ID as an index, and depending on the database structure, you may not be able to change this...You could, however, create a view and order it any way you like. How you connect it to your app is another story altogether..AndyWelcome aboard! There's never enough time to type code right, but always enough time for a hotfix... |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-10 : 17:56:37
|
| Anything that you could to fix this would be altering the 3rd party vendors database which I'm sure is not allowed by the contract that you have with them. So you could add a clustered index or modify the existing one if one exists so that the table is ordered without needing an ORDER BY, or you could modify their query to add an ORDER BY. Either way, you would be altering something that wouldn't be allowed by their contract.Tara |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-03-10 : 18:08:17
|
| That probably is the case, Tara...Sometimes a call to the vendor on something like this is not wasted;quite a few vendors will change sort orders for a user when possible AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
chmilz
Starting Member
4 Posts |
Posted - 2005-03-10 : 18:09:42
|
| Hey there you two!Thanks so much for the quick replies :). I will attempt to answer your questions as best as I can....Steam:Is the User First Name in a column on it's own..--Yes it is and, now that you mention it, it would be much better to sort by last name :)What is the table structure:The table has the following columns:FNAMELNAMEIDPASSWORDEMAILand No... the App does no sorting what-so-ever booooooo! When you return all the rows in SQL they are sorted by ID.Also, this database was orginally an Access database that was imported into the SQL database (the company supported use of SQL or Access and we decided on SQL)Tara:You are exactly right... our contract strictly prohibits Altering the source files of the application.Steam, is there anyway I could alter the index on the table? It has been a long time since I worked with SQL (4 years ago in school) and I really appreciate all of your help!Thanks again!Cheers,Curtis |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-10 : 18:11:05
|
quote:
Originally posted by chmilz
Steam, is there anyway I could alter the index on the table?
Yes but that would alter their schema which would be prevented by the contract. You'll need to get that approved by them.Tara |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-03-10 : 18:17:50
|
| oooooh yeah!Call the vendor and see if they will allow this. The change isn't the problem; getting boiled in oil for breach of contract is!AndyHey Tara, congrats on the engagement thing! Is that recent, or am I a bit behind (like that doesn't happen much!)?There's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-10 : 18:19:30
|
| It happened early December. And thanks!Tara |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-03-10 : 18:24:20
|
| SEE??!!Typical guy, I am. always a day or 60 behind...There's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
chmilz
Starting Member
4 Posts |
Posted - 2005-03-10 : 18:40:43
|
| Thanks a lot for your help you two! I am contacting our vendor to request permission to create a clustered index on the folder I require.Hopefully they don't see it as a big deal :)Cheers,Curtis |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-10 : 18:43:03
|
| You keep mentioning folders and files. Are you talking about MS SQL Server?Tara |
|
|
|
chmilz
Starting Member
4 Posts |
Posted - 2005-03-10 : 18:59:27
|
| Hi Tara,Sorry... brain cramp... it's been a long day :) I meant clustered index on the column in the table... :)Cheers,Curtis |
|
|
|
|
Sorting a Table using a Stored Proc.