Quick T-SQL Help Please

envo said:
You guys should crack a T-SQL book once in a while ;)


Code: 
select Name, Type from [YourTable] 
group by Name,Type
Having count(case when Type = 'Positive' then 1 end) > count(case when Type='Negative' then 1 end)
Click to expand...
Without having to do a subquery. The clean code award.

Only issue is the hardcoding in case Type is variable
 
envo said:
You guys should crack a T-SQL book once in a while ;)


Code: 
select Name, Type from [YourTable] 
group by Name,Type
Having count(case when Type = 'Positive' then 1 end) > count(case when Type='Negative' then 1 end)
Click to expand...
Won't this remove the rows that are negative? I need to just change the type of the last one to positive.
 
Pho3nix said:
Won't this remove the rows that are negative? I need to just change the type of the last one to positive.
Click to expand...

You need to change all the type fields of the name to the one that occurs the most?
 
Code: 
DECLARE  @MyTable TABLE(
   Id int,
   Name nvarchar(max),
   Type nvarchar(max)
);


INSERT INTO @MyTable (id, Name,Type) VALUES (1, 'Bob','Postive'), (2, 'Bob','Postive'), (3, 'Bob','Negative'),(4, 'Sally','Negative')

 
SELECT Name,Type
FROM
(
	SELECT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY OccursCount DESC)  as RankNo,* FROM
	(
		select Name,Type,Count(Type) as OccursCount from @MyTable GROUP BY Name,Type
	)OccursTable 
)RankTable WHERE RankNo=1
 
skimread said:
Code: 
DECLARE  @MyTable TABLE(
   Id int,
   Name nvarchar(max),
   Type nvarchar(max)
);


INSERT INTO @MyTable (id, Name,Type) VALUES (1, 'Bob','Postive'), (2, 'Bob','Postive'), (3, 'Bob','Negative'),(4, 'Sally','Negative')

 
SELECT Name,Type
FROM
(
	SELECT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY OccursCount DESC)  as RankNo,* FROM
	(
		select Name,Type,Count(Type) as OccursCount from @MyTable GROUP BY Name,Type
	)OccursTable 
)RankTable WHERE RankNo=1
Click to expand...

Holy *****, I had forgotten about ROW_NUMBER() OVER(PARTITION BY ).

Lemme try this
 
Pho3nix said:
Yeah :wtf: thought it was clear in the OP LOL.

Sorry guys if I'm confusing you :o
Click to expand...

Code: 
DECLARE  @SourceTable TABLE(
   Id int,
   Name nvarchar(max),
   Type nvarchar(max)
);


INSERT INTO @SourceTable (id, Name,Type) VALUES (1, 'Bob','Postive'), (2, 'Bob','Postive'), (3, 'Bob','Negative'),(4, 'Sally','Negative')


DECLARE  @MostTable TABLE(
   Name nvarchar(max),
   Type nvarchar(max)
);

INSERT INTO @MostTable
SELECT Name,Type
FROM
(
	SELECT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY OccursCount DESC)  as RankNo,* FROM
	(
		select Name,Type,Count(Type) as OccursCount from @SourceTable GROUP BY Name,Type
	)OccursTable 
)RankTable WHERE RankNo=1

UPDATE @SourceTable SET Type=MT.Type  
FROM @SourceTable ST
INNER JOIN @MostTable MT ON ST.Name=MT.Name

SELECT * FROM @SourceTable
 
I like skimread`s, but instead of a temporary table, why not a cte?
Code: 
with r as
(
select id,r.type as old, x.type as new from results r
join
(
SELECT name,Type
FROM
(
	SELECT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY OccursCount DESC)  as RankNo,* FROM
	(
		select Name,Type,Count(Type) as OccursCount from results GROUP BY Name,Type
	)results 
)RankTable WHERE RankNo=1) x on x.name = r.name
)
update r set old = new
 
Urist said:
I like skimread`s, but instead of a temporary table, why not a cte?
Code: 
with r as
(
select id,r.type as old, x.type as new from results r
join
(
SELECT name,Type
FROM
(
	SELECT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY OccursCount DESC)  as RankNo,* FROM
	(
		select Name,Type,Count(Type) as OccursCount from results GROUP BY Name,Type
	)results 
)RankTable WHERE RankNo=1) x on x.name = r.name
)
update r set old = new
Click to expand...

Did mine with a CTE and just plugged in the ROW_NUMBER within that. Thanks to @skimread
 
excuse my query. Can not run to test it so it might not even work

Code: 
select top 1 Name, Type, count(*) as counter from [table]
group by Name, Type
order by counter desc
 
Pho3nix said:
Did mine with a CTE and just plugged in the ROW_NUMBER within that. Thanks to @skimread
Click to expand...

you might also just want to make it a little more deterministic by refing the order in the frame. as it is, you'll get a 'random' result if they are equal.
 
Pho3nix said:
Won't this remove the rows that are negative? I need to just change the type of the last one to positive.
Click to expand...

This is your requirement:
and only return the one that comes up the most.
Click to expand...

So I only return the one that comes up the most, for positive. It's supposed to help you, not give you the answer. If you need me to lok at this for you, my hourly rate is R850
 
I was bored so for schits and gigs (C#) :

DataTable d = new DataTable();
d.Columns.Add("ID");
d.Columns.Add("NAME");
d.Columns.Add("TYPE");

d.Rows.Add("1","Bob","Positive");
d.Rows.Add("2", "Bob", "Positive");
d.Rows.Add("3", "Bob", "Negative");

d.Rows.Add("4", "Betty", "Positive");
d.Rows.Add("5", "Betty", "Positive");
d.Rows.Add("6", "Betty", "Negative");

var results = d.AsEnumerable()
.GroupBy(row => row.Field<string>("NAME"))
.Select(grp => new
{
Name = grp.Key,
TypeCount = grp.Select(row => row.Field<string>("TYPE")).Distinct().Count()
});

foreach (var r in results)
Console.WriteLine(r.Name + " has " + r.TypeCount + "Positives.");
 
envo said:
This is your requirement:


So I only return the one that comes up the most, for positive. It's supposed to help you, not give you the answer. If you need me to lok at this for you, my hourly rate is R850
Click to expand...

Not sure what climbed up your ass this morning. I was looking for some assistance, which some kind people provided.

Didn’t know when we ask questions on the forum we now need to provide a budget.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X