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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
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 !
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 !
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 !
Select Top And Order By
If I return top 25 with an order by, will it get the whole result then take top 25 and order it or will it just return the first 25 it finds and order it. select top 25 c.firstname, c.lastname, o.units, o.partnum from customer c inner join orders o on c.key = o.key where o.units > 500 order by partnum
View Replies !
Order By With Select Into
I have a Microsoft SQL Server 7.0.I wrote a sql command that creates a temporary table with a ORDER BYclause.When a execute a SELECT on this temporary table sometimes the result isok, but sometimes is not ordered. I didn´t see anything like that. Anyclue?Is there any kind of limits with temporary tables ? Because the commandthat creates the temporary table is working and the rsults is alwaysordered. But when I create a table with it, sometimes the table is notordered.Paulo*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
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 Do I Use Order By When I Use Select Distinct.
Hi I have a query which returns some rows.. what happens if i use a select distinct instead of a select.. this is my sproc DECLARE @Counter TABLE( PlanId int, FundId int, ClientFundName varchar(110), DisplayOrder int IDENTITY(1,1), IsDefault bit, IsPortfolioFundOnly bit ) INSERT INTO @Counter ( PlanId, FundId, ClientFundName, IsDefault, IsPortfolioFundOnly ) SELECT 5923, f.FundId, d.FundName, CASE WHEN d.FundDefault IS NULL THEN 0 ELSE 1 END, CASE WHEN Lower(p.FundType) = 'modfundonly' THEN 1 ELSE 0 END FROM PlanDetail d INNER JOIN Statements..Fund f ON d.CUSIP = f.CUSIP OR d.Ticker = f.Ticker OR d.Ticker = f.ClientFundId OR d.CUSIP = f.ClientFundId -- Do an internal join on the PlanDetail table to get the value of the FundType to derive whether --fund can only be chosen as part of a portfolio. LEFT JOIN PlanDetail p ON d.FundName = p.FundName AND d.PortfolioName = p.PortfolioName WHERE d.PlanNumber IS NOT NULL AND p.PortFundPercent IS NULL GROUP BY f.FundId, d.FundName, d.FundDefault, --d.PlanNumber, --d.Cusip, -- d.Ticker, --d.RowNumber, p.FundType ORDER BY Min(d.PlanNumber), Min(d.RowNumber) any help will be appreciated. Thanks Karen
View Replies !
UNION: Select Order
i have 2 selects:select * from view_veiculos where nome_marc like '%fiat%' and ano='2001'union select * from view_veiculos where nome_marc like '%fiat%'when i execute it on sql server, i get the following results:id 1 _______ ano 2004id 2 _______ ano 2001the row with ano 2004 is before the row with ano 2001the problem is that id like it to be ordered following the select order, which means that 2001 should be displayed before 2004,like that:id 1 _______ ano 2001id 2 _______ ano 2004all the results from the first select from the query need to be placed before the results from the second query.how can i make it ?thanks for all
View Replies !
Select Top Alters Order
If i have two identical queries, with exception of top criteriaSelect top 1 * from photosSelect top 8 * from photoswhy does altering the top critieria alter the order of the returnedresults?
View Replies !
Order By In A INSERT INTO..SELECT
I have the following basic statements being executed:Create a temp table, #TempPagingInsert Into #TempPaging (Col1, Col2)Select Col1, Col2 From SomeOtherTable Order By Col2, Col1Select * from #TempPagingI can't provide a reproduceable scenario right now without making thisinto a 200K post, so I'm hoping someone will know what the issue is.. Ican't find anything regarding this in BOL.Basically the order is off a little bit every now and then when thereare large amounts of data, like more than a couple hundred rows. Isthere something I need to do to guarantee the originally selectedorder?This is very important that it be in order from the original selectstatement as this is for paging. Adding an order by in the secondselect from the temp table will not fix the problem. In this particularinstance that I have reproduced we are using SQL 2005 but have alsoseen this on SQL 2000 servers. I had previously asked this question asI was using a SELECT INTO statement, but now we are manually creatingthe temp table (Pain in the ass) and still having the same issue. Bestcase scenario is for it to work for a SELECT INTO.Any ideas?
View Replies !
Select Parameter Order
Which is more efficientWhere NonindexedColumn=x and IndexedColumn=yorWhere IndexedColumn=y and NonindexedColumn=xor does matter? Will the optimiser work it out?(I'm building the SQL string on the fly in the fron-end)
View Replies !
Tricky SELECT And ORDER BY
Dear GroupI wonder whether you can push me in a direction on how to design thefollowing statement. I'm looking for a SELECT with some tricky ORDERBY.The database table looks like this:MenuID TabText SubTabID TabOrderID------- ----------- ----------- -----------1 Main 0 12 Cars 0 23 Boats 0 34 Planes 0 45 Pick-Ups 2 16 Campers 2 2The result should look like this:MainCarsPick-UpsCampersBoatsPlanesNotice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' andmust appear in the result directly following 'Cars'.In more detail:'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabIDvalue of an item identifies to what top-level category a subcategorybelongs.The TabOrderID specifies in what order the items should be sorted,e.g. 'Pick-Ups' comes first and 'Campers' second.Thanks very much for your help & efforts!Martin
View Replies !
SELECT DISTINCT W/ORDER BY
I get the "ORDER BY items must appear in the select list if SELECT DISTINCT is specified. SELECT DISTINCT pdm.Account, pdm.Customer FROM dbo.Demands pdm LEFT OUTER JOIN dbo.Tickets rct ON pdm.Account = rct.Account WHERE pdm.Code IN (66, 51) ORDER BY pdm.TransactionDate DESC Is there any way to make the ORDER BY work in this case?
View Replies !
INSERT INTO As SELECT With ORDER BY
Hi, This may sound like a dumb question, but I need to be certain of the answer. If I have a query like this: INSERT INTO table1 (col1, col2, col3, ... ) SELECT col4, col5, col6, ... FROM table2 ORDER BY col7, col8, col9, ... and table1 has an identity column that increments by 1 each time, am I gauranteed that the records inserted into table1 will always be inserted in the order as specified in the ORDER BY clause and hence the increasing identity column in table1 will reflect the same order as that of the ORDER BY clause? Seems like it should be the case but I need to be sure. Thanks, Peter
View Replies !
INSERT-SELECT Depending On The Select:ed Order
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part. However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak? Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).
View Replies !
Select At Random Order From Database
Hi, I need to select few items from sql database.I know for ORDER BY, but I need those items to be mixed in random order every time when they are returned from database. Those are the same items every time, just randomly mixed. Can i do it with SQL, or I have to find another way (e.g. to mix them after sql returns them)?
View Replies !
ORDER BY Columns In SELECT List?
According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?
View Replies !
Bug In SELECT TOP (100) PERCENT With ORDER BY In SQLExpress?
I have a problem with the following query in SQLExpress: SELECT TOP (100) PERCENT ClientSurname, ClientName FROM dbo.Client ORDER BY ClientSurname, ClientName The query returns always assorted data ignoring the ORDER BY keyword, no matter if the query is invoked directly from Management Studio Express CTP as a View, Table-valued function or called from an Access ADP project. The result is always assorted.¨ Now an interesting thing is that the syntax below returns always an expected order: SELECT TOP (99) PERCENT … SELECT TOP 10000 … Am I missing something or is it a bug in SQLExpress?
View Replies !
SELECT DISTINCT Type ORDER BY
I am trying to display items from a table that have a type. For each condition there are between 7 and 10 types. I am looping through each type and displaying all items in that type. I am using DISTINCT to pull the types and count them, so I know how many there are and how times to loop. My problem is that DISTINCT is ordering the types alphabetically! I want them in the same order they went into the table. I tried adding ORDER BY primaryID, but got an error that said I also had to select primaryID as well as type, so now I am selecting every item that fits the condition and not just the DISTINCT types! Is there any way to make it order by column_position? I am using SQL 2K.
View Replies !
Select Records By Order Of Insertion???
I need to create a temp table with an identity column based on the contents of a physical table (CONTACTS) that has no key or unique way to identify which record was inserted first, I can query CONTACTS with no ORDER clause and everything is in the order it was inserted. However, when I create the temp table with a newid() as ID or IDENTITY(1,1) as ID, the duplicate rows as they existsed in the physical table are not inserted into the temp table in the same order. Is it possible to select records from a physical table into a temp table in the same order while creating a unique field? Here is how they exist in the physical table: CLIENTID CHANGEDATE NEWVALUE ------------ ----------------------- ---------------------------------------------------------------- C0HTRA200005 2007-11-19 21:11:57.000 NULL C0HTRA200005 2007-11-19 21:11:57.000 NULL C0HTRA200005 2007-11-19 21:11:57.000 Acquisition C0HTRA200005 2007-11-19 21:11:57.000 Acquisition C0HTRA200005 2007-11-19 21:11:57.000 Acquisition C0HTRA200005 2007-11-19 21:11:57.000 Acquisition C0HTRA200005 2007-11-19 21:11:57.000 Acquisition C0HTRA200005 2007-11-19 21:11:57.000 Acquisition C0HTRA200005 2007-11-19 21:11:57.000 Acquisition C0HTRA200005 2007-11-19 21:11:57.000 Acquisition C0HTRA200005 2007-11-21 06:46:17.000 Acquisition C0HTRA200005 2007-11-22 07:47:56.000 Acquisition Here is how they exist after being inserted into the temp table using DISTINCT: ID CLIENTID CHANGEDATE NEWVALUE ----------- ------------ ----------------------- ---------------------------------------------------------------- 15 C0HTRA200005 2007-11-19 21:07:20.000 NULL 16 C0HTRA200005 2007-11-19 21:10:52.000 NULL 17 C0HTRA200005 2007-11-19 21:11:57.000 Acquisition 18 C0HTRA200005 2007-11-19 21:11:57.000 NULL 19 C0HTRA200005 2007-11-21 06:46:17.000 Acquisition 20 C0HTRA200005 2007-11-22 07:47:56.000 Acquisition I've tried this many different ways using variations of ORDER BY/ no ORDER BY. I've tried creating the temp table first with the IDENTITY field, then insert into it. I've also tried SELECT INTO syntax while creating the IDENTITY field with the select. I've done this with newid() as well. Any ideas would be huge. Thanks much.
View Replies !
Select As VarChar But Order By DateTime
Hello, I'm trying to CONVERT some DateTime column to VarChar in the SELECT part but still do a DateTime ORDER BY while using a UNION. In other words, does anyone know how to make this work: create table #temp1 ( d datetime ) create table #temp2 ( d datetime ) insert into #temp1(d) select '2001-12-12' union select '2002-11-11' insert into #temp2(d) select '2003-10-10' union select '2004-09-09' --works fine------------------------------- select convert(varchar, d, 101) dd from #temp1 order by d ------------------------------------------- --but can't make this work------------------- select convert(varchar, d, 101) dd from #temp1 union select convert(varchar, d, 101) dd from #temp2 order by dd -- wrong type of sorting --order by d-- error --order by cast(dd as datetime) -- error ------------------------------------------- drop table #temp1 drop table #temp2 I would really appreciate your help. Thanks in advance, Thomas
View Replies !
Select Customer Last Inserted Order Details
Hi, Check this SQL SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending') AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2)) one customer can have more than one orders. So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this
View Replies !
How Can I Use SELECT DISTINCT And Maintain The Original Order
Say I have a result set with two fields numbers and letters. 1 A3 A1 B2 B The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet it will reorder the new result set by the Number field and return 123 However, I'd like maintain the Letter order and return 132
View Replies !
SELECT DISTINCT MyId Order By MyDate
I have the following records, MyDate MyId 2003/10/25 2 2003/10/26 3 2003/10/26 1 2003/10/27 3 2003/10/28 2 2003/10/29 4 I want to get the most earlier date distinct records. I try to use "SELECT DISTINCT MyID from Table;" I expect to get Myid: 2,3,1,4 But the computer returns Myid:1,2,3,4 Is there a way to get the records using "SELECT DISTINCT MyID from Table ORDER BY MyDate;" Appreciate help......
View Replies !
TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
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,
View Replies !
Select Records From Two Tables, Plus Page And Order Them?
Hi, I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this.... DECLARE @Temp TABLE ( IDINTNOT NULL, NameNVARCHAR(128)NOT NULL, TypeCHAR(1)NOT NULL ) INSERT INTO @Temp SELECT i.ID, i.Name, Type = 'I' FROM Item i UNION SELECT p.ID, p.Name, Type = 'P'FROM Package p SELECT * FROM @Temp ORDER BY Name ASC I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further? Many thanks for any help! :)
View Replies !
|
TRACKER
