Referencing a specific row while using DISTINCT

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 2005 Forums

Transact-SQL (2005)

Referencing a specific row while using DISTINCT

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-02 : 12:21:59

let's say I have a table that looks like this:

[font=Andale Mono]
Product | Time | Number
___________________________
Bikes | 9:00 | 3
Bikes | 9:30 | 4
Tires | 10:00 | 7
Bikes | 10:00 | 1
Tires | 10:30 | 2

If I used (SELECT DISTINCT Bikes, Min(Time), Sum(Number)), I'd have

Bikes | 9:00 | 8

But what If I wanted the Number for the first Time in a product in the forth colums, like the following?

Bikes | 9:00 | 8 | 3
Tires | 10:00 | 9 | 7

Is there any good way to do that? All that comes to mind is an ugly self-referential join.

Thanks,
Arithmomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 12:49:12

select t1.* from table t1 inner join
(
select product,min(time) as time from table group by product
) as t2
on t1.Product =t2.product and t1.Time=t2.time

Madhivanan

Failing to plan is Planning to fail

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-02 : 13:34:19

quote:
Originally posted by madhivanan

select t1.* from table t1 inner join
(
select product,min(time) as time from table group by product
) as t2
on t1.Product =t2.product and t1.Time=t2.time

That leaves
Bikes | 9:00 | 3
. How do I get the 8 back in? None of the other rows are even in the source table, as they were eliminated in the join.

I thought about it more after posting, and I guess you have to do taht join. I think the best approach would be to make the | 3 | term a nested subquery:

SELECT Product, 
Min(Time) AS FirstTime, 
Sum(Number) AS Sum,
(SELECT Number FROM Table AS Table2 WHERE Table2.Time = Table1.FirstTime AS FirstSale)
FROM Table AS Table1

---------
Ignorance may be bliss, but knowledge is thrill.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 13:41:24

select t1.*,t2.number from table t1 inner join
(
select product,min(time) as time,sum(number) as number from table group by product
) as t2
on t1.Product =t2.product and t1.Time=t2.time

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 13:47:16

[code]SELECT t.Product,
MIN(Time),
SUM(Number),
MAX(CASE WHEN RowNo=1 THEN Number ELSE NULL END) AS First
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Time) AS RowNo,*
FROM Table)t
GROUP BY Product[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources