Fetch Returning Duplicate Last Record

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


ADVERTISEMENT

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 View Related

Can't Fetch Record From Cursor

Nov 4, 2007

Hi,
I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited.  When there is a record in both the Filters and FilterElements tables, the fetch_status is 0.  If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1.  Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window).  But when used in a cursor, the record is not fetched.  The fetch_status is -1.  Can anyone tell me why the fetch doesn't work in this case.  Thanks
----
DECLARE @CreatedByUser nchar(100), @WorkflowIDs varchar(50);
DECLARE @MyVariable CURSOR;
SET @MyVariable = CURSOR FOR
SELECT isnull(Filters.WorkflowIDs, ''),
isnull(FilterElements.CreatedByUser, '')
 
FROM Filters RIGHT OUTER JOINFilterElements ON Filters.ItemID = FilterElements.FiltersItemID
WHERE FiltersItemID = @FilterID;
OPEN @MyVariable;FETCH NEXT FROM @MyVariable
INTO @WorkflowIDs, @CreatedByUser;

View 3 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

Query To Fetch Latest Record

Jun 29, 2012

Table has details like below:

NAME UPATE-TIMESTAMP
==== ===============
mary time1
mary time2
mary time3
tom time1
tom time2
tom time3
tom time4
richard time1
richard time2

Output Expected:

NAME UPATE-TIMESTAMP
==== ===============
mary time3
tom time4
richard time2

In summary, the requirement is to fetch the latest upated record for each unique NAME.

View 1 Replies View Related

In Master And Detail Table Fetch Record.

Jun 24, 2008

Dear Sir
Thank for your reply but our requirements are not this
I am fully explain my requirements

In Master table have 5 columns; In Master Table stored all records.

NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0
Ad630Log10
Data810Summary0
Data820View0
Data830&View0
Data840Row10
Ad630Mbl20



In second Table store those records who are selected and stored in 2nd table.
NameAppidFunctionCodeFunNameSubFunCode
Data810Summary0
Data820View0
Data830&View0
Ad630Mbl20


Our requirements we use one query,
In query fetch total 5 rows. and output show like this

NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0N
Ad630Log10N
Data810Summary0Y
Data820View0Y
Data830&View0Y
Data840Row0N
Ad630Mbl20Y


Please immediate reply me,
I am waiting your reply.
Thanks Asit Sinha

View 3 Replies View Related

How To Fetch The Data Which Is Not The First Or Last Record From DB By Reporting Service?

Aug 6, 2007



I only found first or last function which means I can't show the data of others, is there any solution to solve this issue?

Thanks in advance.

View 4 Replies View Related

How To Fetch Record From Temp Table - Unique Combination

Sep 23, 2014

Table :StudentTeacherRelation

Id StdId TeacherName Day subject
1 1 Archana Monday English
2 1 Archana Tue Marathi
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi
6 2 Archana Mon english
7 2 Dipti Tues Hindi

Second table : Student

Id Sname Cid
1 Shalini 1
2 Monika 1
3 Rohan 3

I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come

Id StdId TeacherName Day subject
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi

I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query

select Student.Sname,TeacherName, Day,subject
from StudentTeacherRelation
inner join Student
Student.id = StudentTeacherRelation.StuId
where cid = 1

I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.

show
Id StdId TeacherName Day subject
where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)

So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?

View 1 Replies View Related

Returning The Last Row From A Set Of Duplicate Records

Feb 27, 2002

Any information as to how to handle this?

Thanks.

View 1 Replies View Related

Query Returning Duplicate Rows

Oct 6, 2004

I'm trying to write a query that will return rows within a specified range and print to a Crystal Report. When I run the query, it produces 2 row of everything. I would use the SELECT DISTINCT clause, but Crystal Reports will not let me edit the Select statement. But I can edit the FROM, WHERE and ORDER BY clauses. I think the problem is in my INNER JOINS but I'm having a problem figuring it out. Can someone please guide me in the right direction.

SELECT
DrawingVouchers."PlayerID",
DrawingVoucherNumbers."PromoID", DrawingVoucherNumbers."VoucherNumber", DrawingVoucherNumbers."IssueDate", DrawingVoucherNumbers."UserID",
CDS_PLAYER."LastName", CDS_PLAYER."FirstName",
CDS_ACCOUNT."Address1A", CDS_ACCOUNT."City1", CDS_ACCOUNT."State1", CDS_ACCOUNT."Zip1"
FROM
{ oj (("WinOasis"."dbo"."DrawingVouchers" DrawingVouchers INNER JOIN "WinOasis"."dbo"."DrawingVoucherNumbers" DrawingVoucherNumbers ON
DrawingVouchers."PlayerID" = DrawingVoucherNumbers."PlayerID")
INNER JOIN "WinOasis"."dbo"."CDS_PLAYER" CDS_PLAYER ON
DrawingVoucherNumbers."PlayerID" = CDS_PLAYER."Player_ID")
INNER JOIN "WinOasis"."dbo"."CDS_ACCOUNT" CDS_ACCOUNT ON
CDS_PLAYER."Player_ID" = CDS_ACCOUNT."Primary_ID"}
WHERE
DrawingVoucherNumbers."VoucherNumber" >= 37806 AND
DrawingVoucherNumbers."VoucherNumber" <= 37813

View 2 Replies View Related

Query Returning Duplicate Field Names

Jul 20, 2005

I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don

View 1 Replies View Related

Full-text Query (Freetexttable) Returning Duplicate Rows

Aug 31, 2007

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:




Code Snippet
SELECT *
FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL
ORDER BY RANK DESC






vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

View 4 Replies View Related

Returning More Than One Record From SP...

Feb 11, 2004

Hi ,

Is there a way in SQL Server stored procedure to return multiple records/ more than one records....?

Thanks in Advance...

-Mohit.

View 1 Replies View Related

Max Returning More Than One Record

Nov 8, 2007

when this query is run it returns the max value for each of the activity types eg. phone calls, emails etc.
what i want to achieve is for it to return only one record. whichever is more recent. but it only has to be either a phone call or an email.


SELECT regardingobjectidname, MAX(actualend) AS Last_Contacted_On, activitytypecodename, owneridname
FROM FilteredActivityPointer AS A
WHERE (statecodename = 'completed') AND (activitytypecodename IN (@activitytypes))
GROUP BY regardingobjectidname, activitytypecodename, owneridname

Melvin Felicien
IT Manager
DCG Properties Limited

View 8 Replies View Related

Returning The Most Rated Record

Mar 7, 2008

ALTER PROCEDURE dbo.GetMostRatedRecipe

(

@CurrentDate datetime

)

AS

SET NOCOUNT ON

SELECT *

FROM Recipe

WHERE TotalRating =

(SELECT MAX(TotalRating)FROM Recipe) AND published = 1 AND ReleaseDate <= @CurrentDate AND ExpireDate > @CurrentDate

This doesn't work good when the recipe having max total rating is not published & expired. I guess I need to first filter the recipes which are published & unexpired and then select the recipe having max total rating. But I don't know how to do that. Could anyone of you please help me doing this ?

View 1 Replies View Related

Help With Sql Query Returning Last Max Record

Oct 19, 2006

I am having problems with a SQL query.

Table has 10 fields and I need to return them all. The three most importaint, at least for the filter I need are:

id, studentid, date, canceled.

I need to return the last max(date) grater than or equal to @dateparam which is not canceled for each studentid

I have worked out some solutions but am not happy with them. Specially woried about performance when the table grows. I am expecting in full production a table growth of about 3 million records per month.

what would be grate is if there where a way of returning a the coresponding id like in:

select studentid, max(date), related(id) as ids
from tablea
where canceled=0
group by studentid

then I could do:

Select *
from tablea inner join (select studentid, max(date), related(id) as ids
from tablea a
where canceled=0
group by studentid
) b on (a.id=b.ids)

View 5 Replies View Related

SCOPE_IDENTITY() Returning The ID Value Of An Inserted Record.

Jul 29, 2006

There are loads of postings on the net about this problem but none I have found explain the cause.
Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method.
Has anyone come across the solution for this issue?
Thanks
 

View 6 Replies View Related

Returning The Next Identity Value Before Writing A Record.

Oct 28, 2005

I am storing product information in a SQL Server database table; the product information has no unique fields so I have created an Identity field called ‘uid’. Is there a way of querying the table to find out what value will be given to the next ‘uid’ field before the next record is written to the table? I need to use this as a FK in other tables.

View 3 Replies View Related

Returning Table Structure And First Record

Sep 29, 2006

I need to do something sort of like the DESCRIBE function in MySQL. I need to return the table structure, AND the first row from each column sort of as an example of the data in each column.

then i would just need to run this query on each of my tables...

any ideas?

View 1 Replies View Related

Returning One Single Record Rather Than Multiples

Feb 12, 2007

HiIs it possible to return the results of a query so that instead ofhaving say 10 rows its concatenated, egMy query returns 'M' 10 times, can this be returned as 'M M M M M M MM M M'?ThanksLee

View 3 Replies View Related

Duplicate Record

Jan 30, 2006

Dear All,

I need to identify duplicate records in a table. TableA [ id, firstname, surname] Id like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table

Im not sure how to write this query, can someone help? Thanks in advance!

View 3 Replies View Related

Duplicate Record

Jan 14, 2007

Hi guys how do you hide duplicate records, how would I do a select statement for that

In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )

I have about had it with this database I have been asked to make a report out of

View 14 Replies View Related

SQL 2012 :: Select Top 100 - A Record Is Not Returning From Database

Aug 24, 2015

I have a strange situation with an select. I've noticed that when I select top 100, a record is not returning from the database, but when doing top 101 the record appears on position 41.

The query is like this:

select top 100 GroupId, count(HouseId)
from House h
group by h.GroupId
order by max([DateCreated]) desc

From all discussions about top 100 vs top 101 I've noticed that everybody is saying that top 101 is using another algorithm and we can have a speed problem, but my problem is not about this. With top 100 I'm missing a record that should appear at index 41.

View 9 Replies View Related

Correlated Query Returning Only 1 Record And Repeat

Aug 10, 2014

I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as “Furthermore” will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.

The desired output is:

Yellow Blue
11/23/201311/19/2013
11/19/210310/01/2012
10/01/210210/08/2010
10/08/201012/14/2007

The actual output is:

Yellow Blue
11/23/201311/19/2013
11/19/210311/19/2013
10/01/210211/19/2013
10/08/201011/19/2013
11/23/201310/01/2102
11/19/210310/01/2102
10/01/210210/01/2102
10/08/201010/01/2102

The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.

Here is the SQL:

SELECT Long_List.Yellow,Short_List.Blue
FROM
(
SELECT DISTINCT BirthDate AS Blue
FROM (
SELECT DISTINCT BirthDate FROM citizens

[Code] .....

View 9 Replies View Related

Sql Question - Returning Multiple Rows As One Record

Jul 20, 2005

Hi,In the process of localizing the 'regions' table, we added three newtables. The localized data will be stored in the TokenKeys andTokenValues tables. It would be easier if we did away with theTokeyKeys/TokenValues tables and just added a localeid in the regionstable, but this is the desired schema by the client. Here's theschema:Table: regionsid nameabbreviation1 United StatesUSTable: localesid locale1 en_US2 fr_CATable: TokenKeysid key1 db.regions.name2 db.regions.abbreviationTable: TokenValuesid keyid valuelocaleid1 1 Etas Unis22 2 EU2The old sql was simply this:select name, abbreviation from regionswhich returns:United States, USBut the new sql needs to link in the localized data from the tokeykeysand tokenvalues tables using the localeid... Im trying to figure outwhat the sql statement would look like to return this:Etats Unis, EU (This is supposed to be the French version)My confusion is we are trying to return multiple column values fromthe same column (TokenValues.value) and make them act as separatecolumns in the same record, like it was with the original.Thanks

View 1 Replies View Related

Returning Timestamp From Inserted Record Using @@Identity

Oct 5, 2006

i recently found a little error in a stored procedure that was included in a project handed over to me....

the sp was rather simple. it just inserted a record into a table and returned the identity and the timestamp as follows

IF @@ERROR>0
 BEGIN
 SELECT @int_InterventionID = 0
 RETURN @@ERROR
 END
ELSE
 BEGIN 
 SELECT @int_InterventionIDReturned = MAX(InterventionID) FROM tblIntervention
 SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
 SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
 RETURN 0
 END

i figured that it should be using @@Identity for the interventionIdentity rather than max(InterventionID)

so i changed to...

IF @@ERROR>0
 BEGIN
 SELECT @int_InterventionID = 0
 RETURN @@ERROR
 END
ELSE
 BEGIN 
 SELECT @int_InterventionIDReturned = @@IDENTITY
 SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
 SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
 RETURN 0
 END

it returns the @int_InterventionIDReturned but the timestamp now comes back as null??? why??

how can i ensure that i always get the timestamp of the record it has just inserted

any help greatly appreciated,
Cheers,
Craig

 

 

View 3 Replies View Related

Duplicate Record Trigger

Nov 24, 2006

This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice.
 
            -- duplicate record check
            SET @step = 'Duplicate record'
            IF EXISTS (   
                        SELECT     i.myID, i.Type
                        FROM         INSERTED i INNER JOIN
                                              myDB.dbo.myTable c ON i.myID = c.myID
                        GROUP BY i.myID, i.Type
                        HAVING      (COUNT(*) > 1) AND (i.Type = 'In')
            )
            BEGIN
                        ROLLBACK transaction
                        RAISERROR('Error: step: %s.  rollback is done.', 16, 1, @step)
                        Return
            END
           
What is problem?
 

View 1 Replies View Related

Duplicate Inserted Record

Nov 14, 2007

Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT    CompanyName:    <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>"    insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)"    selectcommand="SELECT * FROM [items]">    <insertparameters>        <asp:controlparameter controlid="txtCompanyName" name="CompanyName" />        <asp:controlparameter controlid="txtPhone" name="Phone" />    </insertparameters></asp:sqldatasource> VBPartial Class Default2    Inherits System.Web.UI.Page    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click        SqlDataSource1.Insert()    End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items   

View 2 Replies View Related

Duplicate Record Question

May 4, 2004

In order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert.

The problem is what to actually test for. When the code throws the exception it is a big long string . .

"Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'"

I just thought that there has to be something simplar to test for than comparing the exception to the above string.

Can anyone tell me of a better way of doing this ?

(by the way I am only using Web Matrix and MSDE in case it matters)

Mark

View 2 Replies View Related

Duplicate Record Problem

Nov 4, 2005

I am working on a web application that utilizes a sql server database.  One of the tables is a large text file that is imported through a DTS package from a Unix server.  For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table.  I need to get rid of these duplicates, but can't seem to get a workable solution.  the query that is needed to get the records is:SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,  tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,  tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,  tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTimeFROM  tblAppointments CROSS JOIN               tblPTDEMO2WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)AND tblAppointments.Insurance1 = 'MED'AND tblAppointments.ApptTypeID <> 'MTG'AND tblAppointments.ApptTypeID <> 'PNV'AND DateDiff("dd", ApptDate, GetDate()) = 0Order By tblAppointments.ApptDateMy first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query.  My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails.  I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own.  I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing.  Any help on this topic would be greatly appreciated.  Thanks.

View 5 Replies View Related

Remove Duplicate Record

Jul 27, 2000

i'm a newbie to sql , anyone can give me suggestions on how to
remove duplicate records in a table, a table also has primary key,
thanks

View 1 Replies View Related

Duplicate Record Problem

Apr 23, 2008

So I'm working on updating and normalizing an old database, and I have some duplicate records that I can't seem to get rid of. Every column is identical, right down to what is supposed to be the key. I can't right a delete query to just isolate one row, and I can't delete (or even udpate) any row in management studio. Any thoughts on how to remove the extra rows?

There is a field that's supposed to be unique, so I can write a simple query to get all of the problem rows. The only thing is that they come back in pairs.

View 2 Replies View Related

Deleting Duplicate Record

Nov 19, 2004

hi to all,

How to delete duplicate record in the recordset?

Thanks...

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved