Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

GROUP BY question Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 2:55 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 708, Visits: 3,288
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
Post #1450033
Posted Tuesday, May 7, 2013 3:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
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/
Post #1450038
Posted Tuesday, May 7, 2013 3:35 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 708, Visits: 3,288
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
Post #1450042
Posted Tuesday, May 7, 2013 2:21 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721

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



 
Post #1450328
Posted Tuesday, May 7, 2013 3:48 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 4,576, Visits: 8,351
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?
Post #1450350
Posted Tuesday, May 7, 2013 5:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 
Post #1450367
Posted Tuesday, May 7, 2013 6:26 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 4,576, Visits: 8,351
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.
Post #1450379
Posted Tuesday, May 7, 2013 7:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 1,009, Visits: 1,067
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)
Post #1450384
Posted Wednesday, May 8, 2013 2:14 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 708, Visits: 3,288
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
Post #1450444
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse