SQL Distinct Value And Order By Some Field......
Dear all,
In SQL Server 2000 , how to get distinct records sort by one
field .
Example
SELECT DISTINCT A FROM tblTEST ORBER BY B
Here, In Table
Field 'A' contain more than one same data...
Field 'B' contain all are different Data......
I want distince in Field 'A' and order by Field 'B'..... how to get
it.........
regards
krishnan
View Complete Forum Thread with Replies
Related Forum Messages:
Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
View Replies !
Order By Because Of Distinct
I have a strange situation (I think). Within a view (which explains why I use the top 100% --> to use an order by) I have this query: select TOP 100 PERCENT * from tbOfferDetails where ofd_id = ofd_parent and ofd_fk_off_id = 100 This gives me the following results: ofd_fk_off_id off_fk_class_id 100 2753 100 2753 100 2071 100 2753 Now I change the query to: select distinct ofd_fk_off_id, ofd_fk_class_id from (select TOP 100 PERCENT * from tbOfferDetails where ofd_id = ofd_parent and ofd_fk_off_id = 100 order by ofd_sequence ASC ) as tbOffers This gives me the following results: ofd_fk_off_id off_fk_class_id 100 2071 100 2753 In the execution plan, it says that a distinct order by is used on off_fk_class_id. My question is: why is this done? I want only a distinct and not an order by. So is there a way to change this (default?) behaviour.
View Replies !
Distinct With Order By
It gives me this error: ORDER BY clause (TimeStamp) conflicts with Distinct The sql statement is: sql Code: Original - sql Code SELECT DISTINCT division from table order by Stamp DESC SELECT DISTINCT division FROM TABLE ORDER BY Stamp DESC I've tried: sql Code: Original - sql Code SELECT DISTINCT division, Stamp FROM table ORDER BY Stamp DESC SELECT DISTINCT division, Stamp FROM TABLE ORDER BY Stamp DESC But this produces duplicate rows, which in this specific case is unacceptable. Is there any way I can acheive what I want? Stamp is a TimeStamp if you couldn't figure it out. I just want the last one entered, but all the last ones entered per division. . .
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 !
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 !
DISTINCT Requires ORDER BY
OK I am trying the following select statement but I am getting a 'DISTINCT requires ORDER BY to be used' error. I have an ORDER BY in it so I am not sure what I have missed? SELECT DISTINCT tbl_final_CP.ExtEnum + tbl_final_CP.ExtEnum AS [Full Cost] FROM tbl_final_CP INNER JOIN ContractorAreaRelationship ON tbl_final_CP.Area = ContractorAreaRelationship.AreaNo INNER JOIN tbl_CPCost ON tbl_final_CP.CP = tbl_CPCost.CP WHERE (DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (ContractorAreaRelationship.ContractorNumber = 6112) AND (DATENAME(mm, tbl_final_CP.dCount) = 'Conwy') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.Video > 0) OR (DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (ContractorAreaRelationship.ContractorNumber = 6112) AND (DATENAME(mm, tbl_final_CP.dCount) = 'March') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.ExtEnum > 0) OR (DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (DATENAME(mm, tbl_final_CP.dCount) = 'March') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.Video > 0) AND (ContractorAreaRelationship.AdminID = 6112) OR (DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (DATENAME(mm, tbl_final_CP.dCount) = 'March') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.ExtEnum > 0) AND (ContractorAreaRelationship.AdminID = 6112) ORDER BY tbl_final_CP.CP, tbl_final_CP.ExtEnum + tbl_final_CP.ExtEnum
View Replies !
Distinct & Dynamic ORDER BY
Hello, I have a stored procedure with dynamic ORDER BY. I would like to use the DISTINCT too. Is it somehow possible? Thank you Here is the stored procedure: SELECT identifier_company + cast(identifier_number as nvarchar(3)) as identifier, CASE WHEN canceled = 'True' THEN 'canceledPO' ELSE '' END AS style, staff.staff_name, purchase.purchase_id, purchase.traveller_name, nominal_department.department_name, purchase.canceled, purchase.travel_date, convert(nvarchar(20), purchase.date_raised, 103) as dated, supplier FROM purchase INNER JOIN purchase_project ON purchase.purchase_id = purchase_project.purchase_id INNER JOIN staff ON purchase.raised = staff.staff_id INNER JOIN nominal_department ON purchase.department = nominal_department.nominal_dep_id WHERE (raised in (SELECT staff_id FROM staff WHERE department like @FromDepartment) or purchase.raised = @raisedBy) and purchase_project.project_number like '%' + @Query + '%' ORDER BY CASE @SortDir WHEN 'ASC' THEN CASE @OrderBy WHEN 'staff_name' THEN cast(staff_name as nvarchar(100)) WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100)) WHEN 'department_name' THEN cast(department_name as nvarchar(100)) WHEN 'supplier' THEN cast(supplier as nvarchar(100)) WHEN 'canceled' THEN cast(canceled as nvarchar(10)) END END ASC, CASE @SortDir WHEN 'DESC' THEN CASE @OrderBy WHEN 'staff_name' THEN cast(staff_name as nvarchar(100)) WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100)) WHEN 'department_name' THEN cast(department_name as nvarchar(100)) WHEN 'supplier' THEN cast(supplier as nvarchar(100)) WHEN 'canceled' THEN cast(canceled as nvarchar(10)) END END DESC
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 !
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 !
SELECT DISTINCT And ORDER BY With Aliased Column
This query demonstrates a problem I have run across: USE AdventureWorks GO -- This query works fine. SELECT DISTINCT FirstName AS Name1 FROM Person.Contact ORDER BY FirstName GO -- This query also works fine. SELECT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName GO -- This query returns error 145 SELECT DISTINCT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName GO The last query returns the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified". It will work if I change ORDER BY to use "Name1" instead of "FirstName", but in the situation I have at hand, the query is generated by third-party software and I don't have the ability to change it. Can anyone explain why what's going on here? Oddly, this same query will work if I run it against SQL Server 2000.
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 !
How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
Hi, I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow... Can anyone help? Thanks in advance Gemma
View Replies !
DISTINCT SELECT Query With More Than One Field
Hello, I want to do a DISTINCT SELECT query with more than one field, for example a ID field with a Type field, as if both fields make the primary key, like (ID 1 ,Type 1) ,( ID 1, Type 2) and (ID 2, Type 1) is ok but not (ID 1, Type1) and (ID 1,Type 1) if its not possible to do a distinct with more than one then what other techniques are possible to get the duplicate data out. the reason why I want to use distinct is that I can use that query to export that data to where both of these fields make the primary key. Thanks in advance
View Replies !
Record Counts For Distinct Field Query
Is there a simple, one step query I can use to return the number of records that match a DISTINCT [fieldname] query? I have a table named cust_inf with 3,000 records or so with fields labeled STATE and CITY, among others. If I issue a SELECT DISTINCT state FROM cust_inf I get a table one column that has 15 records which show the 15 distinctly different states that my customers live in. That's fine, but I want to know how many customers I have in each state. What I am looking for is a two-column result cursor that lists the state in one column and the number of records from the 3,000 total that each state has. I've tried a number of single SELECT statements but cant seem to find the right way to make this work. I could write a sript that loops thru the result cursor to COUNT(*) the records that match a WHERE clause but I'm sure there is a simpler way. Does anyone have an idea I can try? Thanks! CHRIS
View Replies !
Select Distinct On Field With Values Seperated By
I have a db that contains a column named DSCODES. Values in DSCODES are seperated by a comma. If I run the following command. select distinct(DSCODES) from DB The following is returned. S100,S102,S103 S100,S103,S105 What I would like returned is the following S100 S102 S103 S105 Is this possible? Thank you in advance Graham
View Replies !
Looking To Collect Distinct Date Part Out Of Datetime Field
from this, circdate being a datetime field:SQLQuery = "select distinct circdate from circdata order by circdate"I need the distinct date portion excluding the time part.this has come about when I discoveredI am inserting and updating some datetime values with the same value,but for some reason, the values are always off by a few seconds. I seta variable called SetNow assigned to NOW and then set the datetimefields to this SetNow variable. Then when I collect the distinct datetime I am assuming they will have the same values recorded incircdate, but no, they are off by several seconds. Makes no sense to meat all. I tried renaming the variable several times but it makes nodifference at all.any help appreciated, thanks.
View Replies !
SELECT DISTINCT To Return Only The YEARS In A Date Field?
I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001. I want to create a query that returns one row for each year that there is a story. For example, if I had this data... 3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006 the query would return this result: 2001 2003 2006 This is the query I've started with: SELECT DISTINCT NewsDate FROM News ORDER BY NewsDate DESC What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here. Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.
View Replies !
Order By On Character Field
Does ORDER BY work on character data type in SQL Server through ODBC? I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results. Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name company_id = integer company_name = 30 characters
View Replies !
ORDER BY &&<VarChar Field&&>
Hi group, I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar. Question: Is it possible to ORDER THEM as if they where of type DateTime? EG select [year], [month] from tbl_WeightedAverageGenerated where [Year] = 2006 ORDER BY [Month] Returns: 2006, 10 2006, 11 2006, 12 2006, 5 2006, 6 etc... I need it to return: 2006 5 2006 6 2006 7 2006 8 2006 9 2006 10 2006 11 2006 12 Is this possible....and how?? TIA Regards, SDerix
View Replies !
Order By Partial Field
How can I "Order By" the second + third characters of a 7 char field ? Sample data looks like: LCA - L LCB - L LCF - M LCE - M LCE - A LCA - A LCB - A If I order by the whole field I get: LCA - A LCA - L LCB - A LCB - L LCE - A LCE - M LCF - L LCF - M What I want is: LCA - L LCB - L LCF - L LCA - A LCB - A LCE - A LCE - M LCF - M I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M"). Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol). All suggestions appreciated. Thanks Roger
View Replies !
Order Of Nvarchar(50) Field
In my SQL 2005 database table Records, I have 3 fields, field1, field2, and field3 which are all nvarchar(50) fields. The value of field2 is something like this, MDB006-MD002-0004-3-2007. I would like to order this field but only use the 0004-3-2007 part of the field to order it. Is it possible to put the last 11 charachters (0004-3-2007) in another field and then order it using this new field?
View Replies !
Field Not Sorting In Ascending Order
Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance
View Replies !
Equivalent Of ORDER BY [field] IN (…) In SQL Server
Hi, I have small question regarding ORDER BY clause. I wanted some value of the fields should come first before other values….Something like: +----------+----------------------------------------+ | code | name | +----------+----------------------------------------+ | UK | United Kingdom | | US | United States | | AF | Afghanistan | | AL | Albania | | DZ | Algeria | | AS | American Samoa | I want to display country UK and US code first and then rest of the countries using SQL statement. I know in MySQL, I can use ORDER BY code IN (...) --SQL SELECT * FROM countries ORDER by code IN ('UK', 'US') desc But not sure how can I do same thing in SQL Server. Is there any equivalent of ORDER BY [field] IN (…) clause in SQL Server??? Though I can create a new field something like sequence and use it to display these countries but I can not do that… Please advice. Thanks, Firoz Ansari
View Replies !
Out Of Order Identity Field - Sql2000
Hi AllI am finding unexpected results when inserted into a newly createdtable that has a field of datatype int identity (1,1).Basically the order I sort on when inserting into the table is notreflected in the order of the values from the identity field.Have I been wrong in assuming that it should reflect the order from thesort?The code is ...create table tmp (A varchar(50), L float, C int identity(1,1))insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Lland I don't understand why the values in tmp.C aren't in the ordersuggested by the sort.Any comments most appreciatedBevan
View Replies !
ORDER BY (field In Foreign Table)
Newbie question. Can someone show me an SQL statement that sorts the results of a query by a field in a different table? CREATE TABLE `Table1` ( `table1_id` INTEGER AUTO_INCREMENT , `table1_name` VARCHAR(255), PRIMARY KEY (`table1_id`) ) CREATE TABLE `Table2` ( `table2_id` INTEGER AUTO_INCREMENT , `table2_name` VARCHAR(255), `table2_table1` INTEGER, PRIMARY KEY (`table2_id`) ) ALTER TABLE `Table2` ADD FOREIGN KEY (`table2_table1`) REFERENCES `Table1`(`table1_id`); What I'd like is something like: SELECT * FROM Table2 ORDER BY "Table1(table2_table1).tabel1_name",table2_name It's the section in double quotes that I can't figure out how to compose. As an example, if Table 1 has 1, A 2, B 3, C and Table 2 has 1, a, 2 2, b, 1 3, c, 2 then I'd like the sort to return 2, b, 1 1, a, 2 3, c, 2 TIA, Stephen
View Replies !
Newbie Q: How To Change The Field Order In A Table ?
Hi... Everybody, I have a set of tables in a database containing data. But I find that the field order in each table (as I view in the design window) is not the way I wanted. This is because I've added new primary keys to the table (as part of DTS services) which are now located at the end of the table. I need these to be moved to the beginning (along with the data, ofcourse) so that I could see all the primary keys in the beginning. Is there any way I can make the order in which fields appear in the design view to my liking ? Any help is appreciated, BR, Sudhakar
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 !
Evaluation Order For TextBox Field In Matrix In RS
Hi Guys, Does anyone knows what is the evaluation order for expressions attached to "Value" property and "BackgroundColor" property for a field. My problem is if a field is being changed in its value property to a particular value, will this changed value be recognised immediately by the Backgroundcolor property during its rule evaluation, it does not seem to be the case.
View Replies !
Stored Procedure To Update A Display Order Field.
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list. Example ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions? Thanks - Mark
View Replies !
Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors. Say I have a table called myTable with columns col1,col2,col3, Here's what I'm trying to do with myCTE AS ( Select col1 ,col2 ,col3 ,row_number() over (order by case when(@DIR = 'ASC') then case when @COLTOSORTBY='col1' then col1 asc when @COLTOSORTBY='col2' then col2 asc else col3 asc end else case when @COLTOSORTBY='col1' then col1 desc when @COLTOSORTBY='col2' then col2 desc else col3 desc end end from myTable ) Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance. Thanks.
View Replies !
Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Hi, I have the following script segment which is failing: CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude)) INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'." Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values. The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why. Any ideas?
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
DISTINCT To ShortDateString, Not DISTINCT To The DateTime; How?
Hello, I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime ) This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table). This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day. So far so good, and everything works fine except one thing: Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows) What I need to do is to get a result like (in a neat fashion :) "SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals" So, how to do it in a suitable way? Thanks in advance.
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 !
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 !
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 !
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 !
|
TRACKER
