I have list of sort options say Relevance, Date Posted, Salary and Job Location. I want to ORDER the search results with the options given above. Relevance, Salary and JobLocation in ascending order and Date Posted in Descending order. I tried adding ORDER BY to the below statement using case but its not working out. And ORDER BY doesn't accept variables as well. Can I use ORDER BY or Is their any other way handle this.Thanks in Advance.SELECT J.* FROM JobDetails AS J JOIN(SELECT DISTINCT J1.JobID FROM JobDetails AS J1inner join dbo.Split1(@list, ' ') AS SON J1.JobPosition LIKE '%' + S.Data + '%') J1 ON J1.JobID = J.JobIDSavvy
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.
Hi Ladies and gentlement,i have a column as below in table in my sql server 2000Cavity (column name)2A2B2A3A3C2C3B1A1Bi select them and bind it to a datagrid as below:strsql="select Cavity from tlb_Cav order by Cavity DESC"actually i wish to display them in order of their alphabet but the statement above unable to make it... can any1 show me some guard on this so tat it looks like below:1A2A2A3A1B2B3B2C3Csorry if this sound stupi*...but i really no idea except i seperate them into 2 columns...Regardslife's Ng
HelloCan anybody tell me the correct MS SQL statement to display names: Where each name displayed is starting with a certain letter of the alphabet only. For example:The list should show names starting with the letter A (what must I change in my statement below) SELECT LastN, FirstN, TelFROM People_DBWHERE LastN = 'A' ORDER BY LastN ASCLast NameAdams,
I have the following query and for some reason when I try to run it, it tells me there is an incorrect syntax near the 'union' statement. Can you tell me what is wrong with this?
SELECT MDN, DATEPART(d,CallDate) as CallDate, DATEPART(hh,CallDate) as Hour, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN UNION SELECT MDN, DATEPART(d,CallDate) as CallDate, DATEPART(hh,CallDate) as Hour, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN
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
I'm trying to write a UNION statement with 2 selects and can't figure out for the life of me how to do it.
select top 10 * from tblusers ORDER BY dateJoined UNION select top 10 * from tblusers ORDER BY lastLogin
The union works fine without the order by's, but I really need the order by's in there and I don't understand why its so difficult to do. Surely there must be some other strategy?
i have the following union statement, which works like a charm:SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel ORDER BY [SortDate]
now i want to ad a limit to this statement, but i can't get it working. i have tried this:SELECT * FROM (SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel) ORDER BY [SortDate] LIMIT 2 but i keep getting an error message saying "Incorrect syntax near the keyword "ORDER". what am i doing wrong?
HiI've done a search and found plenty on ORDER BY problems, but mineshopefully a syntax error.This is a test segment from my stored procedure.DECLARE @Name varchar(50), @SortType IntSET @Name = "canal lock"SET @SortType = 8SELECT dbo.ite_Item.ite_IDFROM dbo.ite_Item LEFT OUTER JOINdbo.iti_ItemImages ONdbo.ite_Item.ite_ID = dbo.iti_ItemImages.iti_LinkToItemWHERE iti_ItemImages.iti_LinkToPhotoType=1AND ite_Name LIKE '%' + @Name + '%'ORDER BYCASEWHEN @SortType = 3 THEN dbo.ite_Item.ite_DateWHEN @SortType = 5 THEN dbo.ite_Item.ite_ViewsWHEN @SortType = 7 THEN dbo.ite_Item.ite_DescriptionEND ASC,CASEWHEN @SortType = 2 THEN dbo.ite_Item.ite_NameWHEN @SortType = 4 THEN dbo.ite_Item.ite_DateWHEN @SortType = 6 THEN dbo.ite_Item.ite_ViewsWHEN @SortType = 8 THEN dbo.ite_Item.ite_DescriptionEND DESC,CASEWHEN @SortType <> 2 THEN dbo.ite_Item.ite_NameEND ASCI get various errors:@SortType = 2. This is a Name field varchar(50)Syntax error converting the varchar value 'Canal Lock' to a column ofdata type smallint.I only have one smallint col and that is the ite_Views column. Why isit trying to convert a varchar(50) col?@SortType = 3. This is a ISO date field nchar(8)The conversion of the nvarchar value '20030909' overflowed an INT2column. Use a larger integer column.Why?@SortType = 7, 8. These are description fields varchar(5000)Syntax error converting the varchar value '<snip>' to a column of datatype smallint.Again, why?All other sort types are fine.What I'm thinking is that when the ORDER BY code is run, it does someconversion but what?Many thanks in advance if anyone has any ideas?Sam
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
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'.
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:
ALTER VIEW dbo.SELECT_PP_END AS SELECT TOP 100 PERCENT
PP_PERIOD_ID,
CONVERT(VARCHAR, PP_END_DATE, 101) AS PP FROM dbo.PP_PERIODS ORDER BY PP_END_DATE DESC
The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help
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
Curious if I have the code below as an example and I execute this code does sql execute from top to bottom? And does the Update run and complete before the delete occurs? Or does SQL execute the update and delete in parallel?
I have to Select Order, Order Details and Order Status
Order Status is determined from Order Stage as follows:
If, at least one order detail line(from Order Details and Related Order details table) is approved, that Order status=Approved.
For the example, Order Status of Order ID=2, is Approved based on order status for order details lines 3(from table 2) and order details ID 1 and 2 (from table 3)
How to combined order stage from table 2 and table 3 and then compute order status.
The stored procedure, below, results in this error when I try to compile...
Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'.
However the select statement itself runs perfectly well as a query, no errors.
The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.
What gives with this?
Thanks in advance R.
The code:
Code Snippet
-- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80);
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! );
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM ... ... ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index]
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.
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
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,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!
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?
