Ordering In The Table
Hello,
I have made sql table called costreallocation consists of two columsn:
RuleID varchar(10)
Type varchar(20)
I run the following three queries:
insert into costreallocation(RuleId,Type)
values('aa','a')
insert into costreallocation(RuleId,Type)
values('dd','d')
insert into costreallocation(RuleId,Type)
values('cc','a')
then when i notice that these three records are not inserted as they are run:
In the table thn there are three records :
aa a
cc d
dd d
but i need to be filled in the table as they run as:
aa a
dd d
cc d
your help is highly appreciated
Best regards
View Complete Forum Thread with Replies
Related Forum Messages:
Ordering Table
hello everybody, I've got a question concerning a sql statement on a table. I have a table that displays several countries. Now I would like to sort these countries. Country Netherlands Germany England Austria This colom must be ordered beginning by Netherlands. By using the default order by i can't fix this so is there another way?
View Replies !
Ordering Pivot Table Using Case Else
I have created a Pivot table using Case Else with a combination of Row_Number function. What I'm looking for is to try to Order it in a specific way. Manivannan.D.Sekaran, helped me with another Pivot table that I had and it worked great. So I decided to learn how to do a Pivot table using Case Else. Sample Data is the following without the Case Else UserID LastName FirstName DocumentDesc docFileName 1 Smith Paul Resume PSmithResume.pdf 1 Smith Paul PhdStatistics phdstatstranscript.pdf 1 Smith Paul MS Applied Statistics MsAStats.pdf 1 Smith Paul MS Operation Research MsOpResearch.pdf 2 Jackson Jane MS Information Systems MsInforSystems.pdf 2 Jackson Jane Resume JaneJacksonResume.pdf This is my query for my Pivot using Case Else: Code Snippet Select UserID, LastName, FirstName, MAX(Case When RecID=1 Then DocumentDesc Else '' End)As Document1, Max(Case When RecID=1 Then docFileName Else '' End) As DocumentFileName, Max(Case When RecID=2 Then DocumentDesc Else '' End)As Document2, Max(Case When RecID=2 Then docFileName Else '' End) As DocumentFileName, Max(Case When RecID=3 Then DocumentDesc Else '' End)As Document3, MAX(Case When RecID=3 Then docFileName Else '' End) As DocumentFileName, Max(Case When RecID=4 Then DocumentDesc Else '' End)As Document4, Max(Case When RecID=4 Then docFileName Else '' End) As DocumentFileName, Max(Case When RecID=5 Then DocumentDesc Else '' End)As Document5, Max(Case When RecID=5 Then docFileName Else '' End) As DocumentFileName, Max(Case When RecID=6 Then DocumentDesc Else '' End)As Document6, Max(Case When RecID=6 Then docFileName Else '' End)As DocumentFileName From ( Select a.UserID, a.LastName, a.FirstName, b.FileName, b.DocumentDesc, b.DocumentTypeID, ROW_NUMBER() OVER(PARTITION BY a.UserID ORDER BY a.UserID) AS RecID FROM Person a JOIN Documents b ON a.UserID = b.UserID Where b.DocumentTypeID = '126d2beb-f7a1-4bf1-b9c0-dded37d3a6bc' Or b.DocumentTypeID = '9087956e-1fb0-4f3d-ba33-ef31d79141af' ) X Group by UserID, LastName, Firstname Order by LastName Code Snippet The Output is the following with the Pivot applied: (I'm excluding UserID, LastName, FirstName for space purposes) The column headings are: Document1 DocumentFileName Document2 DocumentFileName Document3 DocumentFileName Document4 DocFilename The Data for document and filename columns: Ms Information Systems MsInforSystems.pdf Resume JaneJacksonResume.pdf Resume PSmithResume.pdf PhdStatistics Phdstatstranscript.pdf Ms Applied Statistics MsAstats.pdf What I want is to have the column called Document1 to have only Resumes and the rest of the other columns to have the other files. The following data places the data into one table. Create Table #data ( [UserID] int , [LastName] Varchar(100) , [FirstName] Varchar(100) , [DocumentDesc] Varchar(100) , [docFileName] Varchar(100) ); Insert Into #data Values('1','Smith','Paul','Resume','PSmithResume.pdf'); Insert Into #data Values('1','Smith','Paul','PhdStatistics','phdstatstranscript.pdf'); Insert Into #data Values('1','Smith','Paul','MSAppliedStatistics','MsAstats.pdf'); Insert Into #data Values('1','Smith','Paul','MSOperationResearch', 'MsOpResearch.pdf'); Insert Into #data Values('2','Jackson','Jane','MsInformationSystems', 'MsInforSystems.pdf'); Insert Into #data Values('2','Jackson','Jane','Resume', 'JaneJacksonResume.pdf');
View Replies !
Ordering
Hi there! We are experiencing a problem using order clause in the statement below: SELECT person.pkey , first_name FROM person , private_person WHERE fkey_person = person.pkey AND first_name = 'A' ORDER BY first_name , person.pkey Where person table contains the following columns: pkey, name etc and private_person pkey, fkey_person (indexed), first_name (indexed) The output is: STEP 1 The type of query is SELECT FROM TABLE private_person Nested iteration Index : i$private_person$first_name FROM TABLE person Nested iteration Using Clustered Index pkey first_name ----------- --------------------- 2000512 A 10994 A 2299 A 1097 A 1218 A 5133 A 1329 A 1387 A 1465 A 7532 A 5513 A 1884 A 512 A 591 A (14 row(s) affected) STEP 1 The type of query is SETOFF Why SQL server does not order by pkey? Is there any information about it somewhere? Is it a bug or what? If we are ordering by fkey_person everything is Ok. Can anybody help? Thanks a lot! Kind Regards, Vladimir
View Replies !
Ordering Ids
I have a table named as C1_Messages with fields Id, Messages, Dates. My Id filed looks like this: 1000 1001 1008 1009 1084 1093 1098 etc. But here I need to make these Ids in a order, means it sould be in a consecutive order. Like 1000 1001 1002 1003 1004 1005 etc. So is there anyway to do this method in SQL? Regards Shaji
View Replies !
Ordering Dilemma
I have an int field - that will hold number for an ordering system. It's a databound field to a textbox in a grid, I want the default to be empty, however not all fields are required so i may have 1-6 as bound values, and the rest should fall in line behind them - the only thing i can think of to do is default the values to 9999 so they come after 1,2,3,4,5,6 etc... I don't like seeing 9999 in my bound fields. thanks for any suggestions you may have. Jeff
View Replies !
Ordering, Grouping - HELP!
Having recently had excellent service here (many thanks, Erland!),here's another wee problem.SQL 2000I need to get a report which will display the movement of people thus:ID DIRECTION NAME DATE VEHICLE REGISTRATIONThe value of DIRECTION can be either "Inbound" or "Outbound"I need to have the data grouped by ID, with the "Outbound" row first.For example1 "Outbound" Smith 10/02/2005 ABC1231 "Inbound" Smith 11/02/2005 ABC1235 "Outbound" Jones 14/02/2005 XYZ7895 "Inbound" Jones 15/02/2005 DEF456This is the SQL so far.SELECTfldPersonID,tblMovementType.fldType AS [MovementType],'Outbound' AS [Direction],fldMovementDate AS [Date],CASE WHEN tblPerson.fldForenames IS NULLTHEN fldSurnameELSE fldSurname + ', ' + fldForenames END AS [Name],'Outbound ' + fldVehicleOut AS VehicleRegistrationFROMtblPersonINNER JOIN tblMovementTypeON tblMovementType.fldMovementTypeID = tblPerson.fldMovementTypeWHERE((fldMovementDate BETWEEN @FromDate AND @ToDate) AND(fldStatus = 1))UNION ALLSELECTfldPersonID,tblMovementType.fldType AS [MovementType],'Inbound' AS [Direction],fldMovementDate AS [Date],CASE WHEN tblPerson.fldForenames IS NULLTHEN fldSurnameELSE fldSurname + ', ' + fldForenames END AS [Name],'Inbound '+ fldVehicleRtn AS VehicleRegistrationFROMtblPersonINNER JOIN tblMovementTypeON tblMovementType.fldMovementTypeID = tblPerson.fldMovementTypeWHERE((fldMovementDate BETWEEN @FromDate AND @ToDate) AND(fldStatus = 1))What I need is the way to Order or Group it so that I can just put therows into a report without any grouping on the report itself (CrystalReports aarrggh!) - if this is possible!Edward--The reading group's reading group:http://www.bookgroup.org.ukThanks
View Replies !
Ordering Problem
Hi everyone, I have a database (not designed by me) in sql server, I amhaving trouble ordering it correctly, and I would appreciate any help.Here is my structure, it's sort of hierarchical:TABLE_AID_COLSUB_ID_COLTABLE_BID_COL'NAMEThis could be thought of as an employee and supervisor structure. Wehave employees (TABLE_B) and their relationship (TABLE_A).I know I need to use a group and oder by, but I am drawing blankstoday. I need the result to be (if using the employee example):Bob-Ryan-SueMarkNick-George-Paul--Ringo--StarHere is my half-hearted stab at it:SELECT *FROM TABLE_A AINNER JOIN TABLE_B BGROUP BY A.SUB_ID_COLORDER BY NAME;Thank you very much for any help you can offer.Have a great night!Ryan
View Replies !
Ordering An Int Column
I've created a clustered two column index on a table where the secondcolumn is an integer value. When the first column is the same, insteadof ordering in numerical order it is ordering 1,10,100,2,20,200 etc.How can I change the behaviour to order the data in numerical order?Thanks,Rick
View Replies !
ORDERING OF DATA
HOW TO ORDER THIS DATA.MONTH-YEAR10-200310-202011-20033 -20034 -20035 -20036 -20037 -20038 -20039 -2003THANX IN ADV.Join Bytes!
View Replies !
IsNull Ordering
Hi-I've got some issues with the ordering of search results. I've got 4fields, (a, b, c, d). Most often there is data in field a. Sometimesthere isn't and so then I'd like to look at field c.I can accomplish most of what I want with this:selecta as location1, ISNULL(SiteCity, SiteCityOther) AS location1,ISNULL(SiteCityOther, SiteCounty) AS location1, ISNULL(SiteCounty,SiteCountyOther)AS location1,
View Replies !
Specific Ordering
Hi, I was wondering, I have several columns of data that are able to be sorted on - the user just clicks on the column name. However, we have 1 column called order status where we don't want the order to be alphabetical (and it is a text field - a couple current possible values are Processing...In Production...Waiting For Approval). This order is not good for logical sorting - we would want to be able to specify what value would get order. Does that make sense? So we would want to be able to say that Processing is first in the display, Waiting For Approval would be next, then In Production would be next, and so on. Is there any possible way of doing this in a SQL query? Or am I going to have to manually modify all the data adding numbers to the beginning of each data so that it will sort on the numbers? Thanks everyone.
View Replies !
About Ordering Of Columns
i add new column using alter command but i always found it in the end of table but i want to add it in particular position between the columns........... how to do so .............:)
View Replies !
Ordering By Date
Hi,I'm streaming data monitoring histories of components into a database table with the following three columns: Hour (DateTime), Id (Int64), and Value (Int32). The Value entry is an aggregate of all values sent from component Id during, and 59 minutes and 59 seconds after, the time listed in the Hour column.I had rather not have to sort the queries after pulling them from the database by date. So I tried to index the DB by the Hour column. Any column will inevitably have duplicates, since the uniqueness depends on a combination of Hour and PortId. But Indexing the Hour column doesn't result in INSERTs being in order as I had expected. Instead, every entry is listed in order of insertion.So. . .how can I keep such a table ordered by date on the disk? I'm afraid this will become very inefficient if this isn't nipped in the bud right now.Thanks so much for your help!-Brandon
View Replies !
Ordering Days
I have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so.. January 10 January 11 January 12 January 13 January 14 etc January 19 January 2 January 20 January 21 January 22 here is my sample code select [month] + ' ' + [day] as [Date] from mytable where [month] = 'january' and [year] = '2004' Thanks, Jeff
View Replies !
Ordering By @parameter Possible?
Hiya! I've got a little stored procedure: CREATE PROCEDURE rICDCode @param1 char(15) = 'Description' AS SELECT DiagCode, ICD9ID, Description FROM ICDCodes ORDER BY @param1 and SQL is returning error 1008 - "The SELECT item identified by the ORDER BY number %d 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." I want to be able to use the same stored procedure for several different functions which all need the same rowset sorted differently. Any way to do this? Thanks, Sarah
View Replies !
Custom Ordering Top 20 ?
Hi guys. I want to create a top 20 product list from a few thousand products. I want the rest of the products to be grouped into 'others'... I also want the products to be ordered by the facts in the cube. Thus the product dimension would dynamically change depending on the Time dimension thats being selected. is this plausible ? Thanks Tom
View Replies !
Advanced Ordering
Hello. I want to order a resultset from a table by two columns in another table. Here's how it looks: [Books] ID Title [linkBookAuthor] BookID (Books.ID) AuthorID (Authors.ID) [Authors] ID Firstname Lastname AuthorOrder As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname. Thanks for any help.
View Replies !
Ordering By One Field Or Another
Is it possible without using CASE statements, to order a result set by one field (if the second field return value is null) or another (if the second field return value is not null) This would be on datetime fields. Let's say I have 5 records with 2 date fields sched_dt and arriv_dt. sched_dt will always have a value, arriv_dt may or may not have a value. In a single result set I would like to have the records with an arriv_dt sorted by arriv_dt and the ones without an arriv_dt sorted by sched_dt. *Hope that makes sense* Thanks
View Replies !
Ordering Dates
I have a table that has 4 dates for each record (birthday, anniversary, policy_start & policy_end). What I want to do is cycle through each record and then do some calculations using the gaps between the dates. So it might be Calc1 using (birthday->anniversary), Calc2(anniverary->policy_start), Calc3(policy_start->policy_end), etc. BUT the dates might be in different orders (ie the birthday could come first - or the anniverary could, etc). In VBA in Excel, I can use the SMALL function within my calcs and this returns the minimum date, the next smallest, the next & then the biggest. But how can I do this in SQL. I guess I can push the 4 dates out to a temp table, order it and then suck them back in - but I have no idea how to do this for all the records in my primary table I've also tried using various WHERE statements (ie do Calc1 using Birthday & Anniversary WHERE Birthday < Anniversary and ........ - but the code gets awfully long. Is it possible to write my own SMALL function which uses a bubble sort on the 4 dates & then returns the smallest, next smallest, etc Any thoughts ?
View Replies !
Re-ordering Records
Hi all, Ive got a table with a field called 'morder' which orders a menu based on the values in this field (1 for position 1, 2 for position 2 etc...) for example 1 - menu item 1 2 - menu item 2 3 - menu item 3 4 - menu item 4 If I want to add a record to this table and put it at number 2 in the list, i need to update the table to then read... 1 - menu item 1 2 - NEW menu item 2 3 - menu item 2 4 - menu item 3 5 - menu item 4 I want to use a mssql or php function to re-order this field... is it possible?? Hope you can help, and hope it makes sense... Ash
View Replies !
For Each File - Ordering?
Does anyone know in what order files are processed if using a for each container and file enumerator? Does it sort files alphabetically and loop through? Or use a date time stamp? Or something else? I need files to be processed in a certain order, and the ordering is in the filename, so the foreach file enumerator sorts on filename, then I'm ok, otherwise I need to figure something else out. Thanks.
View Replies !
Ordering Date ?
SELECT LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date, SUM(CASE a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming, SUM(CASE a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design, SUM(CASE a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload, SUM(CASE a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing, SUM(CASE a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting, SUM(CASE a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as Others From task_table a,act_table b where a.status_id=b.act_id and a.user_id=(select user_id from user_table where user_name='Raghu') and a.task_date like '%/%/2006' GROUP BY LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) Output : Aug 2006 294 0 0 80 0 0 Jan 2006 14 0 0 0 0 0 Oct 2006 336 0 0 0 0 0 Sep 2006 3262 20 24 8 16 0 How to sort the date in ascending Order ? Jan 2006 Aug 2006 Sep 2006 Oct 2006
View Replies !
Union Ordering
Hello, I'm attempting to run three separate queries and have them returned as one recordset. I need to show the top 10 customers based on the number of orders they placed for each of three months, then combine them to give a representation of which customers have been within the monthly top 10 for the overall period of three months. My problem is when I run a count on the number of orders they have placed and order by that count, it is in ascending order which in escence shows me the bottom 10, not the top 10. To resolve this issue, I can specify descending order. This works fine with one query, but when I run all three queries using union statements, I can only have one order by which returns incorrect results. Here is my query. In this example, I get the combination of the three recordsets as expected, but each of the individual queries is not ordering descending, so I get the wrong end of the table. I am not bound to union, but do not know where to go. I do not believe I can use joins or concatenate queries because I have multiple where clauses. Any help would be greatly appreciated. Please note that I am using a view to convert the cryptic field names which are used by a third party software tool into more friendly names. We will call this view Friendly_View for this example. USE MyDB SELECT TOP 10 CustName, Location, Phone, COUNT(Orders) AS TotalOrders FROM Friendly_View WHERE MONTH([Order Date]) = MONTH(GETDATE())-1 AND YEAR([Order Date]) = YEAR(GETDATE()) GROUP BY CustName, Location, Phone UNION SELECT TOP 10 CustName, Location, Phone, COUNT(Orders) AS TotalOrders FROM Friendly_View WHERE MONTH([Order Date]) = MONTH(GETDATE())-2 AND YEAR([Order Date]) = YEAR(GETDATE()) GROUP BY CustName, Location, Phone UNION SELECT TOP 10 CustName, Location, Phone, COUNT(Orders) AS TotalOrders FROM Friendly_View WHERE MONTH([Order Date]) = MONTH(GETDATE())-3 AND YEAR([Order Date]) = YEAR(GETDATE()) GROUP BY CustName, Location, Phone ORDER BY COUNT(Orders) DESC
View Replies !
Message Ordering
When I am receiving a batch of message from a queue to process, does it matter if I order by the queuing_order or the message_sequence_number? I want to make sure my messages process in the order in which they were put into the queue, which I know will happen if I process messages one-by-one, but want to make sure that the data I pull and process execute in the order in which they were received. Tim
View Replies !
Ordering Rows
hi, i have following data: create table tb_customer (customer_id int, order_id varchar (12)) insert into tb_customer (customer_id, order_id) values (123, 2201) insert into tb_customer (customer_id, order_id) values (123, 2246) insert into tb_customer (customer_id, order_id) values (123, 2236) insert into tb_customer (customer_id, order_id) values (123, 1253) insert into tb_customer (customer_id, order_id) values (120, 1201) insert into tb_customer (customer_id, order_id) values (127, 2251) insert into tb_customer (customer_id, order_id) values (127, 2231) insert into tb_customer (customer_id, order_id) values (122, 2121) and i want to get the following output: output: Nu_order | customer_id| order_id 1 |123| 2201 2 |123| 2246 3 |123| 2236 4 |123| 1253 1 |120| 1201 1 |127| 2251 2 |127| 2231 1 |122| 2121 any idea? thank you
View Replies !
Ordering Within Hierarchy
Hi all, Ive got a bit of a problem, I have two tables: CATEGORY id name parent_CATEGORY_id RECORD id CATEGORY_id Stock_Held_Number ps. Ive stripped out any non relevant fields I also have the following query (again ive stripped out the non-relevant fields) SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName FROM CATEGORY LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great. The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table. What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue. For example: The CATEGORY table has the following values ID Name Parent_Category_id 1 Pens 2 Animals 3 Rocks 4 Horses 2 5 Dogs 2 When I currently run the query I get: Name TotalStock Pens 20 Animals 30 Rocks 40 Horses 50 Dogs 60 It's technically correct because I don't want the parent to calculate the total value of the children What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get Name TotalStock Animals 30 ---Dogs 60 ---Horses 50 Pens 20 Rocks 40 Does anybody have any pointers as to how I can achieve this. Many thanks Mike
View Replies !
Ordering Results
I am trying to do a simple SQL query..the result will be sorted alphabetically by default, but i need to specify say, if there is a "Others" it will be placed last in the list. Is there any way to write the query using ORDER BY? e.g Australia China Others USA and i want it to be: Australia CHina USA OThers
View Replies !
Ordering By Months
I am wondering if someone maybe able to help me, I am needing to order my data via months in the calendar sense not alphabetically, below is what I currently have, but it only does it alphabetically. select to_char(created,'yyyy-Mon'), matdesc, count(*) from test group by to_char(created,'yyyy-Mon'), matdesc order by to_char(created,'yyyy-Mon') desc any help would be greatly appreciated Thank you Stephen
View Replies !
One Attribute Having Different Ordering In Two Hie
Hello All, I have a Day attribute in my Time Dimensions. This attribute is used in two different hierarchies ( Calendar Year Hierarchy and Fiscal Year Hierarchy). I want Day attribute to have ascending order in Calendar Year Hierarchy and descending order in Fiscal Year Hierarchy. Since the OrderBy property is at the attribute level is there any way I can achieve this by not having a redundant Day attribute? Please advice. Regards Jasraja
View Replies !
Need Help On String Ordering In Sql Server
hi, i'm using a similar code for a recursive relationship in sql server written @ http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp.i'm using the Table of Contents Hierarchy code in the article. However, instead of ordering by the sort order, i'm using the TOC instead. My problem is, i know TOC is of varchar type, therefore, if the sort order has an integer of more than 10, the order sequence would be 1.01.11.101.111.2etc.so how do i make it so that it is1.01.11.21.3......1.10 instead? Thank you
View Replies !
Ordering Of The Data Values
Hi frnds, i need a help from all of u. i will tell whats it ------ i have a webpage creating using ASP.NET,C# using SQL server 2000. i have a table in the database which has attribute named "Priority" which has values like "High" "Low" "Medium". all i wana to make is that i enter any of this value tru a dropdown list into the database and i have used Datalist in Webpage so that i will retrieve back the info from the database. when any request is made to retrieve back this value . i want to see all the priority field in the datalist which has priority as "High" in bold and in order. is this possible ,if it is plz let me know thank u in advance sunilk
View Replies !
Custom Ordering Question
Hi group, i'm having a problem with custom ordering in a SP. My code isthe following:CREATE PROCEDURE [dbo].[PersonasSelectAll_P]@fromRow int,@toRow int,@expresionOrdenamiento int = nullASSET NOCOUNT ONDECLARE @TotalFilas intSelect @TotalFilas = count(*) from [Personas]SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @expresionOrdenamiento =1 THEN [Id]--WHEN @expresionOrdenamiento = 2 THEN [Nombre_RazonSocial]WHEN @expresionOrdenamiento = 3 THEN [CUIT_CUIL]ELSE nullEND) AS Row, * , @TotalFilas as TotalRowsFROM [Personas]AS NumberedPersonsWHERE(Row >= isnull(@fromRow, 0)) AND (Row <= isnull(@toRow, row))The line WHEN @expresionOrdenamiento = 2 THEN [Nombre_RazonSocial] iserroring out (the sp compiles, it's a runtime error), i'm getting an'Cannot convert varchar to int'. Maybe it's because Nombre_RazonSocialis varchar? If i comment that line then everything works fine. AnyHelp??Thanks in advance
View Replies !
Greek Alphabet Ordering
Got a tough one here for you SQL junkies.I'm working on a website (in ASP) for a national greek/collegeorganization. All it's college chapters have greek chapter names,i.e. Alpha Chapter, Delta Chapter, Delta Iota Chapter, Gamma PhiChapter, ect. ect.I need the SQL to return the chapters in greek alphabet ordering. Seebelow:1. Alpha2. Beta3. Gamma4. Delta5. Epsilon6. Zeta7. Eta8. Theta9. Iota10. Kappa11. Lambda12. Mu13. Nu14. Xi15. Omicron16. Pi17. Rho18. Sigma19. Tau20. Upsilon21. Phi22. Chi23. Psi24. OmegaBasically I need a way to specify this type of presedence for wordingand keep all the results in greek-letter order, so Sigma Upsilon comesbefore Sigma Tau, and so on.Also, to make it even more difficult, these are written words, not theactual greek symbols like α β etc. Everything is stored inthe DB as Alpha Beta Whateva Chapter.Hopefully some of you SQL junkies will be able to help on this one ;)
View Replies !
Ordering In Conjunction With Unions
Why does this not work? When I run this query in Query Analyzer, I get the error "Incorrect syntax near the keyword 'UNION'." This seems simple enough... SELECT * FROM SalesLead WHERE Age = '50' ORDER BY FirstName UNION ALL SELECT * FROM SalesLead WHERE Age = '60' ORDER BY FirstName Thanks for any advice Aaron
View Replies !
Ordering Of Data Rows
It is one of the covenants of DBMS that data rows are retrieved in no particular order, and I have learned not to rely on that. However, I'd like to know under what circumstances could rows be returned, e.g., as a result of a simple query on SQL Server, not in the order they were stored. I have never seen this happen so far. I can't even test my sorting algorithms (on the data after it gets retrieved from the server) since it always comes already sorted. Naturally, I can always simulate this scenario (and I have) but this is kind of a "conflict of interest", I'd like to see "the real thing". Any ideas? Kamen
View Replies !
Ordering Rows For Contents
I have a series of records, that are displayed as contents in a .NET application,i know i can order the items to be dynamic, i.e I set the top item in the contents, However I know this will get quite messy at the application level. I would like to set up the default if I insert an item, into the table to make it the last in the list, select max(ordernum) + 1, i guess? How can I set an on insert constraint, or what its called for this in SQL Server?
View Replies !
Problem In Ordering The Dataset
Hi Friends, I have generated a query using MSSQL 2005 - Reporting Services. It displays data based on alphabetical order of F_NM. I would like to ascending order the data set base on measure R. I dont know how to incorporate ORDER function to get data set in ascending order of rank (R). Please help. SELECT NON EMPTY { [Measures].[TAR CALL], [Measures].[MATTY MKT RX TOT], [Measures].[R], [Measures].[DRS CALL] } ON COLUMNS, NON EMPTY { ([DIM_SLS_ORG_PHY].[TM].[TM].ALLMEMBERS * [CUST].[F NM].[F NM].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [CB_PHY_LVL_Y] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Thanks & Regards, Naveen J V
View Replies !
Grouping And Ordering Dates
Hello, I am trying to solve this....when I run this query select convert(varchar,date,100) from temp3 where date between convert(varchar,getdate() - 1.0,101) and convert(varchar,getdate(),101) result is: Apr 19 2000 12:45PM Apr 19 2000 12:46PM Apr 19 2000 12:47PM Apr 19 2000 12:48PM Apr 19 2000 12:49PM Apr 19 2000 12:49PM Apr 19 2000 12:52PM Apr 19 2000 12:53PM Apr 19 2000 12:56PM Apr 19 2000 12:57PM Apr 19 2000 12:57PM Apr 19 2000 12:58PM Apr 19 2000 12:59PM Apr 19 2000 12:59PM Apr 19 2000 12:59PM Apr 19 2000 12:59PM Apr 19 2000 1:00PM Apr 19 2000 1:01PM Apr 19 2000 1:02PM Apr 19 2000 1:02PM Apr 19 2000 1:03PM Apr 19 2000 1:04PM Apr 19 2000 1:05PM Apr 19 2000 1:06PM Apr 19 2000 1:06PM Apr 19 2000 1:10PM Apr 19 2000 1:11PM Apr 19 2000 1:11PM this is what I am expecting. but when I try to group it select convert(varchar,date,100) from temp3 where date between convert(varchar,getdate() - 1.0,101) and convert(varchar,getdate(),101) group by convert(varchar,date,100) result is: Apr 19 2000 1:00PM Apr 19 2000 1:01PM Apr 19 2000 1:02PM Apr 19 2000 1:03PM Apr 19 2000 1:04PM Apr 19 2000 1:05PM Apr 19 2000 1:06PM Apr 19 2000 1:10PM Apr 19 2000 1:11PM Apr 19 2000 12:45PM Apr 19 2000 12:46PM Apr 19 2000 12:47PM Apr 19 2000 12:48PM Apr 19 2000 12:49PM Apr 19 2000 12:52PM Apr 19 2000 12:53PM Apr 19 2000 12:56PM Apr 19 2000 12:57PM Apr 19 2000 12:58PM Apr 19 2000 12:59PM result is correct but I would like it to be displayed like the first one. I will really appreciate Any clues, suggestions, How to at this point. Thanks a lot. HP
View Replies !
Ordering Threaded Comments
I'm working on creating a threaded comment system. Anyone can reply to a comment, as long as it itself was not a reply to another post. This is checked so by where the column name parent is 0. The column name for the comment ID is ID. Can I manipulate the SQL query below so that it will group the rows first by the comments that have parent = 0 and then have the replies for each of those rows come directly after each respective comment? Code: SQL SELECT * FROM tbl_name WHERE col_1 = 1 ORDER BY ID ASC; Thanks in advance! Dan
View Replies !
Ordering Values In A Cell
Data 10 13 15 3 26 10 13 20 35 29 10 13 5 26 2 10 15 27 34 33 Query Declare @num1 char(5) Set @num1 = (SELECT ltrim(Substring(ltrim(number2), 8, CHARINDEX(' ', ltrim(number2)))) FROM Num2played) SELECT @num1 FROM Num2played Error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Any suggestions or fix? Thank you. ========================================= The data above is in one colum. I would like to rearange the numbers in order. e.g. from "10 13 15 3 26" to "3 10 13 15 26".
View Replies !
Ordering And Rearrangement Records.
Hi, I have a table records like below. name priority ------------------------------ John 2 Mike 10 Soo 5 Jane 8 I want rearrange the table records like this. name priority ------------------------------ John 0 Soo 1 Jane 2 Mike 3 In other words, rearrange order by priority and change the value to zero-base. Are there simple queries for this function?
View Replies !
Ordering Date In Asc Order ?
SELECT LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date, SUM(CASE a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming, SUM(CASE a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design, SUM(CASE a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload, SUM(CASE a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing, SUM(CASE a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting, SUM(CASE a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as Others From task_table a,act_table b where a.status_id=b.act_id and a.user_id=(select user_id from user_table where user_name='Raghu') and a.task_date like '%/%/2006' GROUP BY LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) Output : Aug 2006 294 0 0 80 0 0 Jan 2006 14 0 0 0 0 0 Oct 2006 336 0 0 0 0 0 Sep 2006 3262 20 24 8 16 0 How to sort the date in ascending Order ? Jan 2006 Aug 2006 Sep 2006 Oct 2006
View Replies !
Problem In Ordering The Dataset
Hi Friends, I have generated a query using MSSQL 2005 - Reporting Services. It displays data based on alphabetical order of F_NM. I would like to ascending order the data set base on measure R. I dont know how to incorporate ORDER function to get data set in ascending order of rank (R). Please help. SELECT NON EMPTY { [Measures].[TAR CALL], [Measures].[MATTY MKT RX TOT], [Measures].[R], [Measures].[DRS CALL] } ON COLUMNS, NON EMPTY { ([DIM_SLS_ORG_PHY].[TM].[TM].ALLMEMBERS * [CUST].[F NM].[F NM].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [CB_PHY_LVL_Y] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Thanks & Regards, Naveen J V
View Replies !
Ordering Of Member Properties
In MSAS 2000, it was possible to drag/drop the member properties in a particular order. This came in handy when showing the properties in the screen tips...like information could be grouped together. In MSAS 2005, it appears that the member properties are ordered by name and I cannot change the order in the Dimension Structure tab. How do you change the member property order in 2005?
View Replies !
How To Prevent Ordering Of Records....
hello, i use VC# and sql server 2005. i create a table using the following command : create table history(name varchar(20) primary key); I insert records in a particular order.After all the records are inserted and when I try to retrieve the records, all of them are sorted in a ascending order.Is there any way to prevent them from being sorted. pls reply asap..... - Sweety
View Replies !
Date Ordering Problem
Hi i have a problem about Date sorting process.i am using CONVERT(char(10),dateTime,104) in my query.and it converts dd.mm.yyyy format successfully but when i write ORDER BY dateTime DESC it sorts only date.it doesn't control month and year how can i solve this problem? Thanks.
View Replies !
Ordering Records - Efficiently
I hope I explain this correctly... I'm required to allow users to order items in a field to be displayed on a page in the order they specified. For example: A user can drag and drop items in a list to specify the order it will be displayed in. I have my drag and drop code ready to do this. I have an idea on how to do this but I think it’s too inefficient. I was going to create an orderby field and populate it with a number that corresponds to the position of the item. However, as one can deduce, if a user drags and drops a record between two others, I would have to change not only its orderby number but then change all the other items orderby number. For instances if I dropped an item with an orderby number of 3 between 6 and 7 I would have to change the 3 to a 7 and then recursively change all the other records orderby numbers up to 3 and then change everything after 7. Well, I hope I make sense. It’s easier to visualize it on paper. Does anyone know how to tackle this issue of user dynamic ordering?
View Replies !
|
TRACKER
