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

Reduce Recordset to first occurance of a Field

How do you reduce a record set  to the first occurrence of unique id's in a field?  Below is my sp
SELECT     TOP (100) PERCENT DRV_MasterPos.DestID, COUNT(dbo.TubeInfo.TubeInfoID) AS InOrder, TubeInfo_1.RackInfoID
FROM         (SELECT     dbo.TempMasterList.Barcode, dbo.TempMasterTubeMatrix.Row, dbo.TempMasterTubeMatrix.Col, dbo.TempMasterTubeMatrix.DestID,
                                              dbo.TempMasterTubeMatrix.Ident
                       FROM          dbo.TempMasterTubeMatrix INNER JOIN
                                              dbo.TempMasterList ON dbo.TempMasterTubeMatrix.Ident = dbo.TempMasterList.OriginalOrder) AS DRV_MasterPos INNER JOIN
                      dbo.TubeInfo ON DRV_MasterPos.Barcode = dbo.TubeInfo.TubeInfoID AND DRV_MasterPos.Row = dbo.TubeInfo.Row AND
                      DRV_MasterPos.Col = dbo.TubeInfo.[Column] INNER JOIN
                      dbo.TubeInfo AS TubeInfo_1 ON dbo.TubeInfo.TubeInfoID = TubeInfo_1.TubeInfoID AND dbo.TubeInfo.RackInfoID = TubeInfo_1.RackInfoID
GROUP BY DRV_MasterPos.DestID, TubeInfo_1.RackInfoID
ORDER BY InOrder DESC

In the code section is my result set. All I want to be returned is the first 4 records. but this could be (n) records depending on the job.
3	4	ABC003562
2	3	ABC003491
4	3	ABC005580
1	2	ABC003617
2	1	ABC003617
1	1	ABC003491

Open in new window

0
TraciShultz
Asked:
TraciShultz
1 Solution
 
TraciShultzAuthor Commented:
I solve my problem with a couple of Cursors.

1 - I dumped my results into a table with an Identity field.
2- I create a new Table to place my final results in
3 - I created a cursor to select the top 1 percent based on DestID and the Identity field
4 - I had to do an aditional check to make sure all DestID were being used. If htere were any missing I would add them
5- Create a final table with my complete result set.
USE [SamManagerTubes]
GO
/****** Object:  StoredProcedure [dbo].[RunXML_CreateDestRackReorderTable]    Script Date: 02/10/2009 15:21:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		TShultz
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[RunXML_CreateDestRackReorderTable] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempFinalDestRackReOrder]') AND type in (N'U'))
	DROP TABLE [dbo].[TempFinalDestRackReOrder]
 
	SELECT     TOP (100) PERCENT IDENTITY(int, 1,1) AS Ident, DRV_MasterPos.DestID, COUNT(dbo.TubeInfo.TubeInfoID) AS InOrder, dbo.TubeInfo.RackInfoID
	INTO TempFinalDestRackReOrder
	FROM         (SELECT     dbo.TempMasterList.Barcode, dbo.TempMasterTubeMatrix.Row, dbo.TempMasterTubeMatrix.Col, dbo.TempMasterTubeMatrix.DestID, 
												  dbo.TempMasterTubeMatrix.Ident
						   FROM          dbo.TempMasterTubeMatrix INNER JOIN
												  dbo.TempMasterList ON dbo.TempMasterTubeMatrix.Ident = dbo.TempMasterList.OriginalOrder) AS DRV_MasterPos INNER JOIN
						  dbo.TubeInfo ON DRV_MasterPos.Barcode = dbo.TubeInfo.TubeInfoID AND DRV_MasterPos.Row = dbo.TubeInfo.Row AND 
						  DRV_MasterPos.Col = dbo.TubeInfo.[Column] INNER JOIN
						  dbo.TubeInfo AS TubeInfo_1 ON dbo.TubeInfo.TubeInfoID = TubeInfo_1.TubeInfoID AND dbo.TubeInfo.RackInfoID = TubeInfo_1.RackInfoID
	GROUP BY DRV_MasterPos.DestID, dbo.TubeInfo.RackInfoID
	ORDER BY InOrder DESC
 
 
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempFinalDestRackNew]') AND type in (N'U'))
	DROP TABLE [dbo].[TempFinalDestRackNew]
 
	CREATE TABLE [dbo].[TempFinalDestRackNew](
		[RackInfoID] [nvarchar](50) NOT NULL,
		[Ident] [int]  NOT NULL
	) ON [PRIMARY]
 
 
	DECLARE @DestRackOrder Int
	DECLARE c1 CURSOR READ_ONLY
	FOR
	SELECT     Ident
	FROM         TempFinalDestRack
	ORDER BY Ident
 
	OPEN c1
 
	FETCH NEXT FROM c1
	INTO @DestRackOrder 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO TempFinalDestRackNew (RackInfoID, Ident)
		SELECT     TOP (1) RackInfoID, DestID
		FROM         TempFinalDestRackReOrder
		WHERE     (DestID = @DestRackOrder)
		
		FETCH NEXT FROM c1
		INTO @DestRackOrder 
	END
	CLOSE c1
	DEALLOCATE c1
 
 
 
	DECLARE @DestRackUnusedOrder Int
	DECLARE c1 CURSOR READ_ONLY
	FOR
	SELECT     TOP (100) PERCENT Ident
	FROM         TempFinalDestRack
	WHERE     (Ident NOT IN
							  (SELECT     Ident
								FROM          TempFinalDestRackNew))
	ORDER BY Ident
	OPEN c1
 
	FETCH NEXT FROM c1
	INTO @DestRackUnusedOrder 
	WHILE @@FETCH_STATUS = 0
		BEGIN
		INSERT INTO TempFinalDestRackNew (RackInfoID, Ident)
		SELECT     TOP (1) RackInfoID, Ident
		FROM         TempFinalDestRack
		WHERE     (RackInfoID NOT IN
								  (SELECT     RackInfoID
									FROM          TempFinalDestRackNew))
		ORDER BY Ident
		FETCH NEXT FROM c1
		INTO @DestRackUnusedOrder 
	END
	CLOSE c1
	DEALLOCATE c1
 
 
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempFinalDestRack]') AND type in (N'U'))
	DROP TABLE [dbo].[TempFinalDestRack]
	
	SELECT     RackInfoID, Ident
	INTO TempFinalDestRack
	FROM         TempFinalDestRackNew
	ORDER BY Ident
 
 
 
END

Open in new window

0
 
dqmqCommented:
Give this a try:
SELECT DRV_MasterPos.DestID, COUNT(dbo.TubeInfo.TubeInfoID) AS InOrder, TubeInfo_1.RackInfoID
FROM         (SELECT     dbo.TempMasterList.Barcode, dbo.TempMasterTubeMatrix.Row, dbo.TempMasterTubeMatrix.Col, dbo.TempMasterTubeMatrix.DestID, 
dbo.TempMasterTubeMatrix.Ident,
Rank() Over (Order By DestID) as seq 
                       FROM          dbo.TempMasterTubeMatrix INNER JOIN
                                              dbo.TempMasterList ON dbo.TempMasterTubeMatrix.Ident = dbo.TempMasterList.OriginalOrder) AS DRV_MasterPos INNER JOIN
                      dbo.TubeInfo ON DRV_MasterPos.Barcode = dbo.TubeInfo.TubeInfoID AND DRV_MasterPos.Row = dbo.TubeInfo.Row AND 
                      DRV_MasterPos.Col = dbo.TubeInfo.[Column] INNER JOIN
                      dbo.TubeInfo AS TubeInfo_1 ON dbo.TubeInfo.TubeInfoID = TubeInfo_1.TubeInfoID AND dbo.TubeInfo.RackInfoID = TubeInfo_1.RackInfoID
where seq <=4  
GROUP BY DRV_MasterPos.DestID, TubeInfo_1.RackInfoID
ORDER BY InOrder DESC

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now