|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Quick DISTINCT questionHello all!
I know the following will work, "SELECT DISTINCT Name, MIN(Sign) AS Sign FROM Profile GROUP BY Name" Will return 2 columns, Name and Sign. But what if I want more than just the two columns, and I need four to be listed, but using the same code above. Just not sure how to add additional columns without getting errors. Is this even possible? TIA!!! Rudy If you can show us some sample data and the required output we can come up
with some queries. Without that, you either add those additional columns to the GROUP BY clause, or have then in the SELECT, within an aggregate function. "Rudy" <R***@discussions.microsoft.com> wrote in message I know the following will work,news:5EC4A04B-AD95-40C6-BBEE-D8A1E9B3BF52@microsoft.com... Hello all! "SELECT DISTINCT Name, MIN(Sign) AS Sign FROM Profile GROUP BY Name" Will return 2 columns, Name and Sign. But what if I want more than just the two columns, and I need four to be listed, but using the same code above. Just not sure how to add additional columns without getting errors. Is this even possible? TIA!!! Rudy Yes, you need to decide, for each of those other columns, which of the
possible multiple values that exists should be output by the query... Since you are Grouping By Name, that means you will get one row in your output per disntinct value of Name. There may be many rows in the original Table for each value of Name, each with different values for these other columns... So for each, you must tell query whether to output the Min(), the Max(), the Sum(), AVG(), or whatever... Select Name, MIN(Sign) AS Sign, Min(Col1), Max(Col2), etc... From Profile Group By Name If you want ALL the values of these other columns listed, as: Name Col1 Col2 John 1 AA John 2 AB John 3 AC etc. then you can't group just by name, you need to add the other columns to the group By clause Show quote "Rudy" wrote: > Hello all! > > I know the following will work, > "SELECT DISTINCT Name, MIN(Sign) AS Sign > FROM Profile > GROUP BY Name" > Will return 2 columns, Name and Sign. > But what if I want more than just the two columns, and I need four to be > listed, but using the same code above. Just not sure how to add additional > columns without getting errors. Is this even possible? > > TIA!!! > > Rudy also, look at the with rollup and with group options for group, then you can
do stuff like: Select Name, col1, min(Sign) From Profile Group By Name, col1 with rollup and you will get all sorts of different levels... -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "CBretana" <cbretana@areteIndNOSPAM.com> wrote in message news:406DA4BA-6217-431D-B838-CA56B96C3453@microsoft.com... > Yes, you need to decide, for each of those other columns, which of the > possible multiple values that exists should be output by the query... > Since you are Grouping By Name, that means you will get one row in your > output per disntinct value of Name. There may be many rows in the original > Table for each value of Name, each with different values for these other > columns... So for each, you must tell query whether to output the Min(), > the > Max(), the Sum(), AVG(), or whatever... > Select Name, MIN(Sign) AS Sign, > Min(Col1), Max(Col2), etc... > From Profile > Group By Name > > If you want ALL the values of these other columns listed, as: > Name Col1 Col2 > John 1 AA > John 2 AB > John 3 AC > etc. > then you can't group just by name, you need to add the other columns to > the > group By clause > > > > "Rudy" wrote: > >> Hello all! >> >> I know the following will work, >> "SELECT DISTINCT Name, MIN(Sign) AS Sign >> FROM Profile >> GROUP BY Name" >> Will return 2 columns, Name and Sign. >> But what if I want more than just the two columns, and I need four to be >> listed, but using the same code above. Just not sure how to add >> additional >> columns without getting errors. Is this even possible? >> >> TIA!!! >> >> Rudy Thanks you everyone for your suggestions! CBretana, your answer did the
trick. Thank!!! Rudy Show quote "CBretana" wrote: > Yes, you need to decide, for each of those other columns, which of the > possible multiple values that exists should be output by the query... > Since you are Grouping By Name, that means you will get one row in your > output per disntinct value of Name. There may be many rows in the original > Table for each value of Name, each with different values for these other > columns... So for each, you must tell query whether to output the Min(), the > Max(), the Sum(), AVG(), or whatever... > Select Name, MIN(Sign) AS Sign, > Min(Col1), Max(Col2), etc... > From Profile > Group By Name > > If you want ALL the values of these other columns listed, as: > Name Col1 Col2 > John 1 AA > John 2 AB > John 3 AC > etc. > then you can't group just by name, you need to add the other columns to the > group By clause > > > > "Rudy" wrote: > > > Hello all! > > > > I know the following will work, > > "SELECT DISTINCT Name, MIN(Sign) AS Sign > > FROM Profile > > GROUP BY Name" > > Will return 2 columns, Name and Sign. > > But what if I want more than just the two columns, and I need four to be > > listed, but using the same code above. Just not sure how to add additional > > columns without getting errors. Is this even possible? > > > > TIA!!! > > > > Rudy  |
|||||||||||||||||||||||
Other interesting topics