Select group by

Question

Post reply

Select group by

Corobori

Ten Centuries

Points: 1316
More actions
April 21, 2016 at 7:54 pm

#400056

I need to list products by their provider's id but only one product per provider. See the print screen below. I have tried several ways but couldn't find the right way. Any clue ?
/****** Object: Table [dbo].[tblTemp] Script Date: 4/21/2016 12:51:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTemp](
[Pr_Id] [int] NOT NULL,
[Pr_Em_Id] [int] NOT NULL,
[Pr_Em_Name] [nvarchar](50) NULL,
[Pr_Product] [nvarchar](50) NULL,
CONSTRAINT [PK_tblTemp] PRIMARY KEY CLUSTERED
(
[Pr_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (1, 5, N'John Doe
', N'Apple Type 1')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (2, 5, N'John Doe
', N'Apple Type 2')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (3, 5, N'John Doe
', N'Apple Type 3')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (4, 5, N'John Doe
', N'Pineapple ')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (5, 1, N'Karl Wendt
', N'Apple Red')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (6, 1, N'Karl Wendt
', N'Pineapple ')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (7, 4, N'Pablo Perez
', N'Apple Green 1')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (8, 4, N'Pablo Perez
', N'Apple Green 2')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (9, 4, N'Pablo Perez
', N'Apple Red')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (10, 4, N'Pablo Perez
', N'Pineapple Type 5')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (11, 3, N'Paolo Rossi
', N'Pineapple ')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (12, 3, N'Paolo Rossi
', N'Pineapple Type 2')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (13, 3, N'Paolo Rossi
', N'Pineapple Type 3')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (14, 3, N'Paolo Rossi
', N'Pineapple Type 4')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (15, 2, N'Paul Ué
', N'Peach Type 1')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (16, 2, N'Paul Ué
', N'Peach Type 2')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (17, 2, N'Paul Ué
', N'Pineapple ')
GO

Jean-Luc
www.corobori.com

Viewing 7 posts - 1 through 6 (of 6 total)

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

Tava SSCarpal Tunnel Points: 4122 More actions · Answer 1

sorry, little confused as to what you are showing with the screenshots.... list1, list2?

Can you do a quick screenshot or just type what you expect the outcome to be?

Just to clear up the confusion... provider id is "Pr_Id" right? or are you saying it is "Pr_Em_Id"?

Secondly, post the code with your grouping problem... it will be easier to understand and explain why it isn't working as expected. I'm assuming it is because your are grouping by "Pr_Id" which will never group as these are unique in your query

Corobori Ten Centuries Points: 1316 More actions · Answer 2

1) What I am showing in list1 and list2 is what I am expecting to get

2) Pr_Id is the product id, Pr_Em_Id is the provider id

Jean-Luc
www.corobori.com

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Corobori (4/21/2016)
1) What I am showing in list1 and list2 is what I am expecting to get
2) Pr_Id is the product id, Pr_Em_Id is the provider id

Quick question, can you provide the desired output? BTW, good job with the DDL and sample data.

😎

Here is one possible solution using simple group by aggregation

USE TEEST;

GO

/****** Check ic Table [dbo].[tblTemp] exists ******/

IF OBJECT_ID(N'dbo.tblTemp') IS NOT NULL DROP TABLE dbo.tblTemp;

CREATE TABLE [dbo].[tblTemp](

[Pr_Id] [int] NOT NULL,

[Pr_Em_Id] [int] NOT NULL,

[Pr_Em_Name] [nvarchar](50) NULL,

[Pr_Product] [nvarchar](50) NULL,

CONSTRAINT [PK_tblTemp] PRIMARY KEY CLUSTERED

(

[Pr_Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ;

GO

INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product])

VALUES (1, 5, N'John Doe', N'Apple Type 1')

,(2, 5, N'John Doe', N'Apple Type 2')

,(3, 5, N'John Doe', N'Apple Type 3')

,(4, 5, N'John Doe', N'Pineapple ')

,(5, 1, N'Karl Wendt', N'Apple Red')

,(6, 1, N'Karl Wendt', N'Pineapple ')

,(7, 4, N'Pablo Perez', N'Apple Green 1')

,(8, 4, N'Pablo Perez', N'Apple Green 2')

,(9, 4, N'Pablo Perez', N'Apple Red')

,(10, 4, N'Pablo Perez', N'Pineapple Type 5')

,(11, 3, N'Paolo Rossi', N'Pineapple ')

,(12, 3, N'Paolo Rossi', N'Pineapple Type 2')

,(13, 3, N'Paolo Rossi', N'Pineapple Type 3')

,(14, 3, N'Paolo Rossi', N'Pineapple Type 4')

,(15, 2, N'Paul Ué', N'Peach Type 1')

,(16, 2, N'Paul Ué', N'Peach Type 2')

,(17, 2, N'Paul Ué', N'Pineapple ')

;

SELECT

TT.Pr_Em_Id

,TT.Pr_Em_Name

,MIN(TT.Pr_Product) AS PRODUCT_NAME

FROM dbo.tblTemp TT

GROUP BY TT.Pr_Em_Id

,TT.Pr_Em_Name;

Output

Pr_Em_Id Pr_Em_Name PRODUCT_NAME

--------- ------------- ---------------

5 John Doe Apple Type 1

1 Karl Wendt Apple Red

4 Pablo Perez Apple Green 1

3 Paolo Rossi Pineapple

2 Paul Ué Peach Type 1

Corobori Ten Centuries Points: 1316 More actions · Answer 4

Corobori

Ten Centuries

Points: 1316

April 22, 2016 at 5:13 am

#1873862

The desire output is this:

Jean-Luc
www.corobori.com

david.smith0143 Newbie Points: 2 More actions · Answer 5

Use distinct so that you will get proper output

Mobile Application Development | iPhone App Development

Corobori Ten Centuries Points: 1316 More actions · Answer 6

Based upon an old SP I have I came up with this:

ALTER PROCEDURE [dbo].[sp_Read]

AS

BEGIN

declare @sSQL varchar(5000)

set @sSQL = COALESCE(@sSQL + ' ','')

create table #MyHead (

Pr_Id int,

Pr_Em_Id int,

Pr_Em_Name nvarchar(50),

SortBy int,

Random int IDENTITY(1,1) )

set @sSQL = 'SELECT Pr_Id, Pr_Em_Id,Pr_Em_Name, '

set @sSQL = @sSQL + ' CAST(NULL AS INT) AS SortBy'

set @sSQL = @sSQL + ' FROM tblTemp'

insert into #MyHead

exec(@sSQL)

--===== Add the necessary clustered index for the "quirky" update

CREATE CLUSTERED INDEX IXU_#MyHead ON #MyHead (Pr_Em_Id,Random)

--===== Create a couple of variables to use in the "quirky" update and preset them

DECLARE @PrevSortBy INT,

@PrevPr_Em_Id INT

SELECT @PrevSortBy = 0,

@PrevPr_Em_Id= 0

--===== Do the "quirky" update. Think of it as "ROW_NUMBER() OVER" for SQL Server 2000

UPDATE #MyHead

SET @PrevSortBy = SortBy = CASE WHEN @PrevPr_Em_Id = Pr_Em_Id

THEN @PrevSortBy + 1

ELSE 1

END,

@PrevPr_Em_Id = Pr_Em_Id

FROM #MyHead WITH(INDEX(0))

--===== Produce the "semi random" output.

SELECT tblTemp.Pr_Id, tblTemp.Pr_Em_Id, tblTemp.Pr_Em_Name,Pr_Product

FROM tblTemp INNER JOIN #MyHead ON tblTemp.Pr_Id = #MyHead.Pr_Id

ORDER BY SortBy,case when left(tblTemp.Pr_Em_Name, 1) >= CHAR(65 + 26 * rand()) then 0 else 1 end

--ORDER BY SortBy,Pr_Em_Id --Semi random as requested

drop table #MyHead

END

Jean-Luc
www.corobori.com