Author |
Topic |
AllenMcAfee
Starting Member
9 Posts |
Posted - 2013-01-27 : 15:58:01
|
I have a table I need to sort by 2 columns. The way I out put it in my program, one column is a checkbox so it is stored in an int field with 1's and 0's (default value of 0 and default to unchecked), and the other is a datetime field (also with a default value, of a large date and time). If the checkbox field is checked I need all those to be sorted to the bottom of the records and then everything else to the sorted by the datetime field in ascending order. So the default dates that haven't been changed would be a the bottom and the dates more recent at the top (I would prefer the checked records to also be sorted by the date time field but that is less important).When I was asking about how to do this through the programming language (C++ in QT) I was told a much more efficient way would be to do it with a table View. I am using MS SQL Server 2005 if that matters. I don't really know very much about SQL programming so when I tried to make the view by going into Databases->theDatabse->Views->NewView->and added in the table. I tried to tell it to order by the one column then the other, then I tried switching which column was 1st and also tried using sort by but kept getting errors. Can anyone help me with how I am supposed to actually make the view to do this please? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 16:51:19
|
That information is incorrect. You cannot order the data in a view. (You could, in older versions, you could fake it, but not reliable, so don't do it). Instead, what you should do is add an order by clause at the very end of the select query that you are using to retrieve the data. The order by clause should be like this:ORDER BY YourCheckBoxColumn ASC, YourDatetimeColumn ASC |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-27 : 16:51:33
|
Unfortunately, you cannot do a parameterized view in SQL Server. Some possible options are using a stored procedure or a table-valued function to return the data. With each of these options you can do a dynamic type sort. We'd need to see what fields in the table and what parameters you are using to get you an appropriate ORDER BY clause.Jim Everyday I learn something that somebody else already knew |
|
|
AllenMcAfee
Starting Member
9 Posts |
Posted - 2013-01-27 : 17:10:33
|
James K I right clicked my table and selected "Script table as" and then selected "SELECT To" and then "New Query Editor Window" which I assume is what you meant by a SELECT Query. But as I said I don't have much experience which SQL so let me know if I am wrong. I added in the ORDER BY clause like you had shown and when I pressed execute it seemed to work correctly. My next question is what do you mean by "the select query that you are using to retrieve the data" because the language I am writing in as a set table function for my TableModel but when I set that to the query it didn't work. This could be cause of where it saved or maybe I need to use a different function after set table to get that query I'm not sure. jimf my fields are called [Below the line] (the checkable one) and [Service Date and Time] (the datetime field) and I'm not sure what you mean by parameters. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 17:16:16
|
In the tool you are using, do you have an option to use a Stored Procedure or a Query instead of a TableModel as the data source? |
|
|
AllenMcAfee
Starting Member
9 Posts |
Posted - 2013-01-27 : 17:25:43
|
Yeah I think I can use a Query Model. Which I am trying now |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 17:30:05
|
If you have the option of using a Stored Procedure that would be even better, for a number of reasons. If you have that option, create a stored procedure (from a query window in SQL Server Management Studio), something like this:CREATE PROCEDURE dbo.GetYourDataASSELECT col1, col2, col3, YourCheckBoxColumn, YourDatetimeColumnFROM YourTableORDER BY YourCheckBoxColumn ASC, YourDatetimeColumn ASCGO Then, you can use the name GetYourData (in this example) as the stored procedure name to supply to your data source. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-27 : 18:20:33
|
If that's a your doing, a view will sufficeCREATE VIEW dbo.GetYourDataASSELECT TOP 100PERCENT col1, col2, col3, YourCheckBoxColumn, YourDatetimeColumnFROM YourTableORDER BY YourCheckBoxColumn ASC, YourDatetimeColumn ASCJimEveryday I learn something that somebody else already knew |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 18:28:44
|
I am weary about it, Jim. This MSDN page advises against relying on the order by clause in views http://msdn.microsoft.com/en-us/library/ms187956(v=sql.90).aspx |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-27 : 18:37:26
|
I just learned something! But then he doesn't need a view or a sproc even. I think we need to hear more from the OP and exactly what it is he trying to do. My original impression was that he is trying to change the sort order based on a checkbox value.JimEveryday I learn something that somebody else already knew |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 18:57:24
|
I recall reading somewhere that Microsoft tries very hard and goes to great lengths to keep existing functionality to avoid breaking existing programs, but that this was one of those cases where the behavior was changed from SQL 2000 to SQL 2005. In SQL 2000 you could rely on order by in views, but SQL 2005 and later you could not. |
|
|
AllenMcAfee
Starting Member
9 Posts |
Posted - 2013-01-28 : 09:12:11
|
James to create a stored procedure do I do that just by clicking "New Query" and make it in there? Also is GetYourData the name of the procedure or the .dbo that already exists? It looks like the name of the procedure but I just want to make sure. Jim as far as learning more about what exactly I am trying to do. I basically need all the records sorted by the datetime field and then also if the checkable field is checked those records need to be moved to the bottom. I would prefer the records that are checked to also be sorted by the datetime field but if not I can deal with that. Also I don't care which order this is done in as long as it outputs correctly. Does that give you enough information or do you need to know more? If so let me know what information would be helpful to you |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 09:17:42
|
Allen, yes - you would click the new query window, which should bring up a new blank query window. Paste the code to that window. Be sure to add the 2 lines shown below at the very top:USE YourDatabaseNameHereGO YourDatabaseNameHere is whatever is the name of your database.GetYourData is a random name that I picked. You can change it to whatever would make sense for you. When you run the script, it is creating an object - a stored procedure - in your database. After you run the script, you can see that it has created the object by navigating down to the Stored Procedures section in the object explorer window in SSMS. Once you create it, it is there forever for you to use. You can ALTER it if you need to make changes, DROP it if you want to get rid of it for some reason etc. |
|
|
AllenMcAfee
Starting Member
9 Posts |
Posted - 2013-01-28 : 10:35:08
|
Ok I created it, nothing showed up like when I created the select query, but it said "Command(s) completed successfully." so I assume it worked correctly. You said that I then use the name such as GetYourData "as the stored procedure name to supply to your data source" how do I do that? and then I guess I will have to call this in my program as a query? And thank you for all the help y'all have given me so far. I would of never been able to figure any of this stuff out in time without y'all's help. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 11:14:10
|
When you create the stored procedure, you are only creating the object; so no result set would be returned. But now if you were to run the query from a SSMS query window ( exec dbo.GetYourData ), that should return the results correctly, and correctly ordered.In your client program, you would need to choose the option to run a stored procedure (as opposed to table view or a adhoc query) to execute the stored procedure. Are you using ADO.Net or one of the .Net facilities to connect to the database? |
|
|
AllenMcAfee
Starting Member
9 Posts |
Posted - 2013-01-28 : 11:52:53
|
Umm I'm not exactly sure. I'm using C++ in the Framework Qt connecting to MS SQL with an ODBC driver. That's about as much as I know off the top of my head. Do you know how I can find out which I am using? I asked on a Qt forum but am waiting for a response. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 12:34:21
|
I am not familiar with QT, Allen. I would expect though, that there would be some options to select what kind of query you are running - typically table-direct, query, or stored procedure are the options that you may see. Look at the place where you changed it from table view to query to see if there are other options. Or you may want to ask QT support forums or consult their documentation. |
|
|
AllenMcAfee
Starting Member
9 Posts |
Posted - 2013-01-28 : 14:06:19
|
Yeah it looks like I'll have to just call it in similar to a query like query.exec("{CALL StoredProcedure}")Will the stored procedure allow for updating information to the table and adding and deleting rows and all that? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 14:10:51
|
The stored procedure that we were testing is just for selecting some data. We can write similar stored procedures for insert/update/delete etc, or for doing anything else for that matter. |
|
|
AllenMcAfee
Starting Member
9 Posts |
Posted - 2013-01-28 : 14:29:19
|
Awesome, Yeah I would need it to allow inserting new rows, updating information, and deleting rows. And I think that's all |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-28 : 15:57:03
|
You will find several examples and tutorials on the web - here is one for inserting: http://www.mssqltips.com/sqlservertutorial/2519/insert-stored-procedure-in-sql-server/ |
|
|
Next Page
|