Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


GROUP BY question


GROUP BY question

Author
Message
Abu Dina
Abu Dina
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 3323
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

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
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 2763
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
Abu Dina
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 3323
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

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
Steven Willis
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 1721


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
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5846 Visits: 11406
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?
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 1721
Sergiy (5/7/2013)
[quote]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
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5846 Visits: 11406
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.
brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1522 Visits: 2010
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
Abu Dina
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 3323
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

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search