how to select several columns with one being distinct

Question

how to select several columns with one being distinct

perry.59

SSC Enthusiast

Points: 102
More actions
May 17, 2017 at 10:08 am

#342921

As you can see in the screenshot below (I hope it is legible) I have a basic query. I need it to return the columns selected, but only one row per "componentId". Writing it out as SELECT DISTINCT does not do any good because the rows are distinct, each with a different "RefId" which is the PK.
I thought I could put the GROUP BY clause at the end grouping by the componentId but that gives an error unless I include all the columns and that just gives me what I had in the first place. In this example I want it to return just one row, either one would be fine.
Do I need to come up with some type of nested select statement?
Thanks for any help!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

What are you expecting your output to look like? If there are 3 RefIds for one component, how do you want this displayed if not in 3 rows?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

perry.59 - Wednesday, May 17, 2017 10:08 AM
As you can see in the screenshot below (I hope it is legible) I have a basic query. I need it to return the columns selected, but only one row per "componentId". Writing it out as SELECT DISTINCT does not do any good because the rows are distinct, each with a different "RefId" which is the PK.
I thought I could put the GROUP BY clause at the end grouping by the componentId but that gives an error unless I include all the columns and that just gives me what I had in the first place. In this example I want it to return just one row, either one would be fine.
Do I need to come up with some type of nested select statement?
Thanks for any help!

SheetRef is also different. What would the expected results be? Can you post sample data and your query in a way that we can just copy, paste and run it?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

perry.59 - Wednesday, May 17, 2017 10:08 AM
As you can see in the screenshot below (I hope it is legible) I have a basic query. I need it to return the columns selected, but only one row per "componentId". Writing it out as SELECT DISTINCT does not do any good because the rows are distinct, each with a different "RefId" which is the PK.
I thought I could put the GROUP BY clause at the end grouping by the componentId but that gives an error unless I include all the columns and that just gives me what I had in the first place. In this example I want it to return just one row, either one would be fine.
Do I need to come up with some type of nested select statement?
Thanks for any help!

Use ROW_NUMBER() OVER (PARTITION BY componentid ORDER BY whatever)

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

caffeinated Ten Centuries Points: 1271 More actions · Answer 4

You'll need to explain how you want to handle different values for the same column to be returned?

Perhaps concatenate the values into a comma delimited value?
Or encoded via a hash?

For example, even with out the PK (partRefID), the sample results you attached has at least two values: Page-2 and Page-3, for ComponentId = 1.

perry.59 SSC Enthusiast Points: 102 More actions · Answer 5

Ok, here's some code. This just creates two tables, a relation and adds some data
USE [test] GO /****** Object: Table PartRef] Script Date: 5/17/2017 11:01:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

--===== If the test table already exists, drop it
     IF OBJECT_ID('test..PartRef','U') IS NOT NULL
        DROP TABLE PartRef
GO
--===== If the test table already exists, drop it
     IF OBJECT_ID('test..PartDef','U') IS NOT NULL
        DROP TABLE PartDef

GOCREATE TABLE [PartDef](
[PartDefId] [bigint] IDENTITY(1,1) NOT NULL,
[PartNumber] [nvarchar](50) NULL,
[PartNumberAlt] [nvarchar](50) NULL,
[Series] [nvarchar](255) NULL,
[WiringPartName] [nvarchar](70) NULL,
[Description] [nvarchar](50) NULL,
[ArticleName] [nvarchar](255) NULL,
[Stocknumber] [nvarchar](150) NULL,
[ElementCount] [int] NULL,
[TerminalCount] [int] NULL,
[PartType] [int] NOT NULL,
[PartClass] [nvarchar](50) NOT NULL,
[FamilyCode] [nvarchar](70) NULL,
[SymbolName] [nvarchar](255) NULL,
[Weight] [float] NULL,
[WeightUnits] [nchar](10) NULL,
[CostEa] [money] NULL,
[Manufacturer] [nvarchar](255) NOT NULL,
[ManufacturerId] [bigint] NULL,
[Supplier] [nvarchar](255) NULL,
[LibraryName] [nvarchar](255) NULL,
[Isobsolete] [int] NULL,
[UseVoltage] [nvarchar](70) NULL,
[CoVoltage] [nvarchar](70) NULL,
[UseFrequency] [nvarchar](70) NULL,
[CoFrequency] [nvarchar](70) NULL,
[ConnectionType] [nvarchar](70) NULL,
[MaxSection] [float] NULL,
[MaxGauge] [nvarchar](70) NULL,
[MinSection] [float] NULL,
[MinGauge] [nvarchar](70) NULL,
[PartMate] [nvarchar](255) NULL,
[ShellType] [nvarchar](10) NULL,
[Value1] [nvarchar](70) NULL,
[Value2] [nvarchar](70) NULL,
[Value3] [nvarchar](70) NULL,
[Value4] [nvarchar](70) NULL,
[Value5] [nvarchar](70) NULL,
[Value6] [nvarchar](70) NULL,
[Inserted] [datetime] NULL,
[InsertedUser] [nvarchar](50) NULL,
[Updated] [datetime] NULL,
[UpdatedUser] [nvarchar](50) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [PartDef] ON

INSERT [PartDef] ([PartDefId], [PartNumber], [PartNumberAlt], [Series], [WiringPartName], [Description], [ArticleName], [Stocknumber], [ElementCount], [TerminalCount], [PartType], [PartClass], [FamilyCode], [SymbolName], [Weight], [WeightUnits], [CostEa], [Manufacturer], [ManufacturerId], [Supplier], [LibraryName], [Isobsolete], [UseVoltage], [CoVoltage], [UseFrequency], [CoFrequency], [ConnectionType], [MaxSection], [MaxGauge], [MinSection], [MinGauge], [PartMate], [ShellType], [Value1], [Value2], [Value3], [Value4], [Value5], [Value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (4, N'M39029/57-356', N'', N'', N'', N'Contact, Socket 22-26 AWG', N'', N'', 0, 0, 1, N'Contact', N'CONT', N'', 0, N'english   ', 0.0000, N'None', 0, N'QPL', N'NONE', 0, N'', N'', N'', N'', N'Socket (female)', 0, N'20', 0, N'26', NULL, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-03-08 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-03-08 00:00:00.000' AS DateTime), N'perry')
INSERT [PartDef] ([PartDefId], [PartNumber], [PartNumberAlt], [Series], [WiringPartName], [Description], [ArticleName], [Stocknumber], [ElementCount], [TerminalCount], [PartType], [PartClass], [FamilyCode], [SymbolName], [Weight], [WeightUnits], [CostEa], [Manufacturer], [ManufacturerId], [Supplier], [LibraryName], [Isobsolete], [UseVoltage], [CoVoltage], [UseFrequency], [CoFrequency], [ConnectionType], [MaxSection], [MaxGauge], [MinSection], [MinGauge], [PartMate], [ShellType], [Value1], [Value2], [Value3], [Value4], [Value5], [Value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (5, N'M39029/58-360', N'', N'', N'', N'Contact, Pin 22-28 AWG', N'', N'', 0, 0, 1, N'Contact', N'CONT', N'', 0, N'english   ', 0.0000, N'None', 0, N'QPL', N'NONE', 0, N'', N'', N'', N'', N'Pin (male)', 0, N'22', 0, N'24', NULL, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-03-08 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-03-08 00:00:00.000' AS DateTime), N'perry')
INSERT [PartDef] ([PartDefId], [PartNumber], [PartNumberAlt], [Series], [WiringPartName], [Description], [ArticleName], [Stocknumber], [ElementCount], [TerminalCount], [PartType], [PartClass], [FamilyCode], [SymbolName], [Weight], [WeightUnits], [CostEa], [Manufacturer], [ManufacturerId], [Supplier], [LibraryName], [Isobsolete], [UseVoltage], [CoVoltage], [UseFrequency], [CoFrequency], [ConnectionType], [MaxSection], [MaxGauge], [MinSection], [MinGauge], [PartMate], [ShellType], [Value1], [Value2], [Value3], [Value4], [Value5], [Value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (32, N'011-01264-00', N'', N'', N'', N'Display, Avionics, GDU 620, Black', N'', N'', 3, 0, 0, N'Component', N'', N'', 0, N'english   ', 0.0000, N'Garmin', 0, N'None', N'None', 0, N'', N'', N'', N'', N'', 0, N'', 0, N'', N'', N'Plug', N'', N'', N'', N'', N'', N'', CAST(N'2017-04-28 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-01 00:00:00.000' AS DateTime), N'perry')
INSERT [PartDef] ([PartDefId], [PartNumber], [PartNumberAlt], [Series], [WiringPartName], [Description], [ArticleName], [Stocknumber], [ElementCount], [TerminalCount], [PartType], [PartClass], [FamilyCode], [SymbolName], [Weight], [WeightUnits], [CostEa], [Manufacturer], [ManufacturerId], [Supplier], [LibraryName], [Isobsolete], [UseVoltage], [CoVoltage], [UseFrequency], [CoFrequency], [ConnectionType], [MaxSection], [MaxGauge], [MinSection], [MinGauge], [PartMate], [ShellType], [Value1], [Value2], [Value3], [Value4], [Value5], [Value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (14, N'M39029/63-368', N'', N'', N'', N'Contact, Socket, 20-24 AWG', N'', N'', 0, 0, 1, N'Contact', N'CONT', N'', 0, N'english   ', 0.0000, N'None', 0, N'QPL', N'None', 0, N'', N'', N'', N'', N'Socket (female)', 0, N'20', 0, N'24', N'M39029/64-369', NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-03-09 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-03-09 00:00:00.000' AS DateTime), N'perry')
INSERT [PartDef] ([PartDefId], [PartNumber], [PartNumberAlt], [Series], [WiringPartName], [Description], [ArticleName], [Stocknumber], [ElementCount], [TerminalCount], [PartType], [PartClass], [FamilyCode], [SymbolName], [Weight], [WeightUnits], [CostEa], [Manufacturer], [ManufacturerId], [Supplier], [LibraryName], [Isobsolete], [UseVoltage], [CoVoltage], [UseFrequency], [CoFrequency], [ConnectionType], [MaxSection], [MaxGauge], [MinSection], [MinGauge], [PartMate], [ShellType], [Value1], [Value2], [Value3], [Value4], [Value5], [Value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (33, N'011-01264-10', N'', N'', N'', N'Display, Avionics, GDU 620, Grey', N'', N'', 3, 0, 0, N'Component', N'', N'', 0, N'english   ', 0.0000, N'Garmin', 0, N'None', N'None', 0, N'', N'', N'', N'', N'', 0, N'System.Data.DataRowView', 0, N'System.Data.DataRowView', N'', N'Plug', N'', N'', N'', N'', N'', N'', CAST(N'2017-05-01 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-01 00:00:00.000' AS DateTime), N'perry')
INSERT [PartDef] ([PartDefId], [PartNumber], [PartNumberAlt], [Series], [WiringPartName], [Description], [ArticleName], [Stocknumber], [ElementCount], [TerminalCount], [PartType], [PartClass], [FamilyCode], [SymbolName], [Weight], [WeightUnits], [CostEa], [Manufacturer], [ManufacturerId], [Supplier], [LibraryName], [Isobsolete], [UseVoltage], [CoVoltage], [UseFrequency], [CoFrequency], [ConnectionType], [MaxSection], [MaxGauge], [MinSection], [MinGauge], [PartMate], [ShellType], [Value1], [Value2], [Value3], [Value4], [Value5], [Value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (21, N'M39029/64-369', N'', N'', N'', N'Contact, Pin, 20-24 AWG', N'', N'', 0, 0, 1, N'Contact', N'CONT', N'', 0, N'english   ', 0.0000, N'Amphenol', 0, N'QPL', N'None', 0, N'', N'', N'', N'', N'Pin (male)', 0, N'20', 0, N'24', N'M39029/63-368', N'', N'', N'', N'', N'', N'', N'', CAST(N'2017-03-24 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-03-24 00:00:00.000' AS DateTime), N'perry')
INSERT [PartDef] ([PartDefId], [PartNumber], [PartNumberAlt], [Series], [WiringPartName], [Description], [ArticleName], [Stocknumber], [ElementCount], [TerminalCount], [PartType], [PartClass], [FamilyCode], [SymbolName], [Weight], [WeightUnits], [CostEa], [Manufacturer], [ManufacturerId], [Supplier], [LibraryName], [Isobsolete], [UseVoltage], [CoVoltage], [UseFrequency], [CoFrequency], [ConnectionType], [MaxSection], [MaxGauge], [MinSection], [MinGauge], [PartMate], [ShellType], [Value1], [Value2], [Value3], [Value4], [Value5], [Value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (27, N'206486-2', N'', N'', N'', N'Connector, 9-pin CPC, Jack', N'', N'', 0, 9, 0, N'Connector', N'CONN', N'', 0, N'english   ', 0.0000, N'Amphenol', 0, N'QPL', N'None', 0, N'', N'', N'', N'', N'', 0, N'20', 0, N'24', N'206485-1', N'Jack', N'', N'', N'', N'', N'', N'', CAST(N'2017-03-28 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-04-19 00:00:00.000' AS DateTime), N'perry')
INSERT [PartDef] ([PartDefId], [PartNumber], [PartNumberAlt], [Series], [WiringPartName], [Description], [ArticleName], [Stocknumber], [ElementCount], [TerminalCount], [PartType], [PartClass], [FamilyCode], [SymbolName], [Weight], [WeightUnits], [CostEa], [Manufacturer], [ManufacturerId], [Supplier], [LibraryName], [Isobsolete], [UseVoltage], [CoVoltage], [UseFrequency], [CoFrequency], [ConnectionType], [MaxSection], [MaxGauge], [MinSection], [MinGauge], [PartMate], [ShellType], [Value1], [Value2], [Value3], [Value4], [Value5], [Value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (28, N'206485-1', N'', N'', N'', N'Connector, 9-pin CPC, Plug', N'', N'', 0, 9, 0, N'Connector', N'CONN', N'', 0, N'english   ', 0.0000, N'Amphenol', 0, N'QPL', N'None', 0, N'0', N'', N'', N'', N'', 0, N'20', 0, N'24', N'206486-2', N'Plug', N'', N'', N'', N'', N'', N'', CAST(N'2017-03-28 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-04-20 00:00:00.000' AS DateTime), N'perry')
SET IDENTITY_INSERT [PartDef] OFF
/****** Object: Index [PK_PartDefId]    Script Date: 5/19/2017 12:21:54 PM ******/
ALTER TABLE [PartDef] ADD CONSTRAINT [PK_PartDefId] PRIMARY KEY NONCLUSTERED
(
[PartDefId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [PartRef](
[PartRefId] [bigint] IDENTITY(1,1) NOT NULL,
[PartDefId] [bigint] NOT NULL,
[Name] [nvarchar](255) NULL,
[PartDescription] [nvarchar](50) NULL,
[DocumentId] [bigint] NULL,
[SheetRef] [nvarchar](50) NULL,
[LocationId] [int] NULL,
[Effectivity] [nvarchar](50) NULL,
[EffectivityId] [bigint] NOT NULL,
[EffectivityDesignatorId] [int] NULL,
[Designator] [nvarchar](50) NOT NULL,
[DesignatorPrefix] [nvarchar](10) NOT NULL,
[DesignatorSuffix] [nvarchar](10) NULL,
[DesignatorSequence] [int] NULL,
[DesignatorSegment] [nvarchar](5) NULL,
[DesignatorTypeCode] [nvarchar](10) NULL,
[DesignatorFunction] [nvarchar](10) NULL,
[Shape] [nvarchar](100) NULL,
[Existing] [bit] NULL,
[Stowed] [bit] NULL,
[Reference] [nvarchar](50) NULL,
[Valid] [bit] NULL,
[tagorderno] [int] NULL,
[tagmanual] [int] NULL,
[taglock] [int] NULL,
[tagroot] [nvarchar](70) NULL,
[tagposition] [int] NULL,
[TermNumber] [int] NOT NULL,
[TermMark] [nvarchar](10) NULL,
[TermMnemo] [nvarchar](70) NULL,
[ParentId] [bigint] NULL,
[ComponentId] [bigint] NULL,
[AssemblyId] [uniqueidentifier] NULL,
[RefType] [int] NULL,
[IsAvailable] [bit] NULL,
[includeInBOM] [bit] NULL,
[value1] [nvarchar](70) NULL,
[value2] [nvarchar](70) NULL,
[value3] [nvarchar](70) NULL,
[value4] [nvarchar](70) NULL,
[value5] [nvarchar](70) NULL,
[value6] [nvarchar](70) NULL,
[Inserted] [datetime] NULL,
[InsertedUser] [nvarchar](50) NULL,
[Updated] [datetime] NULL,
[UpdatedUser] [nvarchar](50) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [PartRef] ON

INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (101, 32, N'GDU 620', N'Component', 39, N'Page-2', 0, N'Baron', 11, 0, N'900', N'M', N'', 0, N'', N'', N'', N'710', 0, 0, NULL, 0, 0, 0, 0, N'', 0, 0, N'0', N'', NULL, 1, NULL, 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-15 11:10:05.000' AS DateTime), N'perry', CAST(N'2017-05-15 11:10:06.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (102, 32, N'GDU 620', N'Component', 39, N'Page-1', 0, N'Baron', 11, 0, N'900', N'M', N'', 0, N'', N'', N'', N'28', 0, 0, NULL, 0, 0, 0, 0, N'', 0, 0, N'0', N'', NULL, 2, NULL, 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-16 12:17:35.000' AS DateTime), N'perry', CAST(N'2017-05-16 12:17:35.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (103, 32, N'GDU 620', N'Component', 39, N'Page-1', 0, N'Baron', 11, 0, N'900', N'M', N'', 0, N'', N'', N'', N'28', 0, 0, NULL, 0, 0, 0, 0, N'', 0, 0, N'0', N'', NULL, 2, NULL, 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-16 12:17:35.000' AS DateTime), N'perry', CAST(N'2017-05-16 12:17:35.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (104, 32, N'GDU 620', N'Component', 39, N'Page-3', 0, N'Baron', 11, 0, N'900', N'M', N'', 0, N'', N'', N'', N'25', 0, 0, NULL, 0, 0, 0, 0, N'', 0, 0, N'0', N'', NULL, 1, NULL, 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-16 11:22:29.000' AS DateTime), N'perry', CAST(N'2017-05-16 11:22:29.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (77, 27, N'', N'connector', 39, N'Page-2', 0, N'Baron', 11, 0, N'904', N'M', N'', 0, N'', N'', N'', N'693', 0, 0, N'', 1, 0, 0, 0, N'', 0, 0, N'0', N'', 0, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (78, 14, NULL, N'contact', 39, N'Page-2', 0, N'Baron', 11, 0, N'', N'', N'', 0, N'', N'', N'', N'', NULL, 0, N'77', NULL, 0, 0, 0, N'', 0, 0, N'1', N'', 27, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (79, 14, NULL, N'contact', 39, N'Page-2', 0, N'Baron', 11, 0, N'', N'', N'', 0, N'', N'', N'', N'', NULL, 0, N'77', NULL, 0, 0, 0, N'', 0, 1, N'2', N'', 27, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (80, 14, NULL, N'contact', 39, N'Page-2', 0, N'Baron', 11, 0, N'', N'', N'', 0, N'', N'', N'', N'', NULL, 0, N'77', NULL, 0, 0, 0, N'', 0, 2, N'3', N'', 27, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (81, 14, NULL, N'contact', 39, N'Page-2', 0, N'Baron', 11, 0, N'', N'', N'', 0, N'', N'', N'', N'', NULL, 0, N'', NULL, 0, 0, 0, N'', 0, 3, N'4', N'', 27, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 1, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (82, 14, NULL, N'contact', 39, N'Page-2', 0, N'Baron', 11, 0, N'', N'', N'', 0, N'', N'', N'', N'', NULL, 0, N'', NULL, 0, 0, 0, N'', 0, 4, N'5', N'', 27, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 1, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (83, 14, NULL, N'contact', 39, N'Page-2', 0, N'Baron', 11, 0, N'', N'', N'', 0, N'', N'', N'', N'', NULL, 0, N'', NULL, 0, 0, 0, N'', 0, 5, N'6', N'', 27, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 1, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (84, 14, NULL, N'contact', 39, N'Page-2', 0, N'Baron', 11, 0, N'', N'', N'', 0, N'', N'', N'', N'', NULL, 0, N'87', NULL, 0, 0, 0, N'', 0, 6, N'7', N'', 27, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (85, 14, NULL, N'contact', 39, N'Page-2', 0, N'Baron', 11, 0, N'', N'', N'', 0, N'', N'', N'', N'', NULL, 0, N'87', NULL, 0, 0, 0, N'', 0, 7, N'8', N'', 27, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (86, 14, NULL, N'contact', 39, N'Page-2', 0, N'Baron', 11, 0, N'', N'', N'', 0, N'', N'', N'', N'', NULL, 0, N'87', NULL, 0, 0, 0, N'', 0, 8, N'9', N'', 27, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (105, 32, N'GDU 620', N'Component', 39, N'Page-3', 0, N'Baron', 11, 0, N'900', N'M', N'', 0, N'', N'', N'', N'28', 0, 0, NULL, 0, 0, 0, 0, N'', 0, 0, N'0', N'', NULL, 1, NULL, 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-16 12:17:35.000' AS DateTime), N'perry', CAST(N'2017-05-16 12:17:35.000' AS DateTime), N'perry')
INSERT [PartRef] ([PartRefId], [PartDefId], [Name], [PartDescription], [DocumentId], [SheetRef], [LocationId], [Effectivity], [EffectivityId], [EffectivityDesignatorId], [Designator], [DesignatorPrefix], [DesignatorSuffix], [DesignatorSequence], [DesignatorSegment], [DesignatorTypeCode], [DesignatorFunction], [Shape], [Existing], [Stowed], [Reference], [Valid], [tagorderno], [tagmanual], [taglock], [tagroot], [tagposition], [TermNumber], [TermMark], [TermMnemo], [ParentId], [ComponentId], [AssemblyId], [RefType], [IsAvailable], [includeInBOM], [value1], [value2], [value3], [value4], [value5], [value6], [Inserted], [InsertedUser], [Updated], [UpdatedUser]) VALUES (87, 27, N'', N'connector', 39, N'Page-3', 0, N'Baron', 11, 0, N'904', N'M', N'', 0, N'', N'', N'', N'8', 1, 0, N'77', 0, 0, 0, 0, N'', 0, 0, N'0', N'', 0, 0, N'26380f25-8e34-496d-93da-1f96f523bcff', 0, 0, NULL, N'', N'', N'', N'', N'', N'', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry', CAST(N'2017-05-12 00:00:00.000' AS DateTime), N'perry')
SET IDENTITY_INSERT [PartRef] OFF
/****** Object: Index [PK_PartRefId] Script Date: 5/17/2017 11:01:50 AM ******/
ALTER TABLE [PartRef] ADD CONSTRAINT [PK_PartRefId] PRIMARY KEY NONCLUSTERED
(
[PartRefId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [PartRef] WITH CHECK ADD CONSTRAINT [FK_PartRef_PartDef] FOREIGN KEY([PartDefId])
REFERENCES [PartDef] ([PartDefId])
GO
ALTER TABLE [PartRef] CHECK CONSTRAINT [FK_PartRef_PartDef]
GO

perry.59 SSC Enthusiast Points: 102 More actions · Answer 6

sorry for breaking this up, this website is choking.
So here is a small script that does not quite get me where I want to go..
USE [test] GO

SELECT
partRefId,
Name,
DocumentId,
SheetRef,
LocationId,
Effectivity,
EffectivityId,
DesignatorPrefix,
Designator,
DesignatorSuffix,
ComponentId
FROM PartRef pr
JOIN PartDef pd
ON pd.PartDefId = pr.PartDefId
Where pd.PartClass = 'Component' AND pr.EffectivityId = '11'
GO

This will give me 5 rows, I only want the first two. I want rows with unique ComponentId's. In this case row 1 and row 2 would be desired.
where there are multiple rows with the same ComponentId, I only want one, any one would be fine but Ideally the one with the smallest partRefId would be best.
I'll try to post the image and maybe this thing wont choke up again
Thanks

perry.59 SSC Enthusiast Points: 102 More actions · Answer 7

perry.59

SSC Enthusiast

Points: 102

May 22, 2017 at 2:08 pm

#1943651

caffeinated Ten Centuries Points: 1271 More actions · Answer 8

Perhaps this?

USE [test] go

select ComponentId, partRefId = MIN(partRefId), Name, DocumentId, SheetRef = MIN(SheetRef), LocationId, Effectivity, EffectivityId, DesignatorPrefix, Designator, DesignatorSuffix FROM PartRef pr JOIN PartDef pd ON pd.PartDefId = pr.PartDefId Where pd.PartClass = 'Component' AND pr.EffectivityId = '11' group by ComponentId, Name, DocumentId, LocationId, Effectivity, EffectivityId, DesignatorPrefix, Designator, DesignatorSuffix

perry.59 SSC Enthusiast Points: 102 More actions · Answer 9

caffeinated - Monday, May 22, 2017 5:59 PM
Perhaps this?
USE [test] go
select ComponentId, partRefId = MIN(partRefId), Name, DocumentId, SheetRef = MIN(SheetRef), LocationId, Effectivity, EffectivityId, DesignatorPrefix, Designator, DesignatorSuffix FROM PartRef pr JOIN PartDef pd ON pd.PartDefId = pr.PartDefId Where pd.PartClass = 'Component' AND pr.EffectivityId = '11' group by ComponentId, Name, DocumentId, LocationId, Effectivity, EffectivityId, DesignatorPrefix, Designator, DesignatorSuffix

Very cool! it does precisely what I want. It is also clear and concise, I was afraid I would have to use some difficult convoluted script with multiple joins, temp tables, sub queries and a trip to pluto. but this is elegant. I had not yet seen a select statement written in such a way.
Thank You!