Order By With String Values
May 27, 2008
Hi all,
I have table with id column of nvarchar..
Now,WHEN I RUN THIS QUERY,
select ID from <TABLE>,
IT RETURNS
erfq-1
erfq-10
erfq-100
erfq-1000
erfq-10000
erfq-1001
erfq-101
erfq-11
erfq-12
erfq-19
erfq-2
erfq-3
erfq-30
erfq-4
erfq-5
erfq-50
erfq-6
erfq-9
erfq-99
erfq-999
WHERE AS I NEED IT AS BELOW
erfq-1
erfq-2
erfq-3
erfq-4
erfq-5
erfq-6
erfq-9
erfq-10
erfq-11
erfq-12
erfq-19
erfq-30
erfq-50
erfq-99
erfq-100
erfq-101
erfq-999
erfq-1000
erfq-1001
erfq-10000
How Can I achive this one ?
Thanks in Advance
Dana
View 7 Replies
ADVERTISEMENT
Nov 9, 2000
Hi,
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
e.g.
Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -
At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?
Thanks
Dan
View 1 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
Jul 21, 2004
I'm having a little trouble with sql order by. I have a a column that has values of:
Gillite.Qutz
Tom.Goo
Jill.Hank
Joseph.Cool
I want to be order by their last name, which is after the period:
Joseph.Cool
Tom.Goo
Jill.Hank
Gillite.Qutz
How would I go about doing that? Thanks!
Sunny B.
View 2 Replies
View Related
Oct 26, 2007
hey everyone...
at the moment, I have 3 status types of items in my db... approved, pending & draft...
what im wanting to know is...
can i do a ORDER BY [status is Draft] so that any items where status (VARCHAR) is draft is at the top of the list..
I can't do alphabetical order...because d is inbetween A & P...
Cheers
View 1 Replies
View Related
Jul 20, 2005
Hi ng.I have a varchar field in my table, called Name.I wanna do a selection, which is ordered by whether this field is empty ornot.E.g. something like:SELECTUserIDORDER BYName <> '';- - -How can I accomplish this?TIA.Klaus.
View 4 Replies
View Related
Nov 24, 2006
Hi every body.
Can u tell me how to get the order values of the SQL query
Example.
My sqlstring ="Select * from tbl_Products"
And it returns 6 rows
And I want to get order values like this 1,2,3,4,5,6
I am a beginner.
Thanks a lots
View 1 Replies
View Related
Apr 20, 2006
I need to be able to order the results of a SELECT query by the order of a specific list of key IDs provided in the WHERE IN statement.
So my query looks like:
SELECT *
FROM TableName
WHERE KeyID IN (3,104,43,22,345)
ORDER BY ????
I need the results returned in the order provided in the IN list (3,104,43,22,345).
Thanks in advance!
pr0
View 5 Replies
View Related
Jul 20, 2005
How do I order a query by a date field ASC, but have any NULL valuesshow up last? i.e.7/1/20037/5/20037/10/2003<NULL><NULL>Any help will greatly be appreciated
View 3 Replies
View Related
Jul 20, 2005
I have a column named "LIST" in a table with strings like the following:151231-1002-02-1001151231-1001-02-1001151231-1002-02-1002151231-1003-02-1001etc....What I'd like to do is include an ORDER BY statement that splits thestring, so that the order would be by the second set of four numbers(i.e. between the first and second - marks), followed by the third setof two numbers, and then by the last set of four numbers.How would I do something like this?--Sugapablo - Join Bytes!http://www.sugapablo.com | ICQ: 902845
View 1 Replies
View Related
Mar 5, 2008
Hello all,The followinq qurey returns sometimes values of null to some of this columns, byK,byT,byD. the column F wil not contains any nulls, and 0 will be populated in it at any case of .Now, the problem is that when sorting out F the sort will not work when there is null parameters in byK because teh fact that a 0 values is greater then NULL value, and the sort of F will not take in considiration. So I guess the question is how can I sort NULL values and 0 values to be the same weight in the sort by command? SELECT A.gym_id as gym,s_id, week, gym_name, boxer, league, sum(points)
points,sum(byK)as byK, sum(byT) as byT,sum(byPoints) as byPoints ,
sum(byD) as byD, count(C.gym) as F
FROM A inner JOIN B ON A.gym_id = B.gym_id
left JOIN C ON A.gym_id = C.gym
WHERE (B.l_id = ?text
group by A.gym_id
order by points DESC,byK DESC,byT DESC, byPoints DESC, byD DESC,F ASC
View 3 Replies
View Related
Dec 14, 1999
Hi!
Has anyone experienced this problem?
Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values.
For example, the following query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,2,3,11,19)
go
returns a different result than this query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,3,11,2,19)
go
The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest:
AND columnA IN (19,11,3,2,1)
Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments...
Thanks!
View 2 Replies
View Related
Apr 3, 2006
Hello,
I am trying to extract from some strings like the following strings the number and order them by that number:
Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)
The problem I am having is that I already extracted the number using
Substring([field],[starting position],[lenght])
but the output seems to be in a string format, so the order is not in an ascending order.
Thanks for any suggestions.
View 6 Replies
View Related
Feb 19, 2008
Note the code below, running on the version noted.
I just found this today, figured I'd share. Not sure if it's a known bug or a "special" feature. The only difference between the two queries is the 3rd line, everything else is the same. Notice that the sort order changes, yet no errors or warnings are given. I assume that the table aliases are ignored for the order by, unless there are duplicate column names in the results.
I abstracted this from a bug I discovered in one of my apps today, where I have sales reps assigned to their clients with start and end dates. A while back, a developer asked me to format the dates without the time portion, and when I did so, I introduced the problem. I resolved it temporarily by ordering by convert(datetime, startdate) but I found it strange that the column alias match overrides the table alias attempted match in the order by. Another way to get around this would be to change the column aliases, then the sort order would be as desired, but I didn't want to have to change the app code for something so trivial.
My apologies if this is a duplicate.
@@version = Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
select convert(varchar(10), table_alias.startdate, 101) as startdate,
convert(varchar(10), table_alias.enddate, 101) as enddate
from
(
select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate
union
select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate
) as table_alias
order by table_alias.startdate
select convert(varchar(10), table_alias.startdate, 101) as startdate,
convert(varchar(10), table_alias.enddate, 101) as enddate,
table_alias.startdate, table_alias.enddate
from
(
select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate
union
select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate
) as table_alias
order by table_alias.startdate
View 4 Replies
View Related
Jul 23, 2005
I have the following insert statement in place:Insert WPHPayments(constituentID, constituentName, campaignYear, fundID, fundDescription, dateAndTimeEntered, amount)Select gt.constituentID, gt.constituentName, gt.campaignYear, gt.fundID, gt.fundDescription, gt.dateAndTimeEntered, gt.amountFrom GTPROCENTERFUNDPAYMENTEXTRACT gt, WPHExtractWhere gt.constituentID = WPHExtract.wph_constIDI want to insert all of the values that are in the GTPROCENTERFUNDPAYMENTEXTRACT table that have the same constituentID that as the records in the WPHExtract table. Am I just missing something becasue the syntax is showing that everytihing is correct however there is nothing comming back in the result set. Thanks in advance everyone. Regards,RB
View 1 Replies
View Related
Feb 20, 2007
Using a reporting services model/report builder we have two related tables:
- Fundings, (parent)
- Draws, (child)
Report Builder reports that subtract "Total Fundings.Amount", (which is SUM(FundingAmount)) from "Total Draw Amount", (which is SUM(DrawAmount)) to get a balance work as expected except when there are no Draw rows, in which case a NULL is returned. Obviously we want to convert NULL values of "Total Draw Amount" to zero so that when subtracted from "Total Fundings.Amount" the correct value is displayed. I've searched for a function similar to COALESCE (Transact-SQL) in report builder but found nothing.
Can anybody help me with this?
Thanks
Bruce
View 11 Replies
View Related
Sep 10, 2004
Hi,
I have a table in my database with several car types, and the order I want for that table is:
Car_typeA_1
Car_typeA_2
Car_typeA_3
Car_typeA_4
Other_Cars_typeA
Car_typeB_1
Car_typeB_2
Car_typeB_3
Other_Cars_typeB
Car_typeC_1
Car_typeC_2
Car_typeC_3
Car_typeC_4
Car_typeC_5
Other_Cars_typeC
...
This table is more or less always the same, but from time to time I want to add a new car type, for instance; Car_typeA_5, but this new type must be located under the last register, in the example under ‘Other_Cars_typeC’. So, now the order is wrong, and when I want to display these car types to a web form object, the items will appear wrong ordered.
My question is: To order the values(items) correctly, Where I have to do it? In the web page (ASP.NET) code behind, or somewhere in SQL Server (for example in the Stored Procedure that passes the value to the application)? Or maybe in the same database table..?
Thank you,
Cesar
View 7 Replies
View Related
Dec 10, 2004
Hello,
I am working with ASP.NET/VB and Microsoft SQL 2000 database.
I have a search form where keywords are submitted.
Consider I write the the keywords 'asp' and 'book'. The results page is called as follows: results.aspx?search=asp%20book
Then I use this script in results.aspx to put the keywords in a string:
Sub Page_Load(sender As Object, e As System.EventArgs)
Dim keywords() As String = Request.QueryString("search").Split(CChar(""))
End Sub
My table is set for FULL TEXT SEARCH.
Consider the SQL when I look for records containing 'asp' and 'book':
SELECT *
FROM dbo.documents
WHERE CONTAINS (*, 'ASP or BOOK')
This SQL looks only for these words. What I need is to look for records that contain the Keywords included in the string keywords().
Can you tell me how to access the string values in the SQL and use it?
Thanks,
Miguel
View 2 Replies
View Related
Jul 6, 2006
SQL neophyte hoping this is an easy answer...
I'm trying to obtain an average for a set of string values. "Huh?" (In an investment portfolio, some securities have ratings, e.g., AAA or Aa2 or BBB, etc., and to obtain an overall rating for the portfolio you have to find a way to average the string values from each individual security.) I figured I'd assign a numerical value to each, average, and convert back to string. My first attempts have been a little futile...can anyone help?
View 10 Replies
View Related
Oct 18, 2007
HI!
I do a select and concatinating the answers into a one column table @tab(string).
Is it possible to format these values like this:
GENAV DELAV TOTIP RES
12 myvalues www.notech.com 1
1 starthere 192.168.0.2 1
125 or here www.hereistomanychar.s 0
max 3 max 10 max 22 max 1
the first column max 3 characters the second column max 10 char...
I want to do this to get a good view of the data i the table @tab
Here is the select:
INSERT INTO @tab
SELECT Convert( varchar, GENAV)+ ' ' + Convert( varchar, DELAV)+ ' ' + Convert( varchar, TOTIP) ' ' + Convert( varchar, RES)
from dbo.MOVE
where DATE between @starttime and @theEndTime
With this select it looks like:
12 myvalues www.notech.com 1
1 starthere 192.168.0.2 1
125 or here www.hereistomanychar.s 0
This is very complicated to read!
View 6 Replies
View Related
Jan 29, 2008
I have a sql server table column that can contain the following possible values. 1. 766/IT 2. 777/HR3. 890/EG4. 012/AS5. Trainee6. Contractor 7. Others I want to write an SQL query grouped by this column, Trainee should be one group, Contractor should be another different group, Others should be another different group and then every thing else ( 766/IT ,777/HR ,890/EG, 012/AS) should be grouped together as one group. Think of it in terms of a pie chart with those groups.
View 4 Replies
View Related
Dec 30, 2003
This is a portion of my stored procedure in T-SQL
CREATE PROCEDURE GetPoll
(
@PollID INT,
@PollQuestion VARCHAR(1000) Output,
@PollOptions VARCHAR(1000) Output,
@PollPercentages DECIMAL Output
)
AS
SELECT @PollQuestion = (SELECT PollQuestion FROM Polls WHERE PollID = @PollID)
SELECT @PollOptions = (SELECT [Option] FROM PollOPtions WHERE PollID = @PollID)
*SELECT @PollPercentages = (SELECT [Option], COUNT([Option]) As Num_Votes FROM Votes WHERE PollID = 1 GROUP BY [Option])
The final part(*) of this SQL will return more than one value, so is it possible for me to return all that information in one varaible with one SELECT query?? Or would I need to use a loop and query a value at a time, and store it into a delimited string?
thx in advance...
View 3 Replies
View Related
Jun 14, 1999
I currently have a need to dynamically build an sql statement that always returns a single value when executed. The sql statement is always the same except the database name reference in the statment.
What I need is to be able to capture that value for later use in the procedure. Since this is a stored procedure, I can't use the "USE" statment to switch databases and I haven't been able to figure this out using the Execute statement. I can execute the string, but I can't capture the value.
I'm simply trying to execute the same set of sql statements in a stored procedure without hardcoding database names or build an identical stored procedure in all our databases.
Any help is appreciated.
View 2 Replies
View Related
Apr 8, 2004
I am not close to an sql server today and this question was posed to me. can someone hook me up?
" I want to query a column of values and place them into a single string seperated by commas" (as a function)
Table a
123
456
789
321
654
987
output
123,456,789,321,654,987
thanks in advance
View 14 Replies
View Related
Oct 25, 2006
How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?
View 10 Replies
View Related
Jun 5, 2014
I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.
The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)
EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"
The result would Be
column header US
with value Project/Prescription sale
next column header NL
with value Project/specificatie etc.
Here are table examples:
IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]
CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,
[Code] ....
View 9 Replies
View Related
Jul 29, 2015
Why can i get the numeric values from this string?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be different...
View 6 Replies
View Related
Jan 10, 2014
I have a program which feeds back information to a table within my database. The annoying thing is that only 1 of the table cells has all of the information I need so essentially I have a large string of data I need to retrieve values from.I retrieve my cell with a simple select details from alerts where ID = (alert number).The results I have are as follows:
[INFO] Dashboard Output Information:
[LastUpdated]
Time=09:59
[LastProcessed]
LastProcessed=*2911
Updated=09:59
[LastReceived]
LastReceived=#10
Updated=09:59
As you can see I have a list (its larger but this is a sample), the only thing I need are the values for each section so for last processed i want the 2911 so I can use it on my dashboard and the last received I want the 10 for that section.i have read about using substrings but am really not sure where to start and how I'd go about getting these values to then use elsewhere?
View 3 Replies
View Related
Apr 5, 2006
I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!
View 6 Replies
View Related
Nov 24, 2015
The data source is Fetch XML - so all the values are string.I need to sum the values but getting an error:
Warning 1 [rsAggregateOfNonNumericData] The Value expression for the textrun ‘Textbox25.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.I tried converting it to INT but it returns a large number which does not match what I'm expecting.
View 10 Replies
View Related
Dec 7, 2007
Any idea why this expression would not work in SSRS?
Based on a expression on a group textbox I get 0 records that match:
=Iif(Fields!ACCRUAL_CODE.Value <> "A", CountDistinct(Fields!LOAN_NBR.Value), 0)
I've evaluated in the proc & there should be a count of 29 records.
I found a work around by using this in the stored proc & I get my record count:
ACCRUAL_CODE = CASE WHEN BASE.ACCRUAL_CODE = 'A' THEN 0 ELSE 1 END
and changed the expression in SSRS to this & it works:
=sum(Fields!ACCRUAL_CODE.Value)
Is this a known issue with strings? From searching on this site I see that string evaluations are widely used so I do not see why it wouldn't work. I've also see this when filtering the dataset on anything that is a character. Any suggestions?
View 3 Replies
View Related
