SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 selecting top X, with a twist
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 09/04/2006 :  03:49:21  Show Profile  Send aiken an ICQ Message  Reply with Quote
I've got a table that holds a list of users who are currently online. It references various other tables, but the problem I'm having can be illustrated in the following simple example:

create table usersOnline(i_users int,state int,myOrder int)

insert into usersOnline(i_users,state,myOrder)
select 100,10,1 union all
select 200,10,2 union all
select 300,10,3 union all
select 400,20,1 union all
select 500,30,1 union all
select 600,30,1


Now what I want is the first user for each state, ordered by myOrder. That is, the result I want is a recordset with three rows: 100, 400, 500.

I can see how to do it like this:

declare @t table (s int,o int)
insert into @t (s)
  select distinct state from usersOnline

update @t set
  o=min(myOrder)
from usersOnline
where s=usersOnline.state

select i_users
from usersOnline
  join @t t on t.state=usersOnline.state
    and t.myOrder=usersOnline.myOrder


That update may be wonky and might need a subquery, but you get the idea. Still, it's ugly as all getout. Is there a way to accomplish this in a single select, or at least more gently than three queries and a table variable?

Thanks
-b

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 09/04/2006 :  04:00:41  Show Profile  Reply with Quote

select	* 
from 	usersOnline u
where	i_users = (select min(i_users) from usersOnline x where x.state = u.state)




KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:06:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why is not the order correct for STATE 30, as it is for STATE 10?
The last record would be 600, 30, 2
then all you have to do is a select * from usersonline where myorder = 1.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:08:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		state,
		myOrder,
		MIN(i_users) i_users
FROM		@UsersOnline
WHERE		MyOrder = 1
GROUP BY	state,
		myOrder


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 09/04/2006 :  04:16:39  Show Profile  Reply with Quote
Oh, I tought the myOrder column is the current sequence of order


KH

Go to Top of Page

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 09/04/2006 :  04:21:13  Show Profile  Send aiken an ICQ Message  Reply with Quote
Argh, sorry -- my sample data was ambiguous. I don't care about getting the min(i_users); I care about getting one i_users for each state, and I want the i_users for whom the myOrder is the lowest value for that state. Here, let me provide new sample data:
create table usersOnline(i_users int,state int,myOrder int)

insert into usersOnline(i_users,state,myOrder)
select 800,10,1 union all
select 200,10,2 union all
select 300,10,3 union all
select 400,20,1 union all
select 500,30,1 union all
select 600,30,2 union all
select 999,40,1 union all
select 950,40,2


For that sample data, my desired results are four rows: 800,400,500,999. And while I don't need the state or MyOrder values in the result rows, it is fine if one or both are there.

Sorry for the lack of clarity.

-b

Edited by - aiken on 09/04/2006 04:29:17
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:28:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
select * from usersonline where myorder = 1


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/04/2006 :  04:29:23  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote

Declare @usersOnline table (i_users int,state int,myOrder int)

insert into @usersOnline(i_users,state,myOrder)
select 800,10,1 union all
select 200,10,2 union all
select 300,10,3 union all
select 400,20,1 union all
select 500,30,1 union all
select 600,30,2 union all
select 999,40,1 union all
select 950,40,2


Select * from @usersOnline u
Where i_Users = (Select Top 1 i_Users  From @usersOnline o Where o.state = u.state) 




Chirag
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 09/04/2006 :  04:29:44  Show Profile  Reply with Quote
select	*
from	usersOnline u
where	myOrder = (select min(myOrder) from usersOnline x where x.state = u.state) 



KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:31:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And if your logic with MyOrder not always starts with 1, use this
select		u.*
from		usersonline u
inner join	(
			SELECT		state,
					min(myorder) mo
			FROM		usersonline
			group by	state
		) m ON m.State = u.state and m.mo = u.myorder


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 09/04/2006 :  04:32:09  Show Profile  Reply with Quote
Peso beat me to it.

At least mine will work if the lowest myOrder is not 1


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:33:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No worry man, I suggested WHERE = 1 already in my first post.
Good solution though!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 09/04/2006 :  04:34:12  Show Profile  Send aiken an ICQ Message  Reply with Quote
Wow, nice solutions. Thanks for the speedy and clever replies!

-b
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000