queue number /help! - 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 2000 Forums

Transact-SQL (2000)

queue number /help!

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-04-29 : 06:06:15

User WorkID Money
paul 1 1000
jess 1 2000
jeremy 1 1500
paul 2 2000
jess 2 1000
jeremy 2 1500

Queue is depends on money. If you pay much you are close the 1st for the queue if you may less you are at the end if queue.I want a result like this for the paul. ( you have to query it by using user only). I tried temp tables but couldnt do it.
WorkID Queue
1 3
2 1

It is easy but my i can think it now :( :D

MS BLESS US

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-29 : 06:45:27

You can do it with a correlated subquery:

select [User] = 'paul', WorkID = 1, [Money] = 1000 into #myTable
union all select 'jess',1,2000
union all select 'jeremy',1,1500
union all select 'paul',2,2000
union all select 'jess',2,1000
union all select 'jeremy',2,1500

select	t1.*
	,(select count(*)from #myTable t2 where t1.WorkID = t2.WorkID and t1.Money <= t2.Money) as Queue
from	#myTable t1
where	[User] = 'Paul'

drop table #myTable

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-04-29 : 08:57:11

No, i think i couldnt explain it.
That thing is about ads. So if you pay much money than other companies your ad will be at 1 st queue.
in my table there are columns like as i wrote above.
so, in results it will contain the queue number of your ad depending on your money that paid.

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-04-29 : 09:01:17

i think it is imposibble.
because there will be same USer on the table and you have to query it accoding to user.
you have to make a loop but for many records that will kill the server.
i will passs it and will solve on other way.

MS BLESS US

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-29 : 09:16:59

Did you even try the query I posted?

If this is what you want for user Paul:
WorkID Queue
1 3
2 1

then that query does exactly what you want.
I think you give up much too easily.

rockmoose

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-04-29 : 09:32:39

yeah i tried union select but what about for 1000 records
so i will ? :
select [User] = 'paul', WorkID = 1, [Money] = 1000 into #myTable
union all select User,WorkID,Money from tablename ?

MS BLESS US

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-29 : 13:49:30

I just posted an example with a temporary table (#myTable).

You want to try this statement on your table:

select	t1.*
	,(select count(*) from tablename t2 where t1.WorkID = t2.WorkID and t1.Money <= t2.Money) as Queue
from	tablename t1
where	[User] = 'Paul'

rockmoose

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-04-30 : 08:32:13

ok thx i solved it.
i have another question.
in that i tried group by but dudunt worked.
table Jobs

userID-----Word-----Money
1---------name----1000
1---------name1---800
1---------name3---1200
2---------name----1500
2---------name1---600
2----------name3---1800

result will be orderd by the highes value of money and will be grouped by word. It will choose the word with the highest amount of money that paid by the user

word-----UserID-----Money
name----2-----------1500
name1--1-----------800
name3---2----------1800

i tried select Word,UserID,Max(money) from jobs group by Word,UserID
but it doenst give me the result.
ii have an alternate way but it will cause server to work harder

MS BLESS US

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-30 : 08:40:36

try:

select Jobs.Word, Jobs.UserID, Jobs.Money
from Jobs inner join (select Word,max(Money) as Money from Jobs) highestMoneyPerWord
on Jobs.Word = highestMoneyPerWord.Word and Jobs.Money = highestMoneyPerWord.Money

rockmoose

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-04-30 : 09:01:16

yeah right
but select Word,max(Money) as Money from Jobs) after that it needs group by i think :D ok i made it thx again

MS BLESS US

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-30 : 09:08:28

You are right, I forgot the group by.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources