How to achieve a non grouped column in an aggregate function

Question

Post reply

How to achieve a non grouped column in an aggregate function

glyn.walters

SSC Rookie

Points: 40
More actions
November 12, 2010 at 5:37 am

#143541

Hi
I wondered if I could get some advice on the best way to achieve this:
price,vendor,item
----------------
56,Bob,Shirt
15,John,Tie
16,Bob,Tie
12,Peter,Tie
68,John,Shirt
I want to get the min(price) grouped by item, but I also want to get the vendor it belongs to.
This clearly is invalid as vendor isn't in the group by clause, but neither do I want it grouped by vendor.
select
min(price)
, vendor
, item
from
items
group by
item
I can think of a few convoluted way I could do it with the group statement joined as a subselect back to the table. But I wondered if there were any simpler solutions.
The result set I'd be after would be
56,Bob,Shirt
12,Peter,Tie

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

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

tommyh SSCertifiable Points: 6302 More actions · Answer 1

Maybe something like this could work.

/*Testcode*/

declare @Items table (Price integer, Vendor varchar(32), Item varchar(32))

insert into @Items(Price, Vendor, Item)

select 56,'Peter','Shirt' union all

select 56,'Bob','Shirt' union all

select 15,'John','Tie' union all

select 16,'Bob','Tie' union all

select 12,'Peter','Tie' union all

select 68,'John','Shirt'

/*end testcode*/

select x.Price, i.Vendor, x.Item

from (select Min(Price) Price, Item from @Items group by Item) x

join @Items i on i.Price = x.Price and i.Item = x.Item

/T

glyn.walters SSC Rookie Points: 40 More actions · Answer 2

Thanks. So it does seem to be something like that with a grouped statement joining back to the original table that's needed. I wondered if there was some simple alternative syntax for these cases that might be available.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Glyn, what version of SQL Server are you using?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

glyn.walters SSC Rookie Points: 40 More actions · Answer 4

It's 2005 Express on my dev environment and 2008 in production

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

glyn.walters (11/12/2010)
It's 2005 Express on my dev environment and 2008 in production

Use ROW_NUMBER() like this:

SELECT

Price,

Vendor,

Item,

rn = ROW_NUMBER() OVER(PARTITION BY Item ORDER BY Price, Vendor)

FROM ( -- replace this with your table name

select Price = 56, Vendor = 'Peter', Item = 'Shirt' union all