GROUP BY except one column - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

GROUP BY except one column

Author

Topic

martind1
Starting Member

28 Posts

Posted - 2012-03-13 : 10:44:44

Hi,

I have the following query:


SELECT MIN(Price), TourDate
FROM myTable
GROUP BY TourDate

Now, I want to bring out a column called location, but I don't want to group it on this.
Main reason being I want the cheapest price for that date regardless of location.

How can I do this, or is it possible?
Thanks.

--
http://www.tutorial-resource.com - Free Web Development Resources

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-13 : 11:01:44

So if there are many different locations in that group - which one you want to be returned?

No, you're never too old to Yak'n'Roll if you're too young to die.

martind1
Starting Member

28 Posts

Posted - 2012-03-13 : 11:11:45

Whichever is the cheapest.

--
http://www.tutorial-resource.com - Free Web Development Resources

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-13 : 11:34:24

is there a unique key in the table?

No, you're never too old to Yak'n'Roll if you're too young to die.

martind1
Starting Member

28 Posts

Posted - 2012-03-13 : 11:39:27

The table has a primary key which includes 6 columns.

--
http://www.tutorial-resource.com - Free Web Development Resources

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-13 : 12:07:02

hm...
will this really make sense without a BAND or whatever will be "on tour"?

No, you're never too old to Yak'n'Roll if you're too young to die.

martind1
Starting Member

28 Posts

Posted - 2012-03-13 : 12:37:46

Well a tour is a trip to a destination. So I just want the cheapest price for any location.

--
http://www.tutorial-resource.com - Free Web Development Resources

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-13 : 13:27:28

This?

declare @sample table (id int identity(1,1),price decimal(12,2),TourDate date, location varchar(255))
insert @sample(price, TourDate,location)
select 10,'20120301','A' union all
select 10,'20120302','B' union all
select 9, '20120301','C' union all
select 10,'20120304','D'

select * from @sample

select TourDate, location, price from
(
select 
TourDate, 
price, 
location,
row_number() over (partition by TourDate order by price asc) rnum
from @sample)dt
where rnum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

martind1
Starting Member

28 Posts

Posted - 2012-03-14 : 08:38:37

Nice Idea.
I eventually solved it using RANK and PARTITION :)

--
http://www.tutorial-resource.com - Free Web Development Resources

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources