GROUP BY question

Question

GROUP BY question

Abu Dina

SSChampion

Points: 14155
More actions
May 7, 2013 at 2:55 am

#293600

Morning all,
Could someone help me generate the following grouping please? Sample data plus expected output below:
Input Table: SiteMatches
Expected Output: SiteMatches_Output
CREATE TABLE dbo.SiteMatches (RetainedRID BIGINT, DroppedRidd BIGINT, Country VARCHAR(100))
INSERT INTO dbo.SiteMatches
SELECT 137478, 87728738, 'UK' UNION ALL
SELECT 137478, 95615, 'UK' UNION ALL
SELECT 851566, 447422, 'SCOTLAND' UNION ALL
SELECT 851566, 188827, 'SCOTLAND' UNION ALL
SELECT 851566, 326887, 'SCOTLAND'
CREATE TABLE dbo.SiteMatches_Output (ID BIGINT, SiteID BIGINT, Country VARCHAR(30))
INSERT INTO SiteMatches_Output
SELECT 137478, 1, 'UK' UNION ALL
SELECT 87728738, 1, 'UK' UNION ALL
SELECT 95615, 1, 'UK' UNION ALL
SELECT 851566, 2, 'SCOTLAND' UNION ALL
SELECT 447422, 2, 'SCOTLAND' UNION ALL
SELECT 188827, 2, 'SCOTLAND' UNION ALL
SELECT 326887, 2, 'SCOTLAND'
select * from SiteMatches
select * from SiteMatches_Output
drop table dbo.SiteMatches
drop table dbo.SiteMatches_Output
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

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

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

can you please provide some more details so that we can help you

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Abu Dina SSChampion Points: 14155 More actions · Answer 2

Not the most elegant solution but I've come up with this:

;with CTE_SiteMatchByCountry (Retained_RID, Dropped_RID, Country, theRank)

AS

(SELECT RetainedRID, DroppedRID, Country,

DENSE_RANK() OVER (ORDER BY RetainedRID)

FROM SiteMatches)

SELECT Retained_RID, Country, theRank

FROM CTE_SiteMatchByCountry

GROUP BY Retained_RID, Country, theRank

UNION ALL

SELECT Dropped_RID, Country, theRank

FROM CTE_SiteMatchByCountry

GROUP BY Dropped_RID, Country, theRank

ORDER By 3

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

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

IF OBJECT_ID('tempdb..#SiteMatches') IS NOT NULL

DROP TABLE #SiteMatches

CREATE TABLE #SiteMatches -- the SOURCE

(ID BIGINT IDENTITY(1,1) NOT NULL,

RetainedRID BIGINT NOT NULL,

SiteID BIGINT NOT NULL,

Country VARCHAR(30)

PRIMARY KEY(ID))

INSERT INTO #SiteMatches

SELECT 137478, 1, 'UK' UNION ALL

SELECT 87728738, 1, 'UK' UNION ALL

SELECT 95615, 1, 'UK' UNION ALL

SELECT 851566, 2, 'SCOTLAND' UNION ALL

SELECT 447422, 2, 'SCOTLAND' UNION ALL

SELECT 188827, 2, 'SCOTLAND' UNION ALL

SELECT 326887, 2, 'SCOTLAND'

IF OBJECT_ID('tempdb..#SiteMatches_Output') IS NOT NULL

DROP TABLE #SiteMatches_Output

CREATE TABLE #SiteMatches_Output -- the DESTINATION

(ID BIGINT IDENTITY(1,1) NOT NULL,

RetainedRID BIGINT NOT NULL,

SiteID BIGINT NOT NULL,

Country VARCHAR(100),

PRIMARY KEY(ID))

INSERT INTO #SiteMatches_Output

SELECT 137478, 1, 'UK' UNION ALL

SELECT 87728738, 1, 'UK' UNION ALL

SELECT 95615, 1, 'UK' UNION ALL

SELECT 851566, 2, 'SCOTLAND' UNION ALL

SELECT 447422, 2, 'SCOTLAND' UNION ALL

SELECT 188827, 2, 'SCOTLAND' UNION ALL

SELECT 326887, 2, 'SCOTLAND'

/* GROUPED BY*/

--variation 1

SELECT

ROW_NUMBER() OVER (PARTITION BY S1.ID ORDER BY S1.SiteID) AS RowNum

,S1.ID

,S1.RetainedRID

,S1.SiteID

,S1.CountryFROM

,#SiteMatches_Output S1

--variation 2

SELECT

ROW_NUMBER() OVER (PARTITION BY S2.RetainedRID ORDER BY S2.SiteID) AS RowNum

,S2.ID

,S2.RetainedRID

,S2.SiteID

,S2.Country

FROM

#SiteMatches_Output S2

--variation 3

SELECT

ROW_NUMBER() OVER (PARTITION BY S3.Country ORDER BY S3.SiteID DESC) AS RowNum

,S3.ID

,S3.RetainedRID

,S3.SiteID

,S3.Country

FROM

#SiteMatches_Output S3

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Abu Dina (5/7/2013)
Morning all,
Could someone help me generate the following grouping please? Sample data plus expected output below:
Input Table: SiteMatches
Expected Output: SiteMatches_Output
CREATE TABLE dbo.SiteMatches (RetainedRID BIGINT, DroppedRidd BIGINT, Country VARCHAR(100))
INSERT INTO dbo.SiteMatches
SELECT 137478, 87728738, 'UK' UNION ALL
SELECT 137478, 95615, 'UK' UNION ALL
SELECT 851566, 447422, 'SCOTLAND' UNION ALL
SELECT 851566, 188827, 'SCOTLAND' UNION ALL
SELECT 851566, 326887, 'SCOTLAND'
CREATE TABLE dbo.SiteMatches_Output (ID BIGINT, SiteID BIGINT, Country VARCHAR(30))
INSERT INTO SiteMatches_Output
SELECT 137478, 1, 'UK' UNION ALL
SELECT 87728738, 1, 'UK' UNION ALL
SELECT 95615, 1, 'UK' UNION ALL
SELECT 851566, 2, 'SCOTLAND' UNION ALL
SELECT 447422, 2, 'SCOTLAND' UNION ALL
SELECT 188827, 2, 'SCOTLAND' UNION ALL
SELECT 326887, 2, 'SCOTLAND'
select * from SiteMatches
select * from SiteMatches_Output
drop table dbo.SiteMatches
drop table dbo.SiteMatches_Output

Where SiteID = 1 for 'UK' and SiteID = 2 for 'SCOTLAND' come from?

_____________
Code for TallyGenerator

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 5

Sergiy (5/7/2013)

Abu Dina (5/7/2013)
Where SiteID = 1 for 'UK' and SiteID = 2 for 'SCOTLAND' come from?

It's right there in your screenshot. Whenever you ask for help unless you give us good examples most of us will have to take a few liberties to get our points across. We (at least I do) always assume that the sample data in a post is just that...sample data. Column names and even the data itself is usually irrelevant to solving a SQL problem.

So I wasn't trying to confuse, but you know what you were looking for and all I had was a dim candle to work by given the limited info you made available. The question was about grouping so it could have been carrots and onions just as easily.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Steven Willis (5/7/2013)
It's right there in your screenshot.

???

Did you actually read the question?

Right there at the top:

Abu Dina (5/7/2013)
Morning all,
Could someone help me generate the following grouping please? Sample data plus expected output below:

The second set is "expected output".

Therefore there is the question:

Where SiteID values come from?

They are nowhere to be seen in the source data.

_____________
Code for TallyGenerator

brad.mason5 Hall of Fame Points: 3299 More actions · Answer 7

^{SELECT DISTINCT}

ID =lot.value

, SiteID = DENSE_RANK()OVER(ORDER BY Country DESC)

,Country

FROM SiteMatches

CROSS APPLY (

VALUES('RetainedRID', RetainedRID)

, ('DroppedRID', DroppedRidd )) Lot(name,value)

Abu Dina SSChampion Points: 14155 More actions · Answer 8

Apologies if I wasn't clear enough with my original post. The site ID is just a unique number that gets assigned to each group so it could be 1, 2, 3 or anything else as long as it uniquely identified a group of Retained and Dropped IDs.

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

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn