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

Transact-SQL (2005)

SQL Quiry

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2008-09-30 : 00:29:24

Hi there

I am trying to figure out quickly using select instead cursor stuff:

I got the following data:

Supplier; Year; Amount
A, 1, 45
A, 2, 55
A, 3, 60
B, 1, 25
B, 2, 35
B, 3, 45
C, 1, 35
C, 2, 45
C, 3, 55

I want to sort out by sum (amount) and cheapest one is on the top:

B, 1, 25
B, 2, 35
B, 3, 45
C, 1, 35
C, 2, 45
C, 3, 55
A, 1, 45
A, 2, 55
A, 3, 60

How do I do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 00:32:57

[code]SELECT t.Supplier,t.Year,t.Amount
FROM YourTable t
INNER JOIN (SELECT Supplier,SUM(Amount) AS TotalAmount
FROM YourTable
GROUP BY Supplier)t1
ON t1.Supplier=t.Supplier
ORDER BY t1.TotalAmount,t.Supplier,t.Year[/code]

dewacorp.alliances

452 Posts

Posted - 2008-09-30 : 00:49:44

Actually, I worked out this :

select *
from upload a
right outer join (select TOP 100 Supplier from upload
group by Supplier
order by Sum(Amount) ) b on a.SUpplier = b.Supplier

But the issue TOP 100 which I ahve to set. How about if we more than 100?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 00:50:39

quote:
Originally posted by dewacorp.alliances

Actually, I worked out this :

select *
from upload a
right outer join (select TOP 100 Supplier from upload
group by Supplier
order by Sum(Amount) ) b on a.SUpplier = b.Supplier

But the issue TOP 100 which I ahve to set. How about if we more than 100?

whats the need of TOP 100?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-09-30 : 01:16:30

try this, there is no need of derived table


SELECT *
FROM UpLoad
ORDER BY SUM(Amount) OVER(PARTITION BY Supplier), Amount

dewacorp.alliances

452 Posts

Posted - 2008-09-30 : 03:02:46

quote:
Originally posted by PeterNeo

try this, there is no need of derived table

SELECT *
FROM UpLoad
ORDER BY SUM(Amount) OVER(PARTITION BY Supplier), Amount

What if I put RecID as another column so it will be like:

RecID, Supplier; Year; Amount
1, A, 1, 45
1, A, 2, 55
1, A, 3, 60
1, B, 1, 25
1, B, 2, 35
1, B, 3, 45
1, C, 1, 35
1, C, 2, 45
1, C, 3, 55
2, A, 1, 45
2, A, 2, 55
2, A, 3, 60
2, B, 1, 25
2, B, 2, 35
2, B, 3, 45
2, C, 1, 35
2, C, 2, 45
2, C, 3, 55

But the sum(total) is now based on the cheapest per RecID?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 03:09:33

[code]SELECT *
FROM UpLoad
ORDER BY SUM(Amount) OVER(PARTITION BY RecID),Supplier,Year[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 03:53:12

quote:
Originally posted by visakh16

whats the need of TOP 100?

Can't user ORDER BY in derived tables without a corresponding TOP.

I think OP wants TOP 100 PERCENT tough.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 03:55:18

[code]SELECT *
FROM UpLoad
ORDER BY SUM(Amount) OVER(PARTITION BY RecID, Supplier),
Year[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 04:03:47

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

whats the need of TOP 100?
Can't user ORDER BY in derived tables without a corresponding TOP.

I think OP wants TOP 100 PERCENT tough.

E 12°55'05.63"
N 56°04'39.26"

yeah...that makes more sense than top 100

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 04:05:06

quote:
Originally posted by Peso

SELECT *
FROM UpLoad
ORDER BY SUM(Amount) OVER(PARTITION BY RecID, Supplier),
Year

E 12°55'05.63"
N 56°04'39.26"

i think what OP wants is sum of Amount per RecID so i guess we need to partition only by recid.

vimalg2006
Starting Member

4 Posts

Posted - 2008-09-30 : 07:32:43

Hi

I got a table with names and email address and basically i want to change the email addresses - for example vimalg2006@yahoo.co.uk to vimalg2006@wackyplay.com .....changing the part after the '@' sign??

How do i do this on sql server 2005??? please help

Vimal Gohil
Vimalg2006@yahoo.co.uk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 07:38:41

quote:
Originally posted by vimalg2006

Hi

I got a table with names and email address and basically i want to change the email addresses - for example vimalg2006@yahoo.co.uk to vimalg2006@wackyplay.com .....changing the part after the '@' sign??

How do i do this on sql server 2005??? please help

Vimal Gohil
Vimalg2006@yahoo.co.uk

Dont hijack threads.please post your question as a new thread.

vimalg2006
Starting Member

4 Posts

Posted - 2008-09-30 : 07:39:28

there are 50+ people listed on the table for my above query

Vimal Gohil
Vimalg2006@yahoo.co.uk

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-30 : 08:18:18

Again, please post your question in a NEW THREAD. Thanks!

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources