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.
| Author |
Topic |
|
g_r_a_robinson
Starting Member
45 Posts |
 Posted - 2004-11-16 : 19:58:22
|
| I have a query that works returns two columns of data. Column (aL will return a bunch of status's and column (b) will return an integer that represents the id of a user. (This is all inside the inner part of the query. I want to return one row where the max status is column(a). So if the max is 4 I will get the 4 and its corresponding column or integer. Problem is that my query only returns my first column status. When I try to add the changed_by_id returned from the inner part I get the 'not a single group by function'. Which makes sense I'm using an aggregate with a non-aggregate type. Can anyone help me with a workaround. Heres the query. select max(status) from ( select s.status, s.changed_by_id from oh.subscriptions s inner join oh.plans p on s.plan_id = p.plan_id inner join oh.dial_in_services ds on ds.subscription_id = s.subscription_id inner join oh.usernames u on ds.username_id = u.username_id where s.customer_id = 315698 and s.followed_by_id is null --and s.changed_by_id = 9 --and rownum = 1 group by s.status, s.changed_by_id ) where status < 5 By the way this in Oracle. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-16 : 21:38:28
|
| select top 1 status, changed_by_id from(....)order by status descp.s. you don't need the group by or indeed the subuery.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
How do i do this?