Not Part Of DISTINCT Clause But I Do Want It Selected
Dec 10, 2007
I get the rows(records) I want by removing the RequestDateTime column from the Select query
SELECT distinct et.configname as 'Part Number',
substring(email,(charindex('@',email)+1),len(email)) as 'email ending',
u.company_name as 'Company',
u.city as 'City',
u.country as 'Country'
--,RequestDateTime as 'Date Downloaded'
from
new_products.dbo.modelrequests et
left outer join
cc_host.dbo.usr_list u
on
et.username = convert(varchar(50),u.[user_id])
where
interfacename like '%download%'
and
result=0
and
u.country = 'JP'
and
CONVERT(DATETIME, RequestDateTime, 102)>= convert(DATETIME,'12/5/2007', 102)
GROUP BY email,et.configname,u.company_name,u.city,u.country,requestdatetime
I want to show the RequestDateTime for the 1600 records that do get selected.
How can I put RequestDateTime back in the resulting table output without it being part of the Distinct clause?
I have a table with 3 columns - userID, score, dateCompleted
From this table I wish to select the highest score for each userID and the date for that score. If there is more than one row with the same userID and score (i.e. the user got the same score twice), I want to select the earliest date.
To clarify, there are multiple rows which have the same userID, and may have the same score too, but I want to pull out just 1 score for each userID.
I could do this quite easily if the DISTINCT operator worked on a per column basis, but it works on the entire row instead. Is there a way to SELECT DISTINCT column, but still output other columns in those rows?
I hope this is clear, but please let me know if I've confused you.
Hello, When I am using DISTINCT in the SELECT clause for a table having a column of datatype "ntext" in SQL 2000 as well SQL 2005, it fails giving the error as "ntext data type cannot be selected as DISTINCT". This is coz "The text, ntext, or image data type cannot be selected as DISTINCT."
But I am able to insert into a temporary table having a column whose datatype is nvarchar) using this SELECT clause in SQL 2000. Please advice me how this happens. If I am not worng, implicit conversion has happened. But this insert fails raising the same error in SQL 2005. Please help me to find the difference thanks in advance!
I'm having a problem building a query for an application I'm writing. Basically, there are a bunch of records in a table, and some of them may have the same identifier code. I only want one of each identifier code. The table looks like this:
Memo --------- Msg_Identifier varchar(60) Memo_ID int (pk) Subject varchar(64) Message text Priority int Deleted int
My query:
SELECT DISTINCT Msg_Identifier, Memo_ID, Subject, Message, Priority FROM Memo WHERE Deleted = '0'
When I run this query, I get "The text data type cannot be selected as DISTINCT because it is not comparable."
I only want Msg_Identifier to be the distinct field, and it's obviously not set up as text. I've used SELECT DISTINCT in the past but I don't ever recall hitting an issue like this. What's the best way to do it?
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
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.
I have a Query like this and i am using fulltext search.
Select * FROM [Search] WHERE (@limitByName = 0 OR CONTAINS([Name], @searchStringOR))
Say @limitByName is 0, will my second part still executes.??? How Sql server 2005 handles the OR part in WHERE clause.(Need detailed answer)..pls give any useful link to refer.
How can i find this, if i use Actual Execution plan.
I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.
I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.
Does anyone know how I can elegantly get around this problem other than my best guess below:
Best guess pseudo code: IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.
Here's some code that reconstructs the problem: select datalength(char(13)) CarriageReturnVisible , datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]
I was trying to copy child records of one parent record into another, and wanted to report back new child record id and corresponding child record id that was used to create it. I ran into run-time error with OUTPUT clause. Following is a script that will duplicate the situation I ran into:
CREATE TABLE Parent( ParentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentName VARCHAR(50) NOT NULL) GO
CREATE TABLE Child( ChildID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentID INT NOT NULL REFERENCES Parent(ParentID), ChildName VARCHAR(50) NOT NULL) GO
INSERT INTO Parent(ParentName) VALUES('Parent 1') INSERT INTO Parent(ParentName) VALUES('Parent 2') GO
INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 1') INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 2') GO
At this stage, there Child table looks like:
ChildID
ParentID
ChildName
1
1
Child 1
2
1
Child 2
What I want to do is copy Parent 1€™s children to Parent 2, and report back which source ChildID that was used to create the new child records. So I wrote the query:
I am having a heck of a time figuring out what controls how/when the generated SQL for a report puts a DISTINCT clause in front of it.
For instance, not that this report makes any sense, but I have 58 rows in my fact table/entity €“ If I pull in a lookup field and execute, the distinct is put in the query and I basically get a list of the possible domain values. It runs the whole joined table query to get them, but it does list (in this case) just 4 records. Now I put in the primary ID of the fact entity and the distinct goes away and I get my 58 rows. If I put two lookup fields, the DISTINCT is back. If I pull in the description field (text string just a direct source field mapping not part of the identifying attributes), the distinct is there. If I pull in the Company Name field on a different entity (which is essentially the same as pulling in Description only it is part of the identifying attributes), there is no DISTINCT. I can pull in all my fields on this entity and none of them drive the distinct. And I swear (ok, I am probably lying but not on purpose) the field/attribute and roles properties are all the same on the attributes. But you get my general question/situation...
Any insight for me? Does it have to do with how I am building the report rather than the underlying model?
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
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site. I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part. While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as "Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
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.
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
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.
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel. What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config? I guess while I use Part 1 to connect, maybe it will be cancel exclusive method of the database ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time!
1) Rows ordered using textual id rather than numeric id
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id
Result set is ordered as: 1, 11, 2 I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
Dear readers, On my page people can opload foto's with a <asp:FileUpload ID="myfile" runat="server" BorderWidth="3px" BorderColor="Silver" BorderStyle="Inset" /> and stored the filePath in the database Now i want, that when there is no picture selected, the path to Noimage.jpg picture who is in my image map will stored on my datadbase. my script start like this If Not (myfile.HasFile) Then
////how can i select a standard image from my imageMap and store path to database /////
Dim cnn As Data.SqlClient.SqlConnection
Dim cmd As Data.SqlClient.SqlCommand
Dim strSQL As String
Dim connString As String = (ConfigurationManager.ConnectionStrings("Personal").ConnectionString) strSQL = "Insert Into tblMateriaal(ArtikelGroep,Artikelnaam,ArtikelType,ArtikelMaat,Aantal,Prijs,ContactPersoon,EmailAdress,Aanvul) Values(@ArtikelGroep,@ArtikelNaam,@ArtikelType,@ArtikelMaat,@Aantal,@Prijs,@ContactPersoon,@EmailAdress,@Aanvul)" cnn = New Data.SqlClient.SqlConnection(connString)
cmd = New Data.SqlClient.SqlCommand(strSQL, cnn)
Dim plaatje As New Data.SqlClient.SqlParameter("@plaatje", System.Data.SqlDbType.NVarChar) plaatje.Value = " " cmd.Parameters.Add(plaatje)
Dim ArtikelGroep As New Data.SqlClient.SqlParameter("@ArtikelGroep", System.Data.SqlDbType.NVarChar) ArtikelGroep.Value = ddl1.SelectedValue cmd.Parameters.Add(ArtikelGroep)
Dim ArtikelNaam As New Data.SqlClient.SqlParameter("@ArtikelNaam", System.Data.SqlDbType.NVarChar) ArtikelNaam.Value = tb1.Text cmd.Parameters.Add(ArtikelNaam)
I am having a problem updating one field in a table the update should be the product of two other fields from the same row. There are atleast 3000+ records need to be updated here.
e.g.
update A set A.b = A.c * A.D
here b c and d are from same row .. I was wondering if someone knows how to solve this problem.
HI Guys, I have a question. I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).
Current Statement: And tblRetailer_Contact.faxcontact LIKE *
I substituted: And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’
This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?
DECLARE @FaxContact as varchar (50)
SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5) SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', '')) SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5) SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', '')) SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)
SELECT tblEData.Timestamp As [TimeStamp], LTRIM(RTrim([ResultsCustName])) AS CustName, LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status], FaxContact AS FaxContact, ResultsPKey As ResultsKey INTO tmpE_Callbacks FROM tblEData LEFT JOIN tblContact ON tblEData.RetailerPrefix = tblContact.Prefix WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date AND FaxContact Like '%@FaxContact%'
I am new to writing sprocs so forgive me if this is trivial. I am selecting fields from a table and placing them into a temp table in row format. (Row 1 in temp table is the first row in a file that will be created using DTS package). My question is: How can a format a field that I have selected that only has, say 3 chars, into a value of 5. Ex: field in DB = aaa I need to format it as: 2 spaces + aaa
But the length of the value will be varying from record to record.
I need to get the Bacup of my SQl 2000 database but i need only last 100 records of all tables in my database. or i want to create new database from existing , schama is same but i need to import only 1000 record from all tables
