Solved

SQL SP GET UNIQUE RECORD BY ID

Posted on 2013-05-17
11
383 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
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
 

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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 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

21 Experts available now in Live!

Get 1:1 Help Now