How to return a LOT of other columns with group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to return a LOT of other columns with group by

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-19 : 13:18:16

I have a MyNames table which contains over 100 columns. The Name column may have the same name in many rows. The data looks like this.

Name..........Factor1..........Factor2...
John..........X................Y
John..........X................X
Mary..........T................Y
Mary..........V................Y

I need a result table containing all 100 columns from MyNames but only the unique names in the name column. I don’t care which name row is used.

Name..........Factor1..........Factor2...
John..........X................Y
Mary..........V................Y

I have tried DISTINCT and GROUP BY.
DISTINCT will unique on every column listed so I will end up with the name repetred in the Name column.

SELECT DISIINCT Name, Factor1, Factor2 => I do not want the Factor columns involved in the distinct. This will cause the same name to appear in more than one row.

Group by will requires that I place the remaininf 99 columns in a function in order to have the columns listed.

SELECT Name, MAX(Address)... => adding 99 fields.

Is there a way get only the unique Names with the 99 other columns with out having to manually put the other 99 columns in functions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-19 : 13:32:18

Try:-

SELECT tmp.Name,tmp.Factor1,tmp.Factor2,...
FROM
(SELECT t.Name,t.Factor1,t.Factor2,...,
(SELECT COUNT(*) +1 FROM MyNames WHERE Name=t.Name AND field < t.field) AS SNo,
FROM MyNames t
)tmp
WHERE tmp.SNo=1

*field is any field in MyNames other than Name with unique values

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-19 : 14:14:56

I am really lost. Could you explain how this works? It is not working when i try to run it

subrata4allfriends
Starting Member

24 Posts

Posted - 2008-01-19 : 15:22:45

Please try this..........putting ur appropiate table and column name.

SELECT DISTINCT
[Name],
(SELECT DISTINCT TOP 1 [Factor1] FROM [MyName]
WHERE [Name] = [Name] ),
(SELECT DISTINCT TOP 1 [Factor2] FROM [MyName]
WHERE [Name] = [Name] )
FROM [MyName]

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

subrata4allfriends
Starting Member

24 Posts

Posted - 2008-01-19 : 15:24:26

Please revert me back in case of any issue...........I will try..at my extent.

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-19 : 22:48:47

I gave your sample a try. It did not work and I don'tunderstand the technique. Is the objective to distinct on the name then bring in the other fields using the select in the select fieldlist?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 23:05:14

Kirk, the CTE solution posted in the other forum u asked the same question will work. why do u say u need to research it, instead of just try it out? give it a shot, post back with further questions.

http://www.sqlservercentral.com/Forums/Topic445236-338-1.aspx

(sorry all...thats two times i posted links to other forums, but in both cases question was asked and answered...not trying to drive traffic away...)

subrata4allfriends
Starting Member

24 Posts

Posted - 2008-01-20 : 04:50:06

quote:
Originally posted by kirknew2SQL

I gave your sample a try. It did not work and I don'tunderstand the technique. Is the objective to distinct on the name then bring in the other fields using the select in the select fieldlist?

Yes..... I think that is ur requirement.
But before posting I had tested the same...as per the requirement, and its working properly.

I think ur requirement is something different!!!

U told that the Name column will be unique and any column value for other columns which contains multiple data....isnt it.
Is this ur requirement......or else

Please inform me....

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-20 : 07:49:04

quote:
Originally posted by kirknew2SQL

I am really lost. Could you explain how this works? It is not working when i try to run it

what is error you got? DO you have a column in your table having unique values?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-21 : 03:14:01

If you are using 2005 build it up with CTE , it is possible to have one CTE referecing another , therefore you can break the problem down into smaller sets

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 03:45:00

[code]SELECT Name,
Factor1,
Factor2,
FactorX
FROM (
SELECT Name,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS RecID,
Factor1,
Factor2,
FactorX
FROM MyNames
) AS d
WHERE RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-21 : 09:23:51

Am I missing anything?

Select name,max(col1),max(col2),....max(col99) from table
group by name

Madhivanan

Failing to plan is Planning to fail

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-21 : 09:35:58

quote:
Originally posted by madhivanan

Am I missing anything?

Select name,max(col1),max(col2),....max(col99) from table
group by name

Madhivanan

Failing to plan is Planning to fail

only OP asked for other way besides "max(col1),max(col2),....max(col99)"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-21 : 10:21:24

I have been working with the solutions provided and have learned how and why they work. Thank you very much for all of the input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources