I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From Name_CatE
Where Education = 'yes'
Order By
LName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,
Here is my code
@syear nvarchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
In SQL 2000, when you generated an SQL script for a database, it was logical and the tables in the script were in alphabetical order. In SQL 2005 they are all mixed up. Am I missing something?
Using the scripting wizard in SQL Server 2005 database engine, I have been able to script all my DDL out to a flat file which is great; however, when I scripts for instance all views I would like to have the script in alphabetical order by view name, is there a value I can set to accomplish this?
Hi, I have a table whose Identifying column is not an integer but rather a manually entered id. (i.e. 106F, 106-09, G11 etc.) When sorted ascending, a G11 will come before a G2 in the list, 106-11 before 106-2, etc.I would like to insert a new column in the database or use some kind of function when sorting the database to ensure that the list in returned in the proper order. Any ideas?Much appreciated...
I have been discussing with some coworkers whether or not it makes sense to invest the time to alphabetize the column names in our tables (aside from the PK and possibly FK's that could be listed first). My reasoning for doing so would make it much easier to scan the list of columns in a table that I was not familiar with to see if it contained a particular column (i.e. meeting_id). I was just wondering if this is common at all in our industry for new DB design (I realize why legacy systems would not be ordered in this way). I remember seeing MSFT designed their tables this way when Site Server first came out.
An argument was made that when you add a new column you would insert it into the appropriate location and in order to make this happen, EP needs to create a temp table, move the data and consequently lock the table until the data has been transferred. While this is correct, I do not see this negative as outweighing the positive experience achieved by scanning a list of fields in a table in a more orderly fashion.
i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.
I am trying to determine how I can select only the rows with alphabetical characters in that column.
I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.
i am building a shopping cart. I want to update the UNIT_IN_STOCK column in database after order have been submitted. i want to subtract the quanity value from the order made from the UNIT_IN_STOCK column in database. how would the sql statement be like?? i tried this but it didnt work. any suggestions??
CREATE PROCEDURE update_Products_By_name (
@ProductName varchar, @UnitInStock int
) AS
UPDATE Products SET UnitInStock=(UnitInStock-@UnitInStock) WHERE ProductName = @ProductName GO
When I click on the 'Create Date' column in Stored Procedures to sort the SPs by date, the SPs do get rearranged but not in the expected order. Is there something I am doing wrong or is this a feature? I am running MSSQL 7.0 on NT4.0 SP5.
I have an issue while display the result in the required order. How to get the required output.
Code :
USE tempdb GO
IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL DROP TABLE #VersionFormat_tbl CREATE TABLE #VersionFormat_tbl ( [FormatID] [smallint] NOT NULL, [Description] [varchar](50) NULL,
[Code] ....
Present output : fileExtension FormatID Description fileExtension versionFormatTypeId txt 1 Text txt 1 html 2 HTML html 1 xml 3 XML xml 1 pdf 4 PDF pdf 1 xls 5 Excel xls 1 doc 6 Word doc 1
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list. Example ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions? Thanks - Mark
I have table1 with orderID and demographic info. Table2 with orderID and items. I would like to have a results display like this: OrderIDDemographicInfo Item1Item2Item3....ect One line per order. When I do a join I displaying all items in different rows.
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name. CREATE TABLE Names (FirstName NVARCHAR (50), LastName NVARCHAR (50)); I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character. I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding. I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View. I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters. Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also? Thanks
int codePage = 950; StringBuilder message = new StringBuilder(); Encoding targetEncoding = Encoding.GetEncoding(codePage); byte[] encodedChars= targetEncoding.GetBytes(str); . message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':"); for (int i = 0; i < encodedChars.Length; i++) { message.Append("Byte " + i + ": " + encodedChars); }
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars)); Console.Writeline(message.ToString());
Apologies if this has been done before, but I couldn't find a completed example. If anyone has time, I'd love to see some improvements...
Where's fribble when you need him? /* function: numeric_order
arguments: @numeric_string - a string of mixed alpha and numeric values @max_digits- the maximum length of digits to compare
description: Function numeric_order creates an orderable string based on the "numeric" value of @numeric_string which can be ordered alphabetically.
Ideally the strings should really be broken up into constituent parts and ordered properly, but occasionally you come across data where it's just not worth the while.
eg select title from regulations order by title returns:
NOTE: because the original strings are mixed, the user may include alphas between digits which are to be sorted alphabetically, which means the string must keep all alpha parts of the original string as is.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[regulations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[regulations] --go
create table regulations (id int identity(1,1), title varchar(200)) --go
insert into regulations (title) select 'Regulation 1(a) section 3' insert into regulations (title) select 'Regulation 11 section 100(b)' insert into regulations (title) select 'Regulation 11 section 2(c)(iii)' insert into regulations (title) select 'Regulation 2 section 1' insert into regulations (title) select 'Regulation 21 section 3 (b)' --go
select title as [Incorrectly Ordered] from regulations order by title --go
select title as [Correctly Ordered] from regulations order by dbo.numeric_order(title, 10) --go
drop table [dbo].[regulations] --go
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[numeric_order]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[numeric_order] go
create function [dbo].[numeric_order]( @numeric_string as varchar(1000), @max_digits as int) returns varchar(8000) as begin declare @return varchar(8000)
declare @part varchar(1000) declare @digit_position int declare @rest varchar(1000) declare @non_digit_position int declare @numeric_term varchar(1000) declare @after varchar(1000) declare @between varchar(1000) declare @first_time int declare @digits varchar(100)
--create a string of zeros equal in length to max number of digits of enclosed numeric values set @digits = replace(space(@max_digits),' ','0')
--handle length issues --worst case scenario is a number every second character multiplied by padlen < 8000 --which would potentially add len(@numeric_string)/2 * padlen characters - so subtract this from the string set @part = left(@numeric_string, ((len(@numeric_string)/2) * @max_digits))
--starting values set @non_digit_position = 0 set @first_time = 1 set @return = ''
--loop while not at end of string while ((@non_digit_position > 0 or @first_time > 0) and (len(@part) > 0)) begin --if there are digits in the string set @digit_position = patindex('%[0-9]%', @part) if @digit_position > 0 begin --get the part of the string after the first digit set @rest = substring(@part, patindex('%[0-9]%', @part) + 1, len(@part)-patindex('%[0-9]%', @part) + 1) set @non_digit_position = patindex('%[^0-9]%',@rest)
--extract the string of digits set @numeric_term = case when @non_digit_position > 0 then substring(@part, @digit_position, @non_digit_position) else substring(@part, @digit_position, len(@part) - @digit_position + 1) end
--keep track of the rest of the string after and between the digits set @after = substring(@part, @digit_position + len(@numeric_term), len(@part) - @digit_position + @non_digit_position) set @between = '' + substring(@part, 1, @digit_position - 1)
--build return string set @return = @return + @between + right(@digits + @numeric_term, @max_digits) end else begin --no more digits, just add back the rest of the original string set @return = @return + @part set @after = '' end
--iterate set @first_time = 0 set @part = @after end
return @return
end go
-- I hope that when I die someone will say of me "That guy sure owed me a lot of money"
When I veiw the exexcution/progress results for a package they show in order of the name of the task, is there anyway to see this tree view in order of the execution?
I am JOINing a Table with a View, and the View can have multiple rows relating to the join condition(i.e. My view as 4 columns, and can look like the following: A B C D A E F G B H I J I want this to look like - A B C D E F G B H I J I want one row, but not one column, I need to maintain each value in its own column, withinin a single row. Doesn't matter to me if that transition happens in my View or as the outcome of my Join. I've been looking at the PIVOT command, but that appears to only work with an aggrgrate function. I don't want to calculate/aggreagte anything, just combine my multiple rows in one. This is my VIEW stmt - SELECT [PKG ID] AS PKG_ID, [APPR GRP NAME], [USRID OF APPR], [APPR STATUS]FROM dbo.pkgapproversWHERE ([APPR STATUS] = 'approved') AND ([APPR GRP TYPE] = 'E') This is my Join stmt - set @sql = N'SELECT DISTINCT [PKG ID] AS PKG_ID, [ELM ACT] AS Action, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment, [USRID OF APPR] FROM [PkgAction] FULL JOIN vw_PkgApprovals ON PkgAction.[PKG ID] = vw_PkgApprovals.[PKG_ID] WHERE 1=1'
Would like to know if it is possiblefor any of you to suggest how to conditionally diaplay a Column based on the values of the other columns. Here's my query SELECT [ProductionDay] ,DATEPART(wk,StartTime) AS WeekCount ,(AVG([RNTDuration])) AS AvgRNT ,MIN([RNTDuration]) As LeastRNT ,MAX([RNTDuration]) As WorstRNT ,dbo.fn_MinimumRNT_sp([ProgramNo],[Machine],[ProductionDay]) AS BestRNT ,dbo.fn_MinimumRNT(SubAssemblyNo,[Machine]) AS BestRNT_txt ,SUM([LoadingDuration]) AS LoadingTime ,[ProgramNo] AS ProgramNo ,COUNT(RNTID) AS PartsCount ,[SubAssemblyNo] ,[Sheetsize] FROM [RPMS].[dbo].[List_MachineShopRNT] where Machine = @iMachine and dateadd(month, datediff(month, 0, [StartTime]),0) = @dtMonthStartDate Group by DATEPART(wk,StartTime), [ProductionDay],[Machine],[ProgramNo],[SubAssemblyNo],[Sheetsize]
Here I would like to Run the function dbo.fn_MinimumRNT_sp or dbo.fn_MinimumRNT if the ProgramNo is 100
======================================================== I have one table store below information, and other one table is store staff name and phone number. how to display of table c data by new column and using the key staff_code1=staff_id or staff_code2= staff_id or staff_code3=staff_id ? ---------------------------------------------------------
Table A staff_code1,staff_code2,staff_code3
Table B staff name staff_id staff_phone -----------------------------------------------
Table C display in new column . staff name-1 code1 staff name-2 code2 , staff name-3 code3 peter id-01 susuan id-03 david id-05
Hi,I have a DataGrid whose DataSource is an SQLDataSource. This SQLDataSource references a SQL Server table with an Identity column, named Id. The SQLDataSource has generated an Update statement for me, but this only works if the Id column is selected for display in the DataGrid, and has Visible = True. If not, then the @original_Id parameter to the generated Update statement has a value of Nothing, and the Update has no effect. So is there a way to enable the Update statement to work, without the user having to see the Id column in the DataGrid? Many thanks,Keith.
My first ASP.NET/SQL project. I'm creating an Asset Management DB. I wish to view the Asset number, the full name of the user it's assigned to and the Make and Model of each record in the DB. However assets are split into different categories e.g. monitors, PCs, Preinters etc. and they are all in different tables. The SQL below displays the asset number, Name of person assigned and the model of the asset. SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model, PCModel.Model AS Expr1, PrinterModel.Model AS Expr2 FROM Hardware INNER JOIN [User] ON Hardware.UserID = [User].UserID INNER JOIN Model ON Hardware.ModelID = Model.ModelID LEFT OUTER JOIN MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID This outputs:- Asset number FullName Model Expr1 Expr2 00000 User Name Model NULL NULL 00001 User Name NULL Model NULL 00002 User Name NULL NULL Model However what i hope to acheive is output Model, Expr1, Expr2 into one column like so:- Asset number FullName Model 00000 User Name Model Can i do this in the SQL or do i have to do it in my ASP.NET (VB) Page? Using VSWD 2005 Ex Edition and SQL Server 2005 Ex Edition Thank you for your replies
