SQL Server 2014 :: Exclude Duplicate And Fetch Max Of X Column
Sep 11, 2014
I want to fetch max of Field2 if duplicate records in Field1 and rest of the values of field1 , below is the sample format.
Field1 Field2 Field3 Field4
32 375 abc-xyz A
32 379 xyz-efg A
55 405 abc-xyz B
55 407 xyz-efg B
132 908 abc-xyz C
132 999 xyz-efg C
152 800 abc-xyz D
152 850 xyz-efg D
155 900 abc-xyz E
156 925 abc-xyz F
157 935 abc-xyz G
View 2 Replies
ADVERTISEMENT
Oct 14, 2015
how to fetch data from oracle database in sql server 2014
example:
oracle schema :t1
sql server :t2
now am in t2 sql server database
now am executing below query
select * from t1.tablename ;
View 1 Replies
View Related
May 26, 2014
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies
View Related
Jan 10, 2007
Hello
I'm developing my fist Integration Service and I have this operations:
Reading from a XML
Check for duplicate records and discard them
Insert the result into the database
The XML I don't control and could came with duplicate records that I have to discard. How can I find them?
I want to find the duplicates in the XML and not in database.
tkx for the help
Paulo Aboim Pinto
Odivelas - Portugal
View 14 Replies
View Related
Mar 3, 2008
Hi. I'm creating a new publication for replication. There are a few columns on our main database I do not want published as they contain some confidential information. When I go to create a new publication, I go through the wizard and I select the option to filter rows vertically. I uncheck the columns I don't want published and the wizard completes without any errors.
When I go to start the Snapshot agent to create the database on new server, it gets error saying it cannot find certain columns--the columns I do not want published to begin with.
Am i doing something wrong? I appreciate any help you may provide!
View 7 Replies
View Related
Jul 16, 2014
This is a followup to a previous question to a previous but in reverse of Find rows where value in column not found in another row
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to exclude any two rows where Type is equal to 'TypeA' and Key2 is Null that have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row.
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
6 26 TypeC
7 NULL TypeD
8 NULL TypeD
I would like to return all the rows except where Key=1 and Key=3 because those rows together meet the criteria of Type='TypeA'/Key2=NULL and does have a corresponding row with Type='TypeB'/Key1=Key2.
View 2 Replies
View Related
Sep 13, 2006
Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!
Thanks in advance, Larry
ALTER FUNCTION dbo.TestFoodDisLikes
(
@ResidentID int
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @RDLike varchar(50)
DECLARE @RDLikeList varchar(250)
BEGIN
SELECT @RDLikeList = ''
DECLARE RDLike_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT FoodItem
FROM tblFoodDislikes
WHERE (ResidentID = @ResidentID) AND (Breakfast = 'True')
OPEN RDLike_cursor
FETCH NEXT FROM RDLike_cursor
INTO @RDLike
SELECT @RDLikeList = @RDLike
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM RDLike_cursor
INTO @RDLike
SELECT @RDLikeList = @RDLikeList + ', ' + @RDLike
END
CLOSE RDLike_cursor
DEALLOCATE RDLike_cursor
END
RETURN @RDLikeList
END
View 5 Replies
View Related
Jan 19, 2015
I found an article on the CodeProject website named T-SQL MapReduce. I'm trying to duplicate the SQL code there into a SQL Server 2914 Developer Edition database I've got, and also the C# code using Visual Studio. I've defined the user data types fine (e.g.: word_t, words_t and so on), but I got an error when I tried to create the UDF dbo.mapper from the page. The error I got was:
Msg 102, Level 15, State 1, Procedure mapper, Line 5
Incorrect syntax near 'CALLER'.
The SQL code I used is straight off of the page; it looks like this:
[code=sql]
create function dbo.mapper(@documents DocumentsWithAutoIncrementIndexTable READONLY, @sep nvarchar)
returns @t TABLE ( wordindex int, word word_t )
WITH EXECUTE AS CALLER
[/code]
What have I done wrong? I'm guessing that the author has done something like left off a variable declaration or something like that. what am I missing?
View 6 Replies
View Related
Feb 1, 2015
I have 3 Tables
TableA - TAID, Name, LastName
TableB - MaleFriendsName, MaleFriendsLastName [One to many]
TableC - FemaleFriendsName, FemaleFriendsLastName [one to many]
A query returns duplicate results from TableB as well as TableC
TableB and TableC have nothing in common and should not interfere with each other.
with TwoTables as (
select
a.QuickSpec as QuickSpecId,
[Code].....
Resultset returns duplicate values on TableB AND only for 1 record in the results [As per Lynn examples]
View 1 Replies
View Related
Sep 11, 2014
i have a table like below
create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)
values like
1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22
I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?
View 3 Replies
View Related
Aug 13, 2015
Below is a XML column data. How to get the Id and respective Names for "Case Manager" Dropdown ONLY in SQL server 2008. I don't want to get anything related to "Intake Staff" drop down.
<DropDown Prompt="Case Manager" Column="case_manager" AddOnly="false" ReadOnly="false" Required="false" CanHaveNotes="no" LabelCssClass="" IndentLevel="1" FirstEntryBlank="false" CssClass="" DefaultValue="" ChoiceType="1">
<Items>
<Item Id="1">ABC</Item>
<Item Id="2">DEF</Item>
<Item Id="3">GHI</Item>
<Item Id="4">JKL</Item>
<Item Id="5">MNO</Item>
[code]....
View 1 Replies
View Related
Nov 12, 2012
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
View 3 Replies
View Related
Jul 16, 2014
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
View 9 Replies
View Related
Aug 4, 2014
I have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')
[Code] .....
View 1 Replies
View Related
Jun 8, 2015
We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.
SELECT
project.project_number,
project.country_code,
project.project_desc,
gsl.global_service_line_desc,
buy.buyer_desc,
[Code] ....
View 9 Replies
View Related
Jul 25, 2015
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
View 2 Replies
View Related
Oct 3, 2013
I am attempting to create an SQL statement that will query a file and give me amount totals by company number/customer number. The totals have to be combined into 4 groups (1/2/3/4) for each amount total in company number/customer number combination. In effect it will look something like this:
COMPANY | CUSTOMER | SORT | AMOUNT
==================================
00001 | 11111 | 1 | $55
00001 | 11111 | 2 | $12
00001 | 11111 | 3 | $19
00001 | 11111 | 4 | $ 0
00001 | 22222 | 1 | $99
00001 | 22222 | 2 | $53
...and so on.
I HAVE THIS PART WORKING ALREADY. The problem is that I am trying to exclude the rows that have 0 (zero) in the amount column from showing up in the output. The amount is a calculated field of all the invoice for that company number/customer number combination for that sort (eg: Company 00001/Customer 11111/Sort 1 has $55 associated to it). I cannot use the calculated field in my where clause.
I will include a simplified version of my select statement so you can see how I got as far as I have and where to go so I pretty much say "WHERE SUM(SubTBL.Amount) <> 0".
----SELECT STATEMENT-----
SELECT
MainTBL.Cust#,
SUM(SubTBL.Amount) As TotAmt,
CASE
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN '1'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN '2'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN '3'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN '4'
[code]....
View 1 Replies
View Related
Dec 26, 2007
I am creating a query which will show patients that are enrolled in more than one program, but I need to exclude those patients that enrolled more than once in the same program. Here's part of the code:
SELECT member_id, service_id
FROM pat_prg_info ppi
This query produces results like the following:
member_id service_id
1001 1
1001 2
1003 9
1003 9
1004 2
1004 9
I would like to exclude 1003, since this member_id is enrolled twice in service_id 9. How can I accomplish this?
View 1 Replies
View Related
Jun 4, 2015
I want to populate data from Production to UAT(except Identity column).For that I created query,its generating script what i have required.But in last column getting with comma.I should eliminate the last comma.
script to my requirement:
declare @TABLE_SCHEMA varchar(10)
set @TABLE_SCHEMA='dbo'
declare @TABLE_NAME varchar(100)
set @TABLE_NAME='Demo_Table'
SELECT ORDINAL_POSITION AS COLUMN_POSTION ,TABLE_SCHEMA,TABLE_NAME
[Code] ....
View 12 Replies
View Related
Oct 17, 2014
I want to change duplicate data in my sql server column.
the data= 'Barack Obama' , what I want
'Barack Obama 23453'
I have the following query:
UPDATE klant SET naamvoornaam=naamvoornaam + CAST(klantnummer AS VARCHAR)
WHERE naamvoornaam NOT IN (
SELECT(naamvoornaam)
FROM klant
GROUP BY naamvoornaam
HAVING ( COUNT(naamvoornaam) = 1 ))
Message from messages:
String or binary data would be truncated.
I think the max length exceeds the length of the destination column. I've tried:
Max(Len(naamvoornaam + CAST(klantnummer AS VARCHAR)))
message from messages:
An aggregate may not appear in the set list of an UPDATE statement.
View 7 Replies
View Related
Aug 29, 2007
I do an insert of the xml into the table and that works fine, but how do I split the tags to different tables. I have tried SSIS and a XML Source to an OLEDB Source, but since the xml file contains different groups that do not work.
The xml is created by Infopath and it seems like the groups are created if the components belongs to different sections.
Table1
id int,
xmltag xml
I am starting to be desperate, I really need some help solving this one way or another.
View 2 Replies
View Related
Feb 12, 2008
Hi All,
I have two tables.
Table A contains a unique column of some Keys.
Table B contains Key column,Value column and Entry_Time column.
Now I need the most recently entered value for each of the Keys in table A using table B.
I dont need any repeatation of Keys in my result.
Please help me out.I am using DB2.
View 1 Replies
View Related
Feb 13, 2014
I have a query that produces unique rows. However, some of the unique rows have the column called testname that has the same test listed more than once. All I want to do is drop the older testname and keep the testdate column.
Select
Distinct
TestID,
TestDate,
TestName
From third.test
I want to keep testdate in the query....guessing I need to put in to a temp table then drop the oldest one somehow by doing a subquery using Select Max....
View 1 Replies
View Related
Oct 14, 2014
With the data example below I am trying to consolidate the duplicate rows by flattening the dealer and billcode, or putting those values in each of the columns instead of creating separate rows...
74 MARTHA PATNE RIPLEY 1 23,327,76 ROTTINGDAM AAC SPRINGFIELD 3052 USA MPATRIP@AMERICANALARM.COM,MPATRIP@COMCAST.NET,PRIPLEY@ONECOMMUNICATIONS.COM
[table]
arnumbercustomernamedealerbillcodeaddl_addraddr1addr2branchcityzipcodecountryemaildefaultEmail
39SUSAN THALKER 2271BOTTOMWOOD RDAACHAMMOND02180-2703USASUWS3@COMCAST.NET1
56ANN REBELLO 123SHERIDAN AVEAACMEDFORD2155USANULLNULL
58DARRELL/PATTI SANDERS 6020DOTY AVENUEAACDANVERS1923USANULLNULL
74MARTHA PATNE RIPLEY 123ROTTINGDAM DRAACSPRINGFIELD3052USAMPATRIP@AMERICANALARM.COM1
74MARTHA PATNE RIPLEY 1327ROTTINGDAM DRAACSPRINGFIELD3052USAMPATRIP@COMCAST.NET1
74MARTHA PATNE RIPLEY 176ROTTINGDAM DRAACSPRINGFIELD3052USAPRIPLEY@ONECOMMUNICATIONS.COM1
[/table]
View 1 Replies
View Related
Apr 21, 2008
Hi,
I am using SQL Server 2005 Mobile Edition & Merge Replication
in this I want to insert a record into table,
in that table I have taken UserID coloumn as auto incrementing Identity type.
In a book I read that,
If you are using an Identity column, you must find the next available number and reseed before an insert can be successful. You will also have to set up ranged identity columns on the published database to prevent errors when the new data is merged.
Now I want to ask here that, how should I ressed that value before inserting?????
any help in a form of CODe will be appriciated.....
thanks in advance...
View 8 Replies
View Related
Aug 2, 2007
Hi!
I have created a view and one of the columns in the view has an alias assigned to it.
I'm able to read the metadata from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and also lookup
from which table each column in the view orginated from except for the column that has an alias assigned to it.
Is there any other way to lookup a column that has an alias assigned to it?
Thanks alot!
Adam
View 2 Replies
View Related
Oct 9, 2014
I have the following table
DECLARE @TABLE1 TABLE (D_ID int, C_ID int, C_HIST_ID int)
INSERT INTO @TABLE1
VALUES(1000016,71,77),
(1000017,71,547),
(1000017,71,553),
(1000017,71,564),
(1000013,71,565),
(1000017,71,3329),
(1000017,71,3330)
SELECT * FROM @TABLE1 ORDER BY C_HIST_ID
D_ID C_IDC_HIST_ID
10000167177
100001771547
100001771553
100001771564
100001371565
1000017713329
1000017713330
I was able to get the following result
SELECT D_ID,C_ID,C_HIST_ID
FROM @TABLE1 ACH
WHERE ACH.C_ID= 71
AND ACH.C_HIST_ID IN (SELECT MAX(ACH1.C_HIST_ID) FROM @TABLE1 ACH1
WHERE ACH1.C_ID = ACH.C_ID
AND ACH.D_ID = ACH1.D_ID )
ORDER BY C_HIST_ID
D_ID C_IDC_HIST_ID
10000167177
100001371565
1000017713330
But the result I need is
D_ID C_IDC_HIST_ID
10000167177
100001771564
100001371565
1000017713330
C_HIST_ID = 564, which I'm unable to get it through my query...
View 4 Replies
View Related
Apr 20, 2015
I want to set a default value to a column while adding it.
Create table test(id int )
insert into Test values(123)
alter table test add column2 int default(0)
In the above query I have set the default value, but it will not set the default value to the already inserted columns.
So how to set a default value for the already inserted rows, while adding a new column to it ?
View 6 Replies
View Related
Jun 12, 2015
MDX query to get the source table and column for a measure from ssas database. i want below highlighted using mdx.
View 4 Replies
View Related
Jan 8, 2014
I have data like this in the table :
IntRecpieID strName intMealtypeID Total
100 ‘A’ 1 20
101 'B' 2 30
100 'A' 3 40
Desired Output required:
IntRecpieID StrName 1 2 3
100 'A' 20 Null 40
101 'B' Null 30 Null
View 5 Replies
View Related
Aug 13, 2015
How do I append an accumulating number to duplicates in a column? I would prefer to update the table
for example
Name
John
John
Tom
Bob
Bob
Bob
Mike
to look like
Name
John
John1
Tom
Bob
Bob1
Bob2
Mike
View 1 Replies
View Related
Aug 24, 2015
I am new in SQL Server, What to check ,what action do i need to take while adding a new column to a table.
View 5 Replies
View Related
Sep 18, 2015
I have a table with some rows and columns what i want is i want to Show sum of particular column in the last row. This is my code.
SELECT DISTINCT Cluster.ClusterName, Gruppe.GruppeName, Arbeitspaket.ArbeitspaketName, BMWProjekt, AnzahlAP, Abgerechnet, InBearbeitung, Billanz FROM Bestellung
INNER JOIN Cluster ON Bestellung.Cluster = Cluster.rowid
INNER JOIN Arbeitspaket ON Bestellung.Arbeitspaket = Arbeitspaket.rowid
INNER JOIN Gruppe ON Bestellung.Gruppe = Gruppe.rowid
WHERE Projekt ="EA-284-Nxx" AND AnzahlAP <> 0 AND Abgerechnet is 1 AND InBearbeitung is NULL AND Billanz is NULL;
View 4 Replies
View Related