SQL 2012 :: How To Find Unique Rows Considering Only Specific Columns
Apr 18, 2014
write a query which retrieves only unique rows excluding some columns.
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19393Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
In the above table project number and id shouldn't be considered and I should get the unique rows considering rest of columns and sorted based on date. Expected result is
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
Using DISTINCT with SELECT have effect only for one column. But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.
Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows Select DISTINCT a4 From Y ---> " 85 rows
We are on SQL 2014...we have a bunch of views in a database where we are trying to find the views which have more than 16 columns max for unique index/constraint...this is needed so we can convert them to indexed views...
Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ... There will be entries where there can be duplicate customerFirst and customerLast names. I would like to just return a single entry of the duplicate names and all associated row information. IE: [customerID], [customerFirst], [customerLast], [ email], [program] 01 Bill Smith bill.smith@hotmail.com ymca 02 Bill Smith bill.smith@hotmail.com Sports 03 jon doe jon.doe@hotmail.com AAA 04 jon doe jon.doe@hotmail.com Ebay 05 Paul Sprite paul.sprite@hotmail.com Rec Desired Returned result: 01 Bill Smith bill.smith@hotmail.com ymca 03 jon doe jon.doe@hotmail.com AAA 05 Paul Sprite paul.sprite@hotmail.com Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); dAdapter.Fill(pocDS, "Data Set"); However this is throwing up an error when i build the app: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Source Error:
Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison 1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry
I have an SQL .bak file and I would only like to restore specific columns as one of the columns is a free text field and is substantially increasing the size of the file. I can't restore it due to disk space constraints so dropping the column isn't possible if I can't get the table into a database locally.
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATEtbl SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000) FROM Table tbl JOIN Person p ON p.PersonID = tbl.PersonID JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2 WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%
'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.
I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
What would be the most economy solution for this WHERE, I see the code where we have covnert to 101 type on both sides of equation, which I tnink is not right.
So I'm thinking to put it on the left like this, just curiouse is this the best solution, I also heard that TSQL interpret between even better , here I'm really care abour performance.
declare @DATE DATE = '01/14/2014' --A: SELECT * FROM TT WHERE CAST( TT.DATETIME AS DATE) = @DATE
--B: SELECT * FROM TT WHERE TT.DATETIME >= @DATE and TT.DATETIME < DATEADD(D,1,@DATE)
I have a table (represented by #Events) that holds modifications made to another table. I do have some control over the table structure and indexing. I want to pull all of the change records that were made between two dates.
The tricky part is to include the previous version of each record, which will usually be found prior to the start date in question.
The code that I have provided below works. So you can use it to easily see what should be returned. But it's very slow in production.
Any better method to pull this data together?
-- Production version of this table has 4.5 million rows (roughly 1,000 rows per day) -- Primary key is on L4Ident (clustered) -- nonclustered index on ProcessDate, LinkRL4 DROP TABLE dbo.#Events; DROP TABLE dbo.#Results; CREATE TABLE dbo.#Events ( L4Ident int IDENTITY(1,1) NOT NULL,
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type FROM Table1 t1 WHERE t1.Key2 IS NULL AND t1.Type LIKE 'TypeA' AND t1.Key1 NOT IN (SELECT Key1 FROM Table1 t2 WHERE t1.Key1 = t2.Key2 AND t1.Key1 <> t2.Key1 AND t2.Type LIKE 'TypeB')
Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.
For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500 W 21-30 400 W 31-40 200 A 17-20 100 H 41-50 250 H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
I am working on a sql data that has a list of product names, shipment type and the count of shipments. The values are listed as rows in the database. it will be in the below format.I want to transpose only the shipment type and the corresponding count of each product name in the below format.I tried to do this but i am not able to achieve in the correct format.
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand, dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number], dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
I am trying to create a calendar style report that will have 12 months (as columns) and store opening listing in rows. I have created a matrix, but the problem that I have is that the store opening listing displays in the right period, but they are not in any order. I would like to have the openings always on top, right under the header in the matrix. Now I have them scattered randomly all over the matrix. I tried numerous way of sorting and that does not work.
I am attaching a sample of what I would like to accomplish (months are columns).
I need find out the number of columns in flat file before i process that particular file.I have file name in @filename variable and file path is @filepath variable.But do not not that how i will check the column name in before i will process that file.
@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles And i am using for each loop container to read the file one by one and put the file name in @filename variable.and my file name like
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file.if it is not there then i have to send mail to client saying that file is not valid.I need to also calculate the size of flat file.
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
