Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Display Database Column In Alphabetical Order


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

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?




View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Generate A Database Script In SQL 2005 In Alphabetical Order
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?



View Replies !   View Related
SQL Server 2005 Database Generating Script DDL In Alphabetical Order
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?


View Replies !   View Related
Sorting Column For Table - Not Alphabetical Or ID
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... 

View Replies !   View Related
DB Design - Alphabetical Column Names?
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.


View Replies !   View Related
Updating Database Column After Order Has Been Made
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


UPDATE Products
SET UnitInStock=(UnitInStock-@UnitInStock)
WHERE ProductName = @ProductName

View Replies !   View Related
Display Of A Result Set Without Order By
Hi all

If we don't specify any Order by clause, what is the default order of displaying the data .

Will SQL Server show the data in a consistent order?


View Replies !   View Related
Display Order In Enterprise Manager
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.

Thanks in advance,


View Replies !   View Related
Default Sort Order When Order By Column Value Are All The Same
We got a problem.
supposing we have a table like this:

aName string2 NOT NULL

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?

View Replies !   View Related
Default Sort Order When The Order By Column Value Are All The Same
We got a problem.
supposing we have a table like this:

aName string2 NOT NULL

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:
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?

View Replies !   View Related
Stored Procedure To Update A Display Order Field.
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.
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

View Replies !   View Related
How Do I Implement A Display Order Or Positional Algorithm In My Datagrid


I have created a website and i have a page called Investment choices where the user can either type in the data in a text box or there they can selectthem from a grid view(called Funds). The Typed data or the selected data goes in to another datagrid(called plans). and i want to implement a display order.

right nowwhen the user adds 6 numbers usingthe text box andsince i seperate them using commas. In Plans datagrid i get the numbers from 1-6. then after words if the user selects the from the Funds gridview it just its adds a number 1.

So how can i have the display order sequentially so if the user types 6 no's it should be 1- 6 and if he selects 2 funds from the gridview it should be 7 and 8. I have a field in the database which is called display order which is a integer. so do u think it makes sense as making that field as an identity field or how should i deal with it.

Any ideas will be appreciated.



View Replies !   View Related
Read Chinese Character From SQL(SQL Server 2005) Database Table Column And Display Chinese Character

I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
(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?

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));

View Replies !   View Related
Recordset's Order And Database's Physical Order?
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!

View Replies !   View Related
Alphabetical Ordering Of Numeric Strings
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?

@numeric_string - a string of mixed alpha and numeric values
@max_digits- the maximum length of digits to compare

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.

select title from regulations order by title

Regulation 1(a) section 3
Regulation 11 section 100(b)
Regulation 11 section 2(c)(iii)
Regulation 2 section 1
Regulation 21 section 3 (b)

select title from regulations order by dbo.numeric_order(title,10)

Regulation 1(a) section 3
Regulation 2 section 1
Regulation 11 section 2(c)(iii)
Regulation 11 section 100(b)
Regulation 21 section 3 (b)

which is the order most users would expect.

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.

expected output:
select dbo.numeric_order('Part 1(b) subsection 1',4)
Part 0001(b) subsection 0001

test data:
use test

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[regulations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[regulations]

create table regulations (id int identity(1,1), title varchar(200))

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)'

select title as [Incorrectly Ordered] from regulations order by title

select title as [Correctly Ordered] from regulations order by dbo.numeric_order(title, 10)

drop table [dbo].[regulations]


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]

create function [dbo].[numeric_order](
@numeric_string as varchar(1000),
@max_digits as int)
returns varchar(8000) as
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))
--if there are digits in the string
set @digit_position = patindex('%[0-9]%', @part)
if @digit_position > 0
--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)

--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)
--no more digits, just add back the rest of the original string
set @return = @return + @part
set @after = ''

set @first_time = 0
set @part = @after

return @return


I hope that when I die someone will say of me "That guy sure owed me a lot of money"

View Replies !   View Related
SSIS Package Execution Results Alphabetical
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?


Casey Smith

View Replies !   View Related
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
I want this to look like -
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'
Any thoughts....Thanks

View Replies !   View Related
Display Multiple Items On One Line Per Order (was &"Query&")
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.

View Replies !   View Related
How *NOT* To Display Header Column

1. This is what SET NOCOUNT ON does:

-- SET NOCOUNT to ON and no longer display the count message.
SELECT au_lname
FROM authors

2. Now, is it possible and what is the statement to use if I don't want to display
the header column? au_lname. Thanks.

View Replies !   View Related
How To Display Column Descriptions?

I am wracking my brain, trying to figure out how to return & display a column's description.

I work in Cold Fusion with SQL 2000 database.

My table name is "clients" and the column in question is "client_firstname"

The description of this column, as entered in Ent. Mgr, is "First Name"

If anyone can please help me, it would be very greatly appreciated!


Now for the details...

I am trying this:

<cfquery name="get_ext2" datasource="#application.dsn#">
objname As 'Column'
, value As 'Description'
('MS_Description', 'user', 'dbo', 'table','client', 'client_firstname', default)


... and it is returning this:

"" ""

In my debug output, the query result appears as such:

get_ext2 (Records=0, Time=16ms)
objname As 'Column'
, value As 'Description'
('MS_Description', 'user', 'dbo', 'table','client', 'client_firstname', default)




View Replies !   View Related
Combine 2 Column Into 1 In Display
I want to know how to combine 2 column in a table into 1 column to display it in a datagrid.
For example i have firstname and lastname in the table. I need to display as Name in datagrid where it have to combine firstname and lastname. How can i do that?

And i also want to know how to change the value to display it in datagrid. In my table the value is only '1' or '0'. Where i need to display as Active when it is '1', Unactive when it is '0'. Is this do in programming way?

Thanks in advance.

View Replies !   View Related
Filter On One Column But Display Another
I have a date column and a value column. I want to display the value column based on the last date. MAX (Date)

I tried using MAX for the date column but that displays the last date.

Any Ideas?

View Replies !   View Related
Conditional Display Of A Column
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
,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
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

Thank you

View Replies !   View Related
Display Column Description
I have this easy sql for a drop down.

select code_value, desc1
from dbo.market_area_code
where code_value in ('1', '2', '4')

How can I add something to display something like

if code_value = '1' then display 'Blue'
if code_value = '2' then display 'Orange'
if code_value = '4' then display 'Red'

View Replies !   View Related
Display Multiple Columns Into One Column
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
[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
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

View Replies !   View Related
Must I Display A Column In DataGrid To Reference It?
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.

View Replies !   View Related
How To Display Result Of Different Year In Different Column Instead Of Different Row?
I am trying to display component usage in January for the past year, if I want to display the year in different column, what should I do?
component_id   component_description   qty_used_on_2005_Jan   qty_used_on_2006_Jan
C58B0BDD       tape drive                             2                                              3
Currently I am using this sql:
select cast(year(date_complete) as varchar(10)) + ' Jan' as Year, component_id, component_description,sum(component_qty) as total_qty_used
from view_jobComponent
where month(date_complete) = 1group by component_id, component_description, cast(year(date_complete) as varchar(10)) + ' Jan'order by component_id, component_description
which I will get something like this:
Year          component_id   component_description   total_qty_used
2005 Jan    C58B0BDD       tape drive                             22006 Jan    C58B0BDD       tape drive                             3

View Replies !   View Related
Change Results In Column To Display Different
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.

Here goes..


View Replies !   View Related
How To Display Column From Inserted Table
Hi all,

I have created a trigger in SQLExpress which looking for any insertion to a specific table in a database:

Create Trigger Tg_Insert on [tablename]
FOR Insert
Select * FROM Inserted
Assume there is nothing wrong with this trigger as I have already checked the output with Query tool.
My question is how can I display the newly inserted data to the client? In my case, Console application.
I want to do something like Console.WriteLine("data from inserted table here");
How can I achieve this? FYI, I'm using VS2005 with sqlexpress 2005.
Thanks in advance.


View Replies !   View Related
Multi Column Display In A Report??

I have a table with a data cell which is printing top-to-down


I want it to print like

1 2 3 4 5 6 7

How to do this??

The cell is in a group detials sections. and I am using Reporting Services 2000 with VS.NET 2003

Thanks in Advance

View Replies !   View Related
How To Display 2 Columns Of Data In 1 Column?

How to display 2 columns of Data in 1 column?

If I use like this

Select Names, 'myData' from emp

The output is:
Name1 myData
Name2 myData
Name3 myData

But, I need to display like this


please advise

View Replies !   View Related
How To Display The Column Description In Query Analyser
Hello All.

I have created a table with short name as the file name but have entered the description in the column box (when creating a new table - see attached). I would like to show this description information in query analyser. Can this be done? I used sp_help <table name> but it doesn't show the description.

Please advise. Thank you.

Best regards

View Replies !   View Related
Hi there...

I have a matrix report within a list so it functions as a report with dynamic columns.

Matrix a:

a b c d e

x 1 2 49 12 <NEED SUBTOTAL across Columns for row x>

y6 2 4 59 <<NEED SUBTOTAL across Columns for row y>

TOTAL <need totals going down rows and need a grand total>

Remember the number of rows and columns are dynamic so I have NO CLUE how to add a column and display it if and only all the columns have been displayed ...sort of like a COLUMN and ROW footer for a matrix report.

Any help will be appreciated

thank you

Prashanth Prasanna

View Replies !   View Related
Order By 2 Column ?
i have i table tab1 and 2 columns date1<b> and <b>date2

i want order this cloumns but i have one thing

* if date1 < date2 then order by date2

* if date2 < date1 then order by date1

how i do it .?


View Replies !   View Related
How To Order A Column
To order a recordset I can simply do
"Select * From Table Order by ColumnName;"

But my need is different.

In a column I have the following content:

But "F" and "A" may exist more than once.

I must order the recordset always in the order

and not "A", "C", "F", "P".

Can anyone pls help me?

Thanks in advance.

View Replies !   View Related
Column Order
Does anyone know how to change column order of a table with data.

View Replies !   View Related
How To Combine Two Cloumns And Display As One Column In UI ? How To Write Query For This?
I have 2 Columns FirstName and LastName but i need to show it in UI as User Name ,that means i need to combine both First Name and Last name and display both as 1 field namely UserName ,How to query tht ? What shld i use?

View Replies !   View Related
Help Required On Mouse Over Display On Stacked Column Charts
Is there a way to get markers on the top of each graph of a stacked column chart?
Also i need to display a label when the mouse points over each of the marker, as well as each partition of the graph. Getting the tooltips to work might b a help but it is not working for me. I got a reply that enabling the mouse over in the report viewer might help, but i dont know how to enable

View Replies !   View Related
Display Column Names And Its Values Programatically From The Dataset

In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this:

@sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM
PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT'


So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code?


View Replies !   View Related
Column Chart Does Not Display Correct Data Values

I have a report that contains a table to display data in tabular format and a column chart. Both the report and chartare driven off the same dataset. There is one parameter called number of days that retrieves data for these many days. The data that gets displayed in the table is correct, but on graph is totally incorrect. The length of column bars is incorrect when compared to data values. I have followed the exact steps to create the chart.

Can someone please help as this is a production problem?

Thanks a lot.

View Replies !   View Related
Order By Column Alias
I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS
2 (
4 Id,
5 Name,
6 Created,
7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers,
9 FROM Projects p
10 )
12 FROM ProjectList
13 WHERE RowNumber BETWEEN 50 AND 60;

This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error:
Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'.
I read this in the documentation:
When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.
So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this

View Replies !   View Related
Does Column Order Really Matter?
Does column order really matter for Query Optimizer to pick index.Case 1: Say my CUSTOMER table has one composite index containing FirstName and LastName. FirstName exists prior than LastName. Does the column, FirstName and LastName, order matter to have Query Optimizer to utilize the index when I write WHERE clause in a SELECT statement?Statement 1:SELECT * FROM CUSTOMER WHERE FirstName = 'John' and LastName ='Smith'Statement 2:SELECT * FROM CUSTOMER WHERE LastName ='Smith' and FirstName = 'John' Will both statement 1 and 2 use the composite index or only statement 1?Case 2:Say my CUSTOMER has two single-column indexes. One index is on column FirstName. Another is on column LastName.For statement 1 and 2 above, which index will be picked by Query Optimizer or both? How does QO pick for index?I read couple book and some books say column order matter but some say no. Which one should I go with?  I'm kind of confused.  

View Replies !   View Related
Using The Same Column In Bother SUM And ORDER BY? How?
Hello,Using SQL 2005. Columns:ID, int (PK, auto-increment by 1)WorkHours, intName, varchar(100)I can't seem to get the following query to work. I want to return allNames and the sum of ALL work hours, in each row and order by eachINDIVIDUAL work hour:SELECT Name, SUM(WorkHours) as hFROM EmployersORDER BY WorkHours DESCIt seems that putting WorkHours in but the aggregate function and theORDER BY clause creates a problem.Thank you for your help!

View Replies !   View Related
Variable ORDER BY Column
I need to be able to pass a parameter to a stored procedure indicating which column to sort the outcome by. I cannot simply sort it by the passed variable (or I have the syntax wrong...). The sort can be anyone of eight columns and I need to do this in a fair few places on complex SELECT statements, so I am reluctant to use a case statement, which would make the sp rather large.

View Replies !   View Related
ORDER BY Any Column Problem
I am using SQL2000.

I have a SELECT statement in an SP that selects 10 fields, however, i want to be able to pass a variable to the SP to determine which field to ORDER BY.

Is there a way to do this ?

I've tried passing in one of the field names to a variable and then doing ORDER BY @OrderByThisColumn ...nope.
I've tried SETting a variable to the above @OrderByThisColumn ...nope.

Any ideas/clues would be a great help

The code...........

CREATE PROCEDURE usp_ShareHolders_Main_sel

@CompanyCode varchar(4),
@OrderByThisColumn varchar(30)


H.Fund_Man as Holders,
H.Shares as SharesHeld,
H.Share_Pric * H.Shares as Value,
H.Pcent as SharesOutstanding,
H.Shares - H.Shares as ShareChange,
C.Reg_Date as ReportDate,
'Register' as Source,
((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital as SectorWeightingPcent,
H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital) as OverUnderWeight,
(H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital)) * C.isc as SurplusDeficit

Citywatch_Company C
Inner Join
Citywatch_Holders H
C.Epic = H.Epic
Inner Join
Citywatch_Sector S
H.Sector = S.Sec_Code
C.Epic = @CompanyCode

ORDER BY <one of the select columns above>

Help me please someone

View Replies !   View Related
ORDER BY - Join Column
Hi, I am writing a small search engine.
There are two tables. The first one holds the search engine main index, the second one is link table.
I have the following query that retrieves results. I would like to sort the results by:
dbo.OCCURS2(LOWER(:query),se_links.anchor). se_links.anchor obviously comes from se_links table, so I get an error. Is it possible to done in one query?
I'm using MSSQL 2005. Thanks.
PS. Function OCCURS2 returns number of occurrences of one string in other.


id as Id,
uri as ElementUri,
size as Size,
modified_date as ModifiedDate,
title as Title,
text as Text,
dbo.OCCURS2(LOWER(:query),Title) as TitleOcc,
dbo.OCCURS2(LOWER(:query),Text) as BodyOcc
FROM se_index
WHERE (title LIKE :query) OR
(text LIKE :query) OR
(id IN
(SELECT se_links.target_index_id
se_index AS se_index_1 ON
se_links.target_index_id = AND
se_links.anchor LIKE :query))

View Replies !   View Related
How To Order In Month Column

table ab contain two column
no dispaymonth
1 April
2 jan
3 Mar

when i using order by in Month column it will return Alpha/- order(April,Mar,jun like that) but i need Jan,Feb,March order

need that Query

Advance thanks

View Replies !   View Related
Using Group By And Order By With The Same Column
Hi All,

Please let me know if both query fetch the same result

Select Col1, Col2, Col3, Col4 from Table1 Group By Col1, Col2, Col3, Col4
Order By Col1, Col2

Select Col1, Col2, Col3, Col4 from Table1
Order By Col1, Col2


View Replies !   View Related
A Column Has Been Specified More Than Once In The Order By List.

I have a function dbo.FIELDthat extracts a part of the value from a field. I want to get two pieces and order by them.

Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.

How can I do this?


These work:



View Replies !   View Related
How To Re-order The ID Column Of A Table?
I have a table that I want to re-order the ID column. The ID are not in order now due to some insertion and deletion. What are the steps to re-order the ID column?

Thanks in advance.

View Replies !   View Related

Copyright 2005-08, All rights reserved