• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

SQL SP GET UNIQUE RECORD BY ID

SQL 2000 Table Temp#a have  ID, Jobno, name, address, ctz, value1, value2,....value20

This table have duplicate ID.

example.

ID     Jobno   name                 address               ctz                            value1----val20

100   a001    Tim Smith        100 High St    Town1 ST1  Zip11111  Valuea     Valueb  ,,,,
100   a001    Tim Smith        100 High St    Town1 ST1  Zip11111  Valuea      Valuex ,,,,
100   a003    Timmy Smith   300 Arrow St Town2  ST2  Zip22222  Valueb   Valuec  ,,,,
200   a001    Tim Smith        100 High St    Town1 ST1  Zip11111  Valuea     Valueb ,,,,
200   a001    Tim Smith        100 High St    Town1 ST1  Zip11111  Valuea      Value9  ,,,,
200   a003    Timmy Smith   300 Arrow St Town2  ST2  Zip22222  Valueb   Valuec  ....



How to get just first row by ID? (one record 100 and one record for 200)
0
WTsuk
Asked:
WTsuk
  • 5
  • 4
  • 2
1 Solution
 
PortletPaulfreelancerCommented:
what is the code that builds the temp table?
is there any need for the additional rows in that temp table?

note there are differences being displayed in name and address, so the rows are probably already considered 'distinct'

I think the solution needed is before inserting to the table.

just to confirm it is sql 2000 correct?
i.e. no row_number() function

need to see the code that builds this table.
0
 
WTsukAuthor Commented:
It is sql 2000 and distinct.  I just need to select any first hit record by ID.  Customer do not want to delete any record but I need to select one reocrd by ID for report.
0
 
PortletPaulfreelancerCommented:
are the records always in sets of 3?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
WTsukAuthor Commented:
No.
0
 
PortletPaulfreelancerCommented:
apologies for the Q&A, the features I normally used aren't available in 2000

any chance of getting to see all fields in this table? (the table definition)
in particular is there a unique identifier for each row?
can the table definition be changed if needed?
0
 
PortletPaulfreelancerCommented:
IF that table has a unique row identity, something like this might work...

e.g.
ALTER TABLE Temp#a ADD unique_row_id int identity;

then do the inserts as usual

then for query

select *
from Temp#a as T
where [unique_row_id] = (select min([unique_row_id])
                                                  from Temp#a
                                                  where Temp#a.ID = T.ID
                                                  group by
                                                      Temp#a.ID)

is this approach a possibility?
0
 
WTsukAuthor Commented:
I will create Test#a with Unique_Row_id and insert into Test#a. then insert into Temp#a with your query sample.
0
 
Ioannis ParaskevopoulosCommented:
It is not elegant but you could use a cursor to fetch the first row of each id in a temp table and then display that table.

Giannis
0
 
WTsukAuthor Commented:
Could you give me sample of cursor to fetch?  I never used this .
0
 
WTsukAuthor Commented:
This is modified SP with your SQL sample

CREATE  PROC [dbo].[PreGetMailingList_0519]
(
@AllJobCodes nvarchar(2000),
@AllZipCodes NText --nvarchar(2000)
)
AS



declare @Code1 varchar(100)                              
set @Code1='''M'',''S'',''X'''

exec('


            
            DECLARE @MasterJob TABLE
            (
            jobno VARCHAR(5),
            MasterJobNo VARCHAR(5)
            )


            INSERT INTO @MasterJob
            Select jobno,MasterJobNo
             from INR_JobShedule where jobNo in('+ @AllJobCodes + ')

            --========================================================2009-01-06
            DECLARE @TMasterJob table(MailMasterJobNo CHAR(5))
            INSERT INTO @TMasterJob
            SELECT DISTINCT MasterJobNo
            FROM INR_JobShedule
            where jobNo in('+ @AllJobCodes + ')
            --========================================================2009-01-06

            DELETE INRV5_PreMailingList_Merge
            FROM       INRV5_PreMailingList_Merge GM INNER JOIN @MasterJob GJ ON GM.jobno=GJ.MasterJobNo
             WHERE GM.JobNo = GJ.MasterJobNo



            DELETE INRV5_PreMailingList
            FROM       INRV5_PreMailingList PM INNER JOIN @MasterJob PJ ON PM.jobno=PJ.MasterJobNo
             WHERE PM.JobNo = PJ.MasterJobNo

            


            INSERT INTO [INRV5_PreMailingList_Merge]
                  (JobNo, [Unique ID], [First name], [Last name], [Address 1], [Address 2], [Address 3], Company_Name, City, State, [5 digit zip],
                                  [last four digits of zip (after the -)], [Delivery Point Barcode], [Check Digit], [Carrier Route], ZipCode, ProfCode, ProfGroup, AnalysisType, Contact_Title,
                                  [Address 4], StudentID, Type, IMB, CountOfMailsSent, MailStatus)



            SELECT  DISTINCT
                  MJ.MasterJobNo, [Unique ID], [First name], [Last name], [Address 1], [Address 2], [Address 3], Company_Name, City, State, [5 digit zip],
                                  [last four digits of zip (after the -)], [Delivery Point Barcode], [Check Digit], [Carrier Route],  [5 digit zip], ProfCode, ProfGroup, AnalysisType, Contact_Title,
                                 [Address 4], StudentID, Type, IMB, CountOfMailsSent,  ''Y''

            FROM INR_MailingList IM INNER JOIN @MasterJob MJ ON IM.jobno=MJ.jobNo

             WHERE IM.[5 digit zip] in('+ @AllZipCodes + ') --Order By IM.[unique id]





       DECLARE @JobID AS VARCHAR(10)
      SET @JobID = (
      SELECT DISTINCT  MJ.MasterJobNo
      FROM  @MasterJob MJ
      where MJ.jobNo in('+ @AllJobCodes + '))

      INSERT INTO INRV5_PreMailingList
      select *
      from  INRV5_PreMailingList_Merge as T
      where [unique_row_id] = (select max([unique_row_id])
                                                  from INRV5_PreMailingList_Merge
                                                  where INRV5_PreMailingList_Merge.[Unique ID]  = T.[Unique ID] AND INRV5_PreMailingList_Merge.JobNO = @JobID
                                                  group by
                                                      INRV5_PreMailingList_Merge.[Unique ID] )





      
        EXEC INRv5_CountPreGetMasterMailingList   @JobID


      
      
')
GO
0
 
Ioannis ParaskevopoulosCommented:
DECLARE @ID INT

--Create a temp table that has the same structure as your table
SELECT TOP 1 * 
INTO #temp
FROM Temp#a

DELETE FROM #temp 
---------------------------------------------------------------------------

DECLARE cursorName CURSOR -- Declare cursor
FOR
Select ID FROM Temp#a
 
OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName
INTO @ID 
 
 INSERT INTO #temp
 SELECT TOP 1 * 
 FROM Temp#a
 WHERE ID = @ID

WHILE @@FETCH_STATUS = 0
 
BEGIN
 
   FETCH NEXT FROM cursorName
   INTO @ID
 
 INSERT INTO #temp
 SELECT TOP 1 * 
 FROM Temp#a
 WHERE ID = @ID

END
 
CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor

SELECT * FROM Temp#a

Open in new window


Giannis
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now