Solved

SQL SP GET UNIQUE RECORD BY ID

Posted on 2013-05-17
11
389 Views
Last Modified: 2013-05-18
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
Comment
Question by:WTsuk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176616
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
 

Author Comment

by:WTsuk
ID: 39176647
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176658
are the records always in sets of 3?
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:WTsuk
ID: 39176679
No.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39176686
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39176691
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
 

Author Comment

by:WTsuk
ID: 39176702
I will create Test#a with Unique_Row_id and insert into Test#a. then insert into Temp#a with your query sample.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39176708
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
 

Author Comment

by:WTsuk
ID: 39176730
Could you give me sample of cursor to fetch?  I never used this .
0
 

Author Comment

by:WTsuk
ID: 39176738
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39176748
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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question