group by (subselect from temp table)

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)

group by (subselect from temp table)

Author

Topic

kristijanv
Starting Member

8 Posts

Posted - 2009-01-27 : 09:30:19

I have a select and the order by clause is changing dynamically

--this part is always sames only temp table is changing (##RAZVCENIKOM_KUTTER0_877357718)

SELECT T.Razlika, G.Referent, G.DatumDDV, G.Kljuc, G.Datum, G.Prejemnik, G.Valuta, P.Poz, P.Ident, P.Naziv, P.Kolicina,
P.Cena AS CenaPoz, P.Rabat, P.CenaZaVred, M.MpCena, M.VpCena, M.VpCena2, M.Sestavnica, P.StrN, P.Oddelek,
T.Nabava, T.Prodaja, T.Razlika, T.SumKolicina FROM Promet G, PrometPoz P, MS M, ##RAZVCENIKOM_KUTTER0_877357718 T
WHERE G.Kljuc = P.Kljuc AND P.Ident = M.Ident AND P.Kljuc = T.Kljuc and P.Poz = T.Poz --and g.prejemnik='BERNARDIN HOTELI'

--this part is changing dynamically
ORDER BY (select Sum(X.Razlika) from ##RAZVCENIKOM_KUTTER0_877357718 X, Promet Y where X.Kljuc = Y.Kljuc and Y.Prejemnik = G.Prejemnik) desc

--i would like to have order by something like that but it doesnt work that way
ORDER BY (select Sum(X.Razlika) from T X, Promet Y where X.Kljuc = Y.Kljuc and Y.Prejemnik = G.Prejemnik) desc

have anybody any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:34:37

use CASE WHEN

kristijanv
Starting Member

8 Posts

Posted - 2009-01-27 : 09:36:07

i don't understand.what you mean.

can u paste an example?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:38:08

before that, can you illustrate with some data the sorting you're looking at?

kristijanv
Starting Member

8 Posts

Posted - 2009-01-27 : 09:48:18

something like that:

t.razlika g.prejemnik sort
23 x 83
4 x 83
56 x 83
6 y 24
7 y 24
2 y 24
9 y 24
2 z 6
1 z 6
3 z 6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:51:02

[code]
SELECT *
FROM
(
SELECT T.Razlika, G.Referent, G.DatumDDV, G.Kljuc, G.Datum, G.Prejemnik, G.Valuta, P.Poz, P.Ident, P.Naziv, P.Kolicina,
P.Cena AS CenaPoz, P.Rabat, P.CenaZaVred, M.MpCena, M.VpCena, M.VpCena2, M.Sestavnica, P.StrN, P.Oddelek,
T.Nabava, T.Prodaja, T.Razlika, T.SumKolicina,
SUM(t.razlika) OVER (PARTITION BY g.prejemnik) AS SortVal
FROM Promet G, PrometPoz P, MS M, ##RAZVCENIKOM_KUTTER0_877357718 T
WHERE G.Kljuc = P.Kljuc AND P.Ident = M.Ident AND P.Kljuc = T.Kljuc and P.Poz = T.Poz --and g.prejemnik='BERNARDIN HOTELI'
)t
ORDER BY SortVal
[/code]

kristijanv
Starting Member

8 Posts

Posted - 2009-01-27 : 11:40:36

splendid :) it works fine

SELECT T.Razlika, G.Referent, G.DatumDDV, G.Kljuc, G.Datum, G.Prejemnik, G.Valuta, P.Poz, P.Ident, P.Naziv, P.Kolicina,
P.Cena AS CenaPoz, P.Rabat, P.CenaZaVred, M.MpCena, M.VpCena, M.VpCena2, M.Sestavnica, P.StrN, P.Oddelek,
T.Nabava, T.Prodaja, T.Razlika, T.SumKolicina FROM Promet G, PrometPoz P, MS M, ##RAZVCENIKOM_KUTTER0_877357718 T
WHERE G.Kljuc = P.Kljuc AND P.Ident = M.Ident AND P.Kljuc = T.Kljuc and P.Poz = T.Poz --and g.prejemnik='BERNARDIN HOTELI'
ORDER BY SUM(t.razlika) OVER (PARTITION BY g.prejemnik) desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 11:59:49

welcome

kristijanv
Starting Member

8 Posts

Posted - 2009-01-28 : 03:02:31

shit :(

its sql 2000 :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 03:58:15

[code]SELECT T.Razlika, G.Referent, G.DatumDDV, G.Kljuc, G.Datum, G.Prejemnik, G.Valuta, P.Poz, P.Ident, P.Naziv, P.Kolicina,
P.Cena AS CenaPoz, P.Rabat, P.CenaZaVred, M.MpCena, M.VpCena, M.VpCena2, M.Sestavnica, P.StrN, P.Oddelek,
T.Nabava, T.Prodaja, T.Razlika, T.SumKolicina INTO #Temp
FROM Promet G, PrometPoz P, MS M, ##RAZVCENIKOM_KUTTER0_877357718 T
WHERE G.Kljuc = P.Kljuc AND P.Ident = M.Ident AND P.Kljuc = T.Kljuc and P.Poz = T.Poz --and g.prejemnik='BERNARDIN HOTELI'

SELECT t1.*
FROM #Temp t1
INNER JOIN (SELECT prejemnik,SUM(Razlika) AS RazSum
FROM #Temp
GROUP BY prejemnik) tmp
ON tmp.prejemnik=t1.prejemnik
ORDER BY RazSum DESC[/code]

kristijanv
Starting Member

8 Posts

Posted - 2009-01-28 : 04:25:59

the main problem is as i wrote that i cannot change the first part (its a third party application):

this part is always sames only temp table is changing (##RAZVCENIKOM_KUTTER0_877357718)

SELECT T.Razlika, G.Referent, G.DatumDDV, G.Kljuc, G.Datum, G.Prejemnik, G.Valuta, P.Poz, P.Ident, P.Naziv, P.Kolicina,
P.Cena AS CenaPoz, P.Rabat, P.CenaZaVred, M.MpCena, M.VpCena, M.VpCena2, M.Sestavnica, P.StrN, P.Oddelek,
T.Nabava, T.Prodaja, T.Razlika, T.SumKolicina FROM Promet G, PrometPoz P, MS M, ##RAZVCENIKOM_KUTTER0_877357718 T
WHERE G.Kljuc = P.Kljuc AND P.Ident = M.Ident AND P.Kljuc = T.Kljuc and P.Poz = T.Poz --and g.prejemnik='BERNARDIN HOTELI'

i can only change the part where is order by clause:(

i will try to tell client to migrate on sql2005 i thing thats the only solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:53:22

but you're not changing first part. you're just storing it in a temp table here and then taking data from it.

kristijanv
Starting Member

8 Posts

Posted - 2009-01-28 : 09:09:39

I mean that select is hardcoded in program and therefore i cannot change it.. i can only trace it :(

i can only modify everything what is behind order by clause :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources