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.
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
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?
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.
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
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;"
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
I know this is a popular problem, but i haven't found an answer to my situation. I have a table (myTable) with three Columns (Column1, Column2, Column3). Column1 entries are unique (with unique identifier), Column2 entries are not unique. I want to do a SELECT DISTINCT on myTable so that Column2 is unique and i get all the other columns. I thought something like this would work
SELECT DISTINCT Column2 as Col2, newid() as Col1, Column3 as Col4 -- I am getting uniqueidentifier for each row as well
This doesn't seem to work however. I still get all the rows because Col1 is unique. since i am using aliases, the usual syntax doesn't work either.
i have an aliased column in an sql statement that works fine whendisplaying its output in a datagrid, but when I transfer the sqlstatement into a stored procedure , the datagrid can't see it. I get anerror "{"DataBinder.Eval: 'System.Data.DataRowView' does not contain aproperty with the name myaliasedcolumn." }
This is my syntax, but it keeps presenting an error of 'Invalid Column name Last Resort
Select fb.foo, fb.man, cz.choo, 'Hi' As [Last Result] From foobar As fb Inner Join chezter As cz On fb.username = cz.username and [fb].[Last Result] = [cz].[Last Result]
There is always a colour value. But there is not always a value in the MD columns. For example one Product may have values in MD01 MD02 MD03 MD04 MD05 whilst another has values in all MD columns.
My problem is thatI am trying to create a results list based upon selecting distinct values from the colour and md columns
In otherwords i can't have more than only one instance of a word appearing in the recordset list
I'm really struggling with this because there are only 6 colours so if i set distinct purely on 6 colours i only get back 6 rows.
When I try to set disctinct across all the MD columns it seems to ignore it and lists results for example in the table
ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010 1 red car bike 2 blue bike car train
my select lists results as red car bike blue bike car train
and it is as if it only carries out the distinct command across the row not across all columns for all rows if you see what i mean?
I need to be able to list all data from all rows that have values in the MD columns and colour column but not list the values more than once and not list "empty" (NULL) columns. Does this make sense?
This is the select statement i wrote.
Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL
But it returns empty columns and it returns every instance of the same word so in other words the distinct command doesn't seem to be working at all?
I don't know if this is because of my asp code I am trying to list results with the rescordset?
<% While ((Repeat1__numRows <> 0) AND (NOT template_rs.EOF)) %>
I have one more problem. How can I also in addition to being able to list all distinct results list only results for a specific letter
for example
Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL WHERE FIRST LETTER ='A'?
I am so far out of my depth here guys I am hoping that someone who has real knowledge of SQL can help me with this statement. I've been pulling my hair out for days now and getting just more and more frustrated listing the same results :(
I am attempting to run the following select statement joining multiple tables but in the end result I would like only Distinct/Unique values to be returned in the invlod.lodnum column.
[select pw.schbat, adrmst.adrnam, adrmst.adrln1, adrmst.adrcty, adrmst.adrstc, adrmst.adrpsz, invlod.lodnum, shipment.host_ext_id, shipment_line.ordnum, car_move.car_move_id from aremst join locmst on (aremst.arecod = locmst.arecod) and (aremst.wh_id = locmst.wh_id)
Been trying to come up with a query to filter-down my sample set intodistinct records. For instance, lets say column1 is a sample set,column2 is the parameter, and column3 is a name and column4 is a type(lets also say there is a fifth column (id) that is an id). What Ineed is one record per type per sample only if type is given, if not,then return that record as well.I've used a subquery to get as close to the desired query is aspossible:select * from table1where id in (select min(id) from table1where column1="A"group by column1, column2)Here's an example of all data for sample "A":1 2 3 4----------A 1 X PA 1 Y PA 1 Z PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VI want output :1 2 3-------A 1 X PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VExcept the above query will exclude the last two records becausecolumn3 is not 'grouped by'.Basically I need to reduce any 'range' of records per sample (columna) where column4 is not null (ie = 'P'), to only one record andkeeping all others. Thanks in advance:-B
I have a table 'wRelated' with the following columns
[related_id] [int] [channel_id] [int] [mui] [varchar] [price_group_id] [type_id] [int] [related_mui] [varchar] (100) [date_started] [smalldatetime] [date_ended] [smalldatetime] [date_entered] [datetime] [deleted] [tinyint], [rank] [int] data in column [mui] is repeated as the table has more than one entries for the same [mui], The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui] The recordset expected should be something like this.
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM ... ... ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index]
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.
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. . .
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.
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
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
Dear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField '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 getit.........regardskrishnan
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.
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 )
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
Hi, 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?
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?
I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert Insert into Employee values('aa', 'T', 'qqq') Insert into Employee values('aa' , 'F' , 'qqq') Insert into Employee values('bb', 'F' , 'eee') Insert into Employee values('cc' , 'T' , 'rrr') Insert into Employee values('cc' , 'pp' , 'aaa') Insert into Employee values('cc' , 'Zz' , 'bab') Insert into Employee values('cc' , 'ZZ' , 'bac') For select select col1,MAX(col2) as Col2,Max(Col3) as Col3 from Employee group by Col1
