SQL To Order Results In Predefined Order
I have a DB with items which can have lengths from 0 to 400 meter.
In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meter
How to build my SQL?
View Complete Forum Thread with Replies
Related Forum Messages:
Specify Order For Select Results, Order By: Help!
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table. My query would look something like this: Select leadershipName From Leadership Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows: Executive Board Members, Delegates, Grievance Chairs, and Negotiators My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC? Thanks, Zoop
View Replies !
Results In Order
I have the following query and I am wanting to get the results to be in order. Right now, it shows me the results by date, but the dates are out of order. How can I get it to give me the results by date in date order??? SELECT DISTINCT MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' 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'))) AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59')) AND DATEPART(weekday, CallDate) in (2,3,4,5,6)) Group By MDN, dateadd(day, datediff(day, 0, CallDate), 0) UNION SELECT DISTINCT MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' 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'))) AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59')) AND DATEPART(weekday, CallDate) in (2,3,4,5,6)) Group By MDN, dateadd(day, datediff(day, 0, CallDate), 0)
View Replies !
How To Order Results From DateName?
HiI want to get the dateName of everything but I don't know how to sort them now. Like I have this:DateName(month,TimeDateStamp) As TimeDateStampWhat gets the dateName now I want to sort these so that they are in order.How do I do this?
View Replies !
Top + Order By = Strange Results
Hi there,I'm a little bit confused here. I the TOP 1 function with ORDER BY DESC to get the last id in my table but it doesn't seems to work.Here's an example:SELECT TOP 1 ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID DESCSELECT ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID ASCSo basically the first SELECT should return the last id value of the second query but it doesn't.The first query gives me that: 60721And the second one gives me that:60680606816068360684606856068660718607196072060721610506112261124So as you can see my TOP 1 ID ORDER BY ID DESC should gave me this result: 61124Am I missing something or... ?Please help me this is aleready in function so I have to fix it ASAP.Thank you, Regards,OR-THO
View Replies !
Order / Rank Results
Hi, i want to create a report so that a list of the top 30 records are returned to the report user. In the report i want to have the records position in the list shown (ie the first row should have 1. and the second should be 2. right on down to the 30th having 30.) how do i achieve this please? many thanks FatherJack
View Replies !
Select Like And Order Results
Hello there, I have two tables I selecting name using like with %string% from the two tables but I need to order the result comes from the two table: 1- the exact match for the search string come first from the two table. 2- and the partial match comes last after the exact match. this is my DDL for the two tables : USE [Northwind] GO /****** Object: Table [dbo].[Person] Script Date: 04/25/2008 14:33:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Person]( [PersonID] [int] NULL, [Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF second table: USE [Northwind] GO /****** Object: Table [dbo].[Members] Script Date: 04/25/2008 14:33:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Members]( [MemberID] [int] NULL, [Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF and this my search query I have it in a stored Proc. select * from ( SELECT PersonID, Type, Name, email FROM Person WHERE (Name LIKE '%'@Name'%') union all SELECT PersonID, Type, Name, email From Members WHERE (Name Like '%'@Name'%' ) ) Y Order by Case[Name] when @Name Then 1 Else 2 End, Case[Name] when 'm' Then 1 Else 2 End Thank you for your time Sms
View Replies !
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
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 Thank you, Mark
View Replies !
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
Hey guys, i need to find out how can i add order items under a Purchase Order number. My table relationship is PurchaseOrder ->PurchaseOrderItem. below is a Stored Procedure that i have wrote in creating a PO: CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT) AS BEGIN 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? CREATE TRIGGER trgInsertPOItem ON PurchaseOrderItem FOR INSERT AS BEGIN 'What do i entered???' END RETURN help is needed asap! thanks!
View Replies !
Order Results By Date Not Working
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this: oct 2 oct 3 sep 13 sep 21 sep 22 sep 30 aug 3 aug 5 aug 16 the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help. also, can you not search here any more? i keep getting timeout errors.
View Replies !
Brain Teaser - Stagger The Order Of The Results
This one has been stumping me for several days. I can run a query thatreturns several different items from several different manufacturers,each with a ranking score. Each manufacturer can have any number ofitems:Item_Name Manufacturer rankItem 1 Manu_A 82Item 2 Manu_A 65Item 3 Manu_A 41Item 4 Manu_B 32Item 5 Manu_C 21Item 6 Manu_B 19However, I would like the records to be ordered so that the highestranking item is shown first, then the next highest item from adifferent manufacturer is shown second, then the next highest item froma third manufacturer is show, etc.:Item 1 Manu_A 82Item 4 Manu_B 32Item 5 Manu_C 21Item 2 Manu_A 65Item 6 Manu_B 19Item 3 Manu_A 41Does anyone have any thoughts on how to order the results in thisfashion?thanks,Matt Weiner
View Replies !
Ordering Results By Order Of The &"IN' Clause
Consider this SQL:SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1','value3')Simple enough, but is there anyway to specify that the result should beordered exactly like the "IN" clause states? So when this recordsetcomes back, I want it like this:my_field------------value2value1value3Possible?Deane
View Replies !
MS Access- TOP / Offset? I Need To Order Results As Pages
Hi, Sorry I have to post this here, but its sort of related to MS SQL anyway I'm running a PHP system with MS Access. I need to order results in pages. (For those who are familiar with MySqL and Postgresql - I need the equivalent to LIMIT/OFFSET in MS Access). I know I can "SELECT TOP 50" in my sql - so that solves the number of results per page. But what about page 2,3,4..etc.... how do I select results from an offset? There doesnt seem to be a solution for it Thanks!
View Replies !
3 Results From One Field - Show Levels In Right Order
Hi there We have a web application (database) that uses one field called Application and another called TicketType. When a user fills out a ticket they can choose up to 3 levels of this field. Eg Application, Application2, Application3 Eg TicketType, TicketType2, TicketType3 The extra two levels not being compulsory. I am using sql server 2005 // Reporting Services My query is as below: SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2], Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2], TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName FROM Ticket INNER JOIN TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID WHERE (Ticket.CreatedDate >= @StartDate) ORDER BY Ticket.TicketNumber End result looks like this: Application App 2 App 3 TicketType Type 2 Type 3 Software Internal Apps proACT SW Other Office Issues General Application Click Track server Alert (App) Service Network Other Network Fault Software Internal Apps Other User Account New Hardware Network HW Fault Application Click Track server Alert (App) Disk space Office Issues General proACT Configuration Deployment Software Server Software SharePoint SW Fault App Failure (Function) Software Server Software SharePoint SW Fault App Failure (Function) Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well. Can someone help please. Please ask if I haven't explained myself. thanks Dianne
View Replies !
Extract Distinct Information And Order The Results
Hi, MSSQL 2000 T-SQL I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row. I've attached a sample of DB data below. tran_nr ret_ref_no msg_type description 5111 12345 420 reversal 5112 12345 200 auths 5113 15236 200 auths 5114 46587 200 auths 5115 46587 420 reversal Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB. I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200. SELECT * FROM SAMPLE WHERE ret_ref_no in ( SELECT ret_ref_no FROM SAMPLE GROUP BY ret_ref_no HAVING COUNT(*) > 1 ) Results of query 5111 12345 420 reversal 5112 12345 200 auths 5114 46587 200 auths 5115 46587 420 reversal If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it. Regards Deceptive
View Replies !
Order By Cluase Cause Wrong Results To Be Returned.
I have the follow table. /****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[deletethisTempOut]( [ThemeName] [varchar](60) NULL, [intLocationCount] [int] NULL, [dblRepValueA] [float] NULL, [dblRepValueB] [float] NULL, [dblRepValueC] [float] NULL, [dblRepValueD] [float] NULL, [dblTotalRepValue] [float] NULL, [dblLimit1] [float] NULL, [dblLimit2] [float] NULL, [dblLimit3] [float] NULL, [dblLimit4] [float] NULL, [dblTotalLimit] [float] NULL, [fltEmployeecount] [float] NULL, [intAreaLevel1] [tinyint] NOT NULL, [strFullName] [varchar](13) NOT NULL, [strAreaLevel2] [varchar](20) NOT NULL, [strAreaLevel3] [varchar](20) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF If I use the following SQL: SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3, dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3 FROM deletethisTempOut ORDER BY strAreaLevel2, strAreaLevel3 GET Following correct results: Adair 284 899989594 0 574857716 190479902 1665327212 0 0 0 0 1665327212 0 1 United States 1 1 IF I use the following SQL I get the wrong results: SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3, dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3 FROM deletethisTempOut ORDER BY ThemeName WRONG results: Adair 74 81733110 0 49616018 24671651 156020779 50510500 0 0 0 203870779 0 1 United States 50 1 Adair 437 1468698657 0 495479839 353202768 2317381264 12984266 0 0 0 2315676030 0 1 United States 25 1 Adair 1813 20309722045 0 6597005374 4253819645 31160547064 43636703 0 0 0 31135010742 0 1 United States 11 1 Adair 606 439581417 0 331746662 132240332 903568411 0 0 0 0 903568411 0 1 United States 45 1 Adair 236 350256381 0 524269553 504973831 1379499765 4080368 0 0 0 1380473415 0 1 United States 23 1etc.....
View Replies !
Best Way To Order Results Sequentially Starting From Somewhere In The Middle
I'm working with SQL Server 2005, and I'm trying to sort the results based on a user selected letter. Say the user selects 'D' to filter his results. I'd like to return the results starting from D followed by E, F, G...Z, A, B, C. What I'm getting is the results for the D entries at the top of the result set, followed by A, B, C, E...Z. A solution comes to mind that would be very long and db intensive, by querying on 'like 'D', followed by like 'E', followed by like 'F', etc, but I'm sure that there is a much more efficient way to do this. Below is the code that I'm using now. ' where @SortString = 'd' and @Test is a temp Table BEGIN Insert into @Test Select CompanyName,ContactId, CompanyId from vContacts where CompanyName like @SortString +'%' Order by CompanyName Insert into @Test Select CompanyName,ContactId, CompanyId from vContacts where CompanyName not like @SortString +'%' Order by CompanyName END Thanks in advance for your help
View Replies !
SQL7: Order Of Values In IN Clause Affects Results
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 Replies !
Find Order By Date Range Or Order Id
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
View Replies !
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 !
Default Sort Order When Order By Column Value Are All The Same
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?
View Replies !
Default Sort Order When The Order By Column Value Are All The Same
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?
View Replies !
Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name. Product: Microsoft SQL Server Express Edition Version: 9.00.1399.06 Server Collation: SQL_Latin1_General_CP1_CI_AS for example, create table test_sort ( description varchar(75) ); insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons'); then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75)); select * from test_sort order by cast( description as nvarchar(75)); Resultset1 ---------- Non-A Non-O Noni Nons Resultset2 ---------- Non-A Noni Non-O Nons Any ideas?
View Replies !
Order By Clause In View Doesn't Order.
I have created view by jaoining two table and have order by clause. The sql generated is as follows SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth If I run sql the results are displayed in proper order but the view only order by first item in order by clause. Has somebody experience same thing? How to fix this issue? Thanks,
View Replies !
Invalid Results For Order By On Select Query Against Table Variable
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set. DECLARE @tblCustomRange AS TABLE ( RecordID INTEGER IDENTITY(1,1), RangeMonth INTEGER, RangeDay INTEGER ) DECLARE @Month INTEGER DECLARE @Day INTEGER -- Initialize month and day variables. SET @Month = 8 SET @Day = 11 -- Insert records into the table variable. INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (1,2) INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (1,27) INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (6,10) INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (9,22) INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (12,16) -- Select everything from the table variable ordering the results by month, day in -- descending order SELECT * FROM @tblCustomRange WHERE (RangeMonth < @Month) OR (RangeMonth = @Month AND RangeDay <= @Day) ORDER BY RangeMonth, RangeDay DESC I am getting the following resultset: RecordID RangeMonth RangeDay ----------- ----------- ----------- 2 1 27 1 1 2 3 6 10 I am expecting the following resultset: RecordID RangeMonth RangeDay ----------- ----------- ----------- 3 6 10 2 1 27 1 1 2
View Replies !
Order ID For Latest Order For Every Customer
Hi! For the Orders table (let's assume for the Northwind database), I'm trying to get the order id of the latest order for every customer. That means that the result should be one record per customer and that would display CustomerID and OrderID. Any ideas? Thanks, Assaf
View Replies !
In-Order/Level Order Etc. Traversal Using CTE
Hi, I have some hierarchical data in a table. Say for example: Parent Child ------------------------ NULL 1 1 2 1 3 2 4 2 5 3 6 3 7 5 8 5 9 7 10 7 11 11 12 11 13 Now I want to be able to use CTE's to be able to traverse this tree in 1) level by level order 1,2,3,4,5,6,7,8,9,10.... 2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13... What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well. with Tree (id) as ( select id from WithTest where parent is null union all select a.id from Tree b join WithTest a on b.id = a.parent ) select * from Tree Any ideas? Thanks.
View Replies !
How To Load A Unicode File Into The Database In The Same Order As The File Order
The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View Replies !
Force The &"ORDER BY&" To Be In Ascending Order??
I noticed the StockDate is not sorted in proper order, like ascending order... Code: select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd... The sample data is MM/DD/YYYY... So, how do I get it ordered propery by Year, Month then Day??
View Replies !
Sql Order By
Hi all, I had one question on sql statement. I had a table with a field named severity. The field severity will either consist of Minor, Moderate or Severe. How can I construct an sql statement whereby the severity will be order as Severe follow by Moderate and Minor. Thanks
View Replies !
WHERE + ORDER BY ?
Hello I am not sure of the correct syntax. I know that the first part works: ****************************** SELECT Extn, Domain_Name, Price FROM Domains_DB **************************** I am trying to add a WHERE clause is equal to com and an ORDER BY assending order. I have tried all sort of combinations, where am I going wrong with the following: SELECT Extn, Domain_Name, Price FROM Domains_DB [WHERE Extn = com [ORDER BY Domain_Name ASC ]] Thanks. Lynn
View Replies !
ORDER BY
hi, i' ve Drop Down List with sorted catagory and Data Grid that cange according to selected item in drop down list ... i need to send the selected item as value to SELECT statment, so i 've send (option) as a value "SELECT [userstory].* FROM [userstory] WHERE ([userstory].[rel_id] = @rel_id) ORDER BY @options " but there is an error: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name
View Replies !
Order By
Hi everyone,I have a select statement of the form SELECT * FROM temp ORDER BY timeI have a scalar function ConvertToMinutes that takes in varchar and returns int, is there any way to do something like this SELECT * FROM temp ORDER BY ConvertToMinutes(time). I tried doing this and it doesn't work (it tells me ConvertToMinutes is not a built-in function). Please guide me as to how I would accomplish this. Thanks in advance.P.S. Clarification: I am trying to order the table temp by the value returned by the function ConvertToMinutes on the coloumn time.
View Replies !
Order By??
Hi All, I have a question in sql.... How can i sort a select statement depending on nvarchar not on Int ??My select statement is : " select * from table1 order by st_name asc"can anyone help me? thanks a lot
View Replies !
Order By
How would i add order by to the syntax below:"Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &" and " & Box2i have tried adding it after Box2 but it doesnt work.Any ideas?
View Replies !
Is It Necessary To Order Again?
I have a function that returns a table:CREATE FUNCTION dbo.Example(@Param int)RETURNS @Tbl TABLE (Field1 int,Field2 int) ASBEGININSERT @Tbl (Field1,Field2)SELECT FieldA,FieldB FROM DataTableWHERE FieldC = @ParamORDER BY FieldARETURNENDThe statement that populates the table orders the data. In orderto ensure the results are ordered that way, should the call to thefunction include an ordering? I.e., is this sufficientSELECT * FROM dbo.Example(17)or is this necessary? --SELECT * FROM dbo.Example(17) ORDER BY Field1Thanks!
View Replies !
Order By
i want to use an order by clause. my issue is that the values i have are alpha and i need the top of the order to be 'A', 'S', then what ever. how can i sort this column and have the top two being 'A's and 'S's???? thanks in advance e
View Replies !
Order By
Was playing around with the order by to try and understand how it works in a union query. So l took a query that l've written before and modified it. When l run this query l get the following error. ORDER BY items must appear in the select list if the statement contains a UNION operator. SELECT Distinct Loan_no AS Loan_no, Date_Issued AS Date_Issued, Store AS Store, Product AS Product, Capital_Amount AS Capital_Amount, Interest_Amount AS Interest_Amount, Total_Amount AS Total_Amount, Insurance_amount As Insurance_Amount, Admin_Fee AS Admin_Fee, User_Issued AS User_Issued, LoanBook AS Company, Status FROM Loan Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000' UNION SELECT NULL AS Loan_no, NULL AS Date_Issued, NULL AS Store, NULL AS Product, Sum(Capital_Amount) AS Capital_Amount, Sum(Interest_Amount) AS Interest_Amount, Sum(Total_Amount) AS Total_Amount, Sum(Insurance_amount) As Insurance_Amount, Sum(Admin_Fee) AS Admin_Fee, NULL AS User_Issued, NULL AS Company, NULL AS Status FROM Loan Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000' ORDER BY CASE WHEN Loan_no IS NULL THEN 0 ELSE 1 END , Date_Issued,
View Replies !
BCP Out With ORDER BY ??
I need to BCP out from a table and guarantee the "ORDER BY" with a key column. When using BCP, are the records guaranteed to be returned in a specific order? If not, how can I specify? (BOL says selecting without an ORDER BY clause cannot guarantee order of result set.)
View Replies !
Order By
Table has two columns id, theme select id, theme from content_theme order by theme brings following result set 38 Alphabet 97 Animals 0 Any Theme 98 Architecture 3 Artists' Lives & Work 92 Autumn/Fall 39 Awards & Honors 4 Being Your Best 40 Birthdays 41 Boats & Ships Is there a way to display 'Any theme' 1st in the set and than display other in order?
View Replies !
Order By
my table consists of a column with the following records; T1 T1.1 T1.2 T2 T3 .. .. T100 In my query if I am sorting (order by) this column, the results are; T1 T1.1 T1.2 T10 T11 T2 T21 ... You got the idea! How can I sort it the right way?
View Replies !
Need Help With Order By
i got a huge query, and some counting columns. i want to order by a column that have been counted. i have used AS in this query but it doesnt work. i know that its nothing wrong in this query excpet that order thing. i hope you guys can help me Code: String sql = "SELECT Top " + Integer.parseInt(tVisaValda.getText()) + " Kund.Idnummer,Kund.Namn, (SELECT Count(Kundorder.Kundid) FROM Kundorder WHERE Kundid = Kund.kundid) AS antKop, (SELECT sum((Salda_artiklar.Pris+((Salda_artiklar.momssats/100)*Salda_artiklar.Pris))*Salda_artiklar.Antal) FROM Kundorder INNER JOIN Salda_artiklar ON Kundorder.Orderid=Salda_artiklar.Orderid WHERE Kundid=Kund.Kundid) AS inkMoms, (SELECT Sum(Salda_artiklar.Pris*Salda_artiklar.Antal) FROM Kundorder INNER JOIN Salda_artiklar ON Kundorder.Orderid=Salda_artiklar.Orderid WHERE Kundid=Kund.Kundid) AS exkMoms, Salda_artiklar.Skickade_datum AS skickadeDatum, (SELECT sum(Salda_artiklar.Pris*Salda_artiklar.Antal) FROM Kundorder INNER JOIN Salda_artiklar ON Kundorder.Orderid=Salda_artiklar.Orderid WHERE Kundid=Kund.Kundid) AS genom " + "FROM ( Kund INNER JOIN Kundorder ON Kund.Kundid = Kundorder.Kundid) " + "INNER JOIN Salda_artiklar ON Kundorder.Orderid = Salda_artiklar.Orderid " + "WHERE Salda_artiklar.Skickade_datum BETWEEN '" + tIntervalll.getText() + "' AND '" + tIntervall2.getText() + "' " + "ORDER BY antKop;
View Replies !
Order By
Hello, How will you use the order by in a column that distinguish in each string? say; id | letter 0 | ABC 1 | ABD 2 | ABE "select letter from table order by letter asc" using this puts the value ABD on top than ABC
View Replies !
Order By
Hi, I need some help with this query I have a table which saves data for athletes Tblsports AthName SchoolId SchoolName CoachName Gender TestDate Total So there will be 1000 records of different schools who particapate What i want to display is the School Name Coach Name Total The way it has to be is find the top 3 athletes in each school add their total and then find the 3 schools who got the highest total and order them so the highest school is on top todd
View Replies !
T-SQL Help With Order By
In a stored procedure MS-SQL I am trying to write an order by expression that is a function of the incoming paramters. SO for example CREATE PROCEDURE test @sortby varchar(10) AS SELECT * FROM table WHERE condition ORDER BY @SORTBY Gives an error (1008) but the error message implies that you can use a variable in the order by expression. I have tried every combination I can think of with the variable as varchar = column name, as integer . I have also tried variations of Order by COL_Name(OBJECT_ID('item"), variable) and I ether get errors or no errors but no order either. Any leads appreciated. TIA Mike
View Replies !
Order By
Is there a way to order data how you want besides Ascending or Descending? Example: I have M-F I need to sort in order of Monday-Friday.
View Replies !
Help With ORDER BY 1,2,1,2
i have this table fname val ---------------------- aaaaa 2 aaaaa 1 bbbbb 2 bbbbb 1 ccccc 2 ccccc 1 ddddd 2 ddddd 1 how to roder BY FNAME + VAL like this 1,2,1,2,1,2 always 2 is above 1 TNX
View Replies !
Order By
how do i order a varchar column so i get the result 1,2,3,4,5,6,7,8,9,10,11,A,B instead of 1,10,11,2,3,4,5,6,7,8,9,A,B
View Replies !
PK Order
I have a compiled and deployed to our clients site application, which in one case does not use ORDER BY clause in a sql statement. The assumption has been that SQL Server will always return the rows in ascending order of the PK (it is a composite PK). However, on some SQL Server machines the order is returned correctly, on others - not. I suspect that the order is defined by the way the indexing structure is built behind the scenes. The process of deploying patches to the clients' sites is difficult and very long. I am wondering if there is some SQL Server setting (stored procedure), which will rebuild the indexing structure in a way so that it uses the ascending order of the PK columns. Thank you, Nikolay
View Replies !
ORDER BY WHERE?
Is it possible to write a select statement that will order results descending by most matches of a column value? eg. i have a table with an object.. including an id number.. a second table with comments on the object.. holds the id number as well. i want to return the object with the most comment rows first.
View Replies !
|
TRACKER
