Adding a field to an aggregate query

Question

Adding a field to an aggregate query

Miguel Ramirez-454354

Valued Member

Points: 68
More actions
March 17, 2010 at 11:04 am

#138877

Hi all I am having a hard time figuring out how to write query:
Data is like this:
1stname2ndName SEQsize
Nameaname113232
Nameaname121212
Nameaname212133
Nameaname35313
Namebname1112213
Namebname12121212
Namebname312111
Namebname322323
I want to group by 1stname and 2ndName and the highest SEQ number along with the associated size.
The results from my sample data would be:
Nameaname121212
Nameaname212133
Nameaname35313
Namebname12121212
Namebname322323
I have created the following query with all that I want but the size column.
SELECT 1stName, 2ndName, MAX(SEQ) AS SeqID
FROM tablename
GROUP BY 1stName, 2ndName
Order by 1stName, 2ndName
Is there a way to add the size column. I could probably do it by making a temp table but it seam to me that there should be a simpler way.
Thank you in advance for your help.
Miguel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

It depends.

Do you want to (1) get the min, max or average size within each name, along with your max SeqID , (2) group by size within name, or (3) get the size found on the same row as the max SeqID? Draw us a picture of your expected output, please.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Miguel Ramirez-454354 Valued Member Points: 68 More actions · Answer 2

Sorry, I look very readable on word doc but when I tried to clean it up it posted it. I am looking for option: (3) get the size found on the same row as the max SeqID

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 3

Hi

This is typical request to select top n in grioup by. If you use group by clause then you need to use cursor in while loop. But there is a get-around way to select top n in group by as below.

IF OBJECT_ID('tempdb..#namelist') IS NOT NULL

drop table #namelist

create table #namelist

(

[1stName] varchar(30) not null,

[2ndName] varchar(30) not null,

seq int not null,

int not null

)

insert into #namelist values('Namea', 'name1', 1, 3232)

insert into #namelist values('Namea', 'name1', 1, 232)

insert into #namelist values('Namea', 'name1', 2, 1212)

insert into #namelist values('Namea', 'name2', 1, 2133)

insert into #namelist values('Namea', 'name3', 5, 313)

insert into #namelist values('Namea', 'name3', 5, 1313)

insert into #namelist values('Nameb', 'name1', 1, 12213)

insert into #namelist values('Nameb', 'name1', 2, 121212)

insert into #namelist values('Nameb', 'name1', 2, 221212)

insert into #namelist values('Nameb', 'name3', 1, 2111)

insert into #namelist values('Nameb', 'name3', 1, 1111)

insert into #namelist values('Nameb', 'name3', 2, 2323)

insert into #namelist values('Nameb', 'name3', 2, 323)

SELECT distinct *

FROM #namelist a

WHERE (

SELECT Count(*)

FROM #namelist

WHERE a.[1stName]=[1stName] and a.[2ndName]=[2ndName] and a.seq=seq

and a.<

)<1

tip: You need to select distinct rows. Otherwise Namea,name1,1 appears twice.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

There are several approaches to solving the "get all columns of only one row from a group" problem.

Three different approaches are described here[/url].

But for a small number of rows in the source table, this one will work fine in 2008.

declare @sample table (firstName varchar(30), SecondName varchar(30), SEQ int, size int)

insert into @sample

select 'NameA', 'Name1', 1, 3232 union all

select 'NameA', 'Name1', 2, 1212 union all

select 'NameA', 'Name2', 1, 2133 union all

select 'NameA', 'Name3', 5, 313 union all

select 'NameB', 'Name1', 1, 12213 union all

select 'NameB', 'Name1', 2, 121212 union all

select 'NameB', 'Name3', 1, 2111 union all

select 'NameB', 'Name3', 2, 2323

select * from @sample

;with cte as ( select firstname,secondName, seq, size,

row_number() over (partition by firstname, secondname order by SEQ desc) as testVal

from @sample

)

select firstname,secondName,SEQ,size

from cte

where testVal = 1

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

Miguel Ramirez-454354 (3/17/2010)
Sorry, I look very readable on word doc but when I tried to clean it up it posted it. I am looking for option: (3) get the size found on the same row as the max SeqID

Which size?

create table #namelist

(

[1stName] varchar(30) not null,

[2ndName] varchar(30) not null,

seq int not null,

int not null

)

insert into #namelist values('Namea', 'name1', 1, 3232)

insert into #namelist values('Namea', 'name1', 1, 232)

insert into #namelist values('Namea', 'name1', 2, 1212)

insert into #namelist values('Namea', 'name2', 1, 2133)

insert into #namelist values('Namea', 'name3', 5, 313)

insert into #namelist values('Namea', 'name3', 5, 1313)

insert into #namelist values('Nameb', 'name1', 1, 12213)

insert into #namelist values('Nameb', 'name1', 2, 121212)

insert into #namelist values('Nameb', 'name1', 2, 221212)

insert into #namelist values('Nameb', 'name3', 1, 2111)

insert into #namelist values('Nameb', 'name3', 1, 1111)

insert into #namelist values('Nameb', 'name3', 2, 2323)

insert into #namelist values('Nameb', 'name3', 2, 323)

SELECT a.*

FROM #namelist a

INNER JOIN

(SELECT [1stName], [2ndName], MAX(SEQ) AS SeqID

FROM #namelist

GROUP BY [1stName], [2ndName]) g ON g.[1stName] = a.[1stName] AND g.[2ndName] = a.[2ndName] AND a.seq = g.SeqID

Results:

1stName 2ndName seq size

---------- ---------- ----------- -----------

Nameb name3 2 2323

Nameb name3 2 323

Nameb name1 2 121212

Nameb name1 2 221212

Namea name3 5 313

Namea name3 5 1313

Namea name2 1 2133

Namea name1 2 1212

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Miguel Ramirez-454354 Valued Member Points: 68 More actions · Answer 6

:-)Thank you all for your replies. I was driving myself nuts trying to figure it out. Next time I will do a better job in posting my questions.

Thanks again,

Miguel

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

There is nothing in the original data provided to guarantee the order of the rows... until there is, all of the methods offered so far (as well written as they may be) have the potential for failure and may not produce the posted desired result set.

There has to be another column somewhere in the original data that will maintain the desired order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)