Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL SP GET UNIQUE RECORD BY ID

Posted on 2013-05-17
11
Medium Priority
?
391 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 49

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 49

Expert Comment

by:PortletPaul
ID: 39176658
are the records always in sets of 3?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:WTsuk
ID: 39176679
No.
0
 
LVL 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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