Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number
Aug 21, 2007
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
View 7 Replies
ADVERTISEMENT
Feb 15, 2008
The query below at the bottom works, but when I try to add the line below (a third line/argument in the CASE statement) then I get an error. It is not possible to have a table name and a DESC (sort order) in the same CASE line/argument. Are there other ways to accomplish this, because I would like to sort with both "h.ObjectType" and "h.ObjectType DESC"? WHEN @SortBy = 2 THEN h.ObjectType DESC SELECT weight, ObjectText.ObjectId, ObjectText.ObjectType, ObjectText.Title, ObjectText.ShortText FROM @hits h INNER JOIN ObjectText ON h.id = ObjectText.ObjectId AND h.ObjectType = ObjectText.ObjectType WHERE ObjectText.LanguageCode = @LanguageCode ORDER BY CASE WHEN @SortBy = 0 THEN weight WHEN @SortBy = 1 THEN h.ObjectType END DESCRegards, Sigurd
View 4 Replies
View Related
Sep 24, 2015
I use Order By Used with Case Statement and may be this is the reason for which i am getting error
Windowed functions can only appear in the SELECT or ORDER BY clauses.
Here is my complete sql where i use Row_Number function for pagination with sorting.
DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1
[Code] ....
View 7 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Sep 9, 2015
I'm trying to set up a statement that gives me a field called 'BINNO' if the payor = Commercial. But, I have a few customers that don't have Commercial. They have a Payor of Grant or Part D. How would I set up a statement that looks for Commerical 1st...then Grant or Part D. I started with this
case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO
View 9 Replies
View Related
Jan 18, 2007
Hi All,
I have this query :
select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then col99
end
when i execute the above query it gives me the following error message.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'col99'.
Thanks in advance.
Thanvi.
View 5 Replies
View Related
Feb 22, 2008
I have been trying to get the following Selects to work using a case expression in the order by section.
I know I can easily separate out the two statements but I want to do a select using the case statement below ;
however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.
If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.
Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.
Thanks
J
declare @date
set @date = '2001'
select internalID from section_data_v3
union
select internalID from section_data_v4
order by
case when (@date = '2001') then internalID end desc
View 5 Replies
View Related
Sep 21, 2007
Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.
How do I keep it in DESC order for viewing? Here's the statement:
SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC
View 4 Replies
View Related
Aug 5, 2007
hi, i have headers at the top of my list and would like for people to click the pubs link and it orders the pubs alphabetically descending and when they click again to ascend also to do this with towns and addresses and postcodes?!
can someone please help?
J x
View 2 Replies
View Related
Apr 15, 2003
Does anyone have a general rule or guide on when to use this SQL 2000 option when creating indexes? I was thinking generally on nonclustered indexes where the column would be unique and incremental and usually filtered on by range and often used in the order by clause. Such as columns of datetime or integers datatypes. Thanks.
View 1 Replies
View Related
Apr 14, 2007
Hi all,I have a SQL statement that allows paging and dynamic sorting of thecolumns, but what I can't figure out without making the SQL a dynamicstring and executing it, or duplicating the SQL statement between anIF and ELSE statement.Following is the SQL statement;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_search]@search VARCHAR( 80 ), @startRow INT = 1, @endRow INT = NULL, @postcode AS CHAR( 4 ) = NULL, @suburb AS VARCHAR( 40 ) = NULL, @stateIdentity AS TINYINT = NULL, @fromLatitude AS REAL = NULL -- latitude the user is located in, @fromLongitude AS REAL = NULL -- longitude the user is located in, @sort TINYINT = 1ASBEGINSET NOCOUNT ON;DECLARE @calculateDistance BIT;SET @calculateDistance = 0;-- get the longitude and latitude if requiredIF ( NOT @postcode IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_postalcodeWHERE(postalcode = @postcode)SET @calculateDistance = 1ENDELSE IF ( NOT @suburb IS NULL AND NOT @stateIdentity IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_localityWHERE(locality = @suburb)AND(stateIdentity = @stateIdentity)SET @calculateDistance = 1END/*ELSE IF ( @fromLatitude IS NULL AND @fromLongitude IS NULL )BEGINRAISERROR( 'You need to pass a valid combination to this storedprocedure, example: postcode or suburb and state identity or longitudeand latitude', 18, 1 );END*/SELECT D1.[row], D1.[totalRecordCount], D1.[classifiedIdentity], D1.[title], D1.[summary], D1.[price], D1.[locality], D1.[state], D1.[postcode], D1.[addedLast24], D1.[dateStamp], D1.[t2Rank], D1.[t3Rank], D1.[tRank], D1.[distance], F.[originalName], F.[extension], F.[uniqueName]FROM(-- derived tableSELECT ROW_NUMBER() OVER ( ORDER BY CASE @sort WHEN 0 THENCAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) )WHEN 1 THEN C.title WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN ( C.locality + ' ' +C.state ) WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC ) AS row, COUNT( * ) OVER() AS totalRecordCount, C.[classifiedIdentity], C.[title], C.[summary], C.[price], C.[locality], C.[state], C.[postcode], CASE WHEN ( C.[dateStamp] >= DATEADD( day, -1, GETDATE() ) )THEN 1 ELSE 0 END AS addedLast24, C.[dateStamp]/* , t1.RANK AS t1Rank */, t2.RANK AS t2Rank, t3.RANK AS t3Rank, /* COALESCE( t1.RANK, 0 ) + */ COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS tRank, CASE @calculateDistance WHEN 1 THEN CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) ELSE 0 END AS distanceFROM [tbl_classified] AS CINNER JOINtbl_locality LONC.localityIdentity = L.localityIdentity/* LEFT OUTER JOINCONTAINSTABLE( tbl_category, title, @keyword ) ASt1ON FT_TBL.categoryIdentity = t1.[KEY] */LEFT OUTER JOINCONTAINSTABLE( tbl_classified, title, @search ) ASt2ON C.classifiedIdentity = t2.[KEY]LEFT OUTER JOINCONTAINSTABLE( tbl_classified, description,@search ) AS t3ON C.classifiedIdentity = t3.[KEY]WHERE ( /* COALESCE( t1.RANK, 0 ) + */COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) ) != 0) AS D1LEFT OUTER JOINtbl_classified_file CFOND1.classifiedIdentity = CF.classifiedIdentityLEFT OUTER JOINtbl_file FONF.fileIdentity = CF.fileIdentityWHERE( row >= @startRow )AND( @endRow IS NULL OR row <= @endRow )ENDThe part I'm having trouble with is making the sort order in thefollowing line dynamicORDER BY CASE @sort WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) ) WHEN 1 THEN C.title WHEN 2 THENCAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude,@fromLongitude, L.latitude, L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN( C.locality + ' ' + C.state ) WHEN 4 THEN CAST( C.price ASCHAR( 10 ) ) END ASCany help would be greatly apprecaited.Thanks
View 1 Replies
View Related
Mar 15, 2008
I am using sql statement like SELECT CREATEDBY,FIRSTNAME,BUSINESS,NOTES,NOTESDATE FROM BUSINESS ORDER BY NOTESDATE DESC, CREATEDBY ASC
But NotesDate is sorting descending order, but only sorting based on the date and month not on year
Please help me
View 4 Replies
View Related
Apr 2, 2008
I've been using
ROW_NUMBER() OVER(ORDER BY @@langid)
in cases where row numbering is needed but order is irrelevant.
Any problems using @@langid in this way?
View 1 Replies
View Related
Dec 7, 2004
I've this Stored procedure on a SQLserver 2000 SP3:
SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @squadra
this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
---------------------------
OK
---------------------------
An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??
View 2 Replies
View Related
May 20, 2004
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:
ie,
SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3
etc.
The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".
Regards,
Dave
View 5 Replies
View Related
Apr 9, 2014
Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE
case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'
View 1 Replies
View Related
Aug 17, 2007
I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View 9 Replies
View Related
Sep 3, 2007
Hi,
Code Snippet
This is difficult to explain in words, but the following code outlines what I am trying to do:
with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)
select * from myTableWithRowNum
Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?
View 4 Replies
View Related
Sep 21, 2006
I am trying to determine the next available order id using the method below. It works provided the table has a record in it. If it doesn't I get the error "Input string was not in a correct format." I am certain that it is because the query is returning a value of NULL. How can get around that or check for the NULL value?' Establish data connection...Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))'Determine order id number...Dim order_id As IntegerDim strSQL As StringstrSQL = "Select MAX(order_id) from mkt_order"Dim sqlCmd As New SqlCommand(strSQL, sqlConn)Dim sqlDA As New SqlDataAdapter(sqlCmd)Dim sqlDS As New DataSetsqlDA.Fill(sqlDS, "item")If sqlDS.Tables(0).Rows.Count <> 0 Thenorder_id = Convert.ToInt32(sqlDS.Tables(0).Rows(0)(0).ToString()) + 1Elseorder_id = 1End If
View 1 Replies
View Related
May 17, 2007
I'm working on a sproc that determines the next order id for a specified customer. The table has
custid int,
ordernum varchar(10)
Data is:
1000, 1000-001
1000, 1000-002
1001, 1001-001
1000, 1000-003
I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet).
I honestly don't know where to begin.
Can someone please help?
View 6 Replies
View Related
Oct 17, 2006
Hi.
I have a VB.NET function that returns an array of Integers.
Say, FunArray = [2, 3, 5, 8, 6, 23, 1, 10, 20 , 4, 54]
One characteristic of the array is that no two numbers
repeat - it reflects the IDs of my Users table. And, that
it is not ascending or descending.
What I would like to know is how do I sort my query in
the order of the integers in this array? Ideally, I
would like to use ORDER BY for this query.
thanks in advance.
View 2 Replies
View Related
Nov 23, 2004
Does anyone have a script that analyzes primary and foreign key relationships of tables and produces a suggested load order based upon dependancies?
Thanks,
Fred.
View 4 Replies
View Related
Feb 4, 2008
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
View 4 Replies
View Related
Nov 5, 2007
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
Thanks,
Jason
View 1 Replies
View Related
Feb 28, 2008
HI,
Thanks in advance for taking your time to read this post.
I am trying to write a SQL query using MS SQL 2005 that will read the value of a field and tell if it is alpha or numeric. I have tried the following but it does not work:
select field1 from table1 where left(field1,2)='[0-9]'
select field1 from table1 where isnumber(left(field1,2) tried with a =1 at the end and without and =1 at the end
the goal is to read through a field and format it so if a field looks like this 12xxx111xx I can change it to look like 12-xxx-111-xx.
Any help is greatly apprecaited
View 4 Replies
View Related
Jun 8, 2007
I am currently trying to find a way in which I can determine if a column in a Select clause is Japanese. The column currently supports English and Japanese Kanjis and other kanas. Is there a way to determine if this column is not English or if it is Japanese without physically looking at it.?
Thanks .... Chris
View 1 Replies
View Related
May 10, 2007
Is there a way to setup a field so that if it equals 'Yes' then it highlights the row. And if its 'No' it leaves it in plain text.
If this possible?
View 4 Replies
View Related
Dec 22, 2006
this is my query="SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' ANDtype='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"My SortKey can be NULL. Here's the output I am getting:(the || is to denote sortkey column)37542 Tape 1 ||37544 Tape 2 ||37819 Symphony1 ||37820 Symphony2 ||37821 Symphony3 ||37828 Symphony ||60962 Test ||61570 New Test Record |Africa|61572 Test 3 |Africa 1|63186 Music for Strings |Brazil|I want use Sortkey when it is not null. desired output:61570 New Test Record |Africa|61572 Test 3 |Africa 1|63186 Music for Strings |Brazil|37542 Tape 1 ||37544 Tape 2 ||37819 Symphony1 ||37820 Symphony2 ||37821 Symphony3 ||37828 Symphony ||60962 Test ||
View 10 Replies
View Related
Oct 21, 2014
I have several tables a varbinary column in a database. They have names like CSB_BLOB or OBJECT_BLOB. Now I am having intermittent success with getting the data out.
For example this query returns readable text from this data.
0x46726F6D3A20226465616E6E6167726.....etc --data as stored in the column
SELECT CAST(CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM OBJECT_BLOB
However this column has the following query results.
0x0001000000FFFFFFFF01000000000000000C....etc. --data as stored in column
--this query returns empty result
SELECT (CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM CSB_STATUS_LOG
--this query returns no change???
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CSB_BLOB, 2), 2) FROM CSB_STATUS_LOG
0001000000FFFFFFFF01000000000000000C....etc
Obviously there is a difference between the two but I am not educated enough to interpret this difference. What do I need to learn / read so I can look at the data in one of these BLOB columns and know how to convert it to something meaningful?
Something like:
1. Try to cast as varchar to see if it is text.
2. Turn into a byte array and see if it is a jpg
3. Turn into a byte array and see if it is a pdf
4. Convert it to hex and then cast as varchar
5. etc....
View 3 Replies
View Related
Aug 13, 2014
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
View 1 Replies
View Related
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Oct 31, 2013
It will be part of the stored proc, but for now I couldn't even get it running in ssms. It will be two parameters/variables, one for order by column name and other for order by direction, i.e. desc or asc.I have tried following three ways, but none is working:
(1)
order by case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Desc' then A_ID end desc
case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Asc' then A_ID end asc
(2)
order by case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Desc' then A_ID desc end
case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Asc' then A_ID asc end
(3)
ORDER BY CASE @Sort_by when '[A_ID]' then [A_ID] end
Case @Sort_Dir when 'Desc' then desc end
View 3 Replies
View Related