selecting top X, with a twist - 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)

selecting top X, with a twist

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-09-04 : 03:49:21

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)

17689 Posts

Posted - 2006-09-04 : 04:00:41

[code]
select *
from usersOnline u
where i_users = (select min(i_users) from usersOnline x where x.state = u.state)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:06:23

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:08:56

[code]SELECT state,
myOrder,
MIN(i_users) i_users
FROM @UsersOnline
WHERE MyOrder = 1
GROUP BY state,
myOrder[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 04:16:39

Oh, I tought the myOrder column is the current sequence of order

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-09-04 : 04:21:13

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:28:49

select * from usersonline where myorder = 1

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-04 : 04:29:23

[code]
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)

[/code]

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 04:29:44

[code]select *
from usersOnline u
where myOrder = (select min(myOrder) from usersOnline x where x.state = u.state)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:31:33

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 04:32:09

Peso beat me to it.