Solved

SQL SP GET UNIQUE RECORD BY ID

Posted on 2013-05-17
11
381 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
  • 5
  • 4
  • 2
11 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
are the records always in sets of 3?
0
 

Author Comment

by:WTsuk
Comment Utility
No.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Could you give me sample of cursor to fetch?  I never used this .
0
 

Author Comment

by:WTsuk
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

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…
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now