How to do Order By in Sub Query

[Danial]

Hi,
Can any one please help me with sorting with sub query, i have a query something like the following.

Select a, b, c, (Select Count(b) From MyTable Where c=a as MyCount) From TableName

Now as you can see i have a count field called MyCount which is the result of a subquery, the thing i want to know is how would i use order by stement on that count field.

I tried [Select a, b, c, (Select Count(b) From MyTable Where c=a as MyCount) From TableName Order By MyCount] but it doesnt work.

Any ideas would be appriciated.

Thanks

Danial

[PilgrimPete]

Might be missing the point here, but aren't your parentheses in the wrong place..?

SELECT a, b, c, 
(Select Count(b) From MyTable Where c=a) as MyCount
FROM TableName 
ORDER BY MyCount

[Danial]

Hi,
Do you mena it should be like

SELECT a, b, c, 
(Select Count(b) From MyTable Where c=a as MyCount)
FROM TableName 
ORDER BY MyCount

If i try that it gives me syntax error.

[pvb]

Well, this works but i'm pretty sure it's not what you're looking for(i included the sample script i used):

if object_id('TableName') is not null
	drop table TableName
go

Create Table TableName
(
	a int,
	b int,
	c int
)
go

insert into TableName (a, b, c) values (1, 2, 3)
insert into TableName (a, b, c) values (1, 3, 3)
insert into TableName (a, b, c) values (2, 16, 3)
insert into TableName (a, b, c) values (4, 12, 4)
insert into TableName (a, b, c) values (1, 3, 1)
go

Select a, b, c, 
(Select Count(b) From TableName Where c=a) as MyCount
From TableName 
Order By MyCount
go

Now the above script works like a champ(and to make sure, this is all SQL Server 2000 no Access). But lookin at your code I'm thinking that you're looking for something different and possibly going about it at the wrong angle. What exactly(without using code) are you trying to accomplish with the sub-select? on the above code, of course you'll only get one value in the sub-select, so doing the Order By on the sub select is meaningless. However, if you're doing a correlated sub query, the tables and columns you're trying to use don't really make sense either. Are you really trying to sum up information? How many tables are you working with?(you reference two in your example, TableName and MyTable). Perhaps describing the problem(not in code but in english) you're trying to solve would help clear some things up.

[PilgrimPete]

Danial,

Look closely at the SQL in both mine and pvb's post - the 'AS MyCount' need to be outside the parentheses - as it is a column in the outer query that is aliased to the output of the subquery.

[ViagraFalls]

Something like this?

SELECT
a.Firstname
, a.Lastname
, COUNT(b.EmployeeID) As OrderCount
FROM
employees AS a
INNER JOIN orders AS b
ON a.EmployeeID = b.EmployeeID
GROUP BY
a.Firstname
, a.Lastname

ORDER BY
OrderCount DESC

(Uses Northwind, and makes a selection from two tables. The count function counts the number of orders for each employeeID).

-Vf-

[Danial]

Originally posted by pvb
lookin at your code I'm thinking that you're looking for something different and possibly going about it at the wrong angle. What exactly(without using code) are you trying to accomplish with the sub-select?

Ok let me try to explain what i am trying to do, i am writing my own forum. I am trying to display all the member list, now the information are stored in 3 tables, User, Post, Threads. I want the user to be able to sort by eg username, first name etc.

I have two count fields eg the post count and the thread count i want to let the user to sort by those fields too.

I am no sql expert but the below is what i am using as the query.

if you wanna see this in action click here
http://www25.brinkster.com/asianism/...getalluser.asp

	sql="SELECT DISTINCT User.UserId, User.Username, User.Name, User.Date_Registered, User.Status, User.Location, User.URl, User.Email, User.MSN, User.Yahoo, User.ICQ, User.DOB, User.Icon, User.HideEmail, User.HideDOB, User.Title, User.LastLoggedIn, User.Banned, Count(Post.PostId) AS Post" &_
	",(Select Count(ThreadId) From Thread Where UserId=User.UserId) as ThreadCount " &_
	" FROM [User] LEFT JOIN Post ON User.UserId = Post.UserId" &_
	" GROUP BY User.UserId, User.Username, User.Name, User.Date_Registered, User.Status, User.Location, User.URl, User.Email, User.MSN, User.Yahoo, User.ICQ, User.DOB, User.Icon, User.HideEmail, User.HideDOB, User.Title, User.LastLoggedIn, User.Banned" &_
	" HAVING (((User.Username) Like '" & searchtext & "%'))" &_
	" ORDER BY " & orderby & " " & asc

Now i can order by any field except for ThreadCount, May be my sql query is wrong, is there a better way of doing it.

This is the relation ship of the table :
Thread : ThreadId, ThreadTitle, UserId
Post : PostId, PostTitle, PostBody, ThreadId, UserId
User : UserId, ............

Again i would like to thank everyone for their response. I will try out your suggestion later on tonight when i get back home.

Danial

[Danial]

Just like to mention that i am using Access for the time being, but i would be using SQL server once i have finished writing it.

Thanks

[ViagraFalls]

Hmm... I briefly checked the query, and the page. Did you notice that on the asp page, the columnname is Threads, and not Threadscount? Try removing the SELECT partin front of the count, and remove the parenthesis ( (<-- These things --> )) (Not too sure if the proper english term is parenthesis ).

That might fix it.

-Vf-

[Danial]

Originally posted by ViagraFalls
Hmm... I briefly checked the query, and the page. Did you notice that on the asp page, the columnname is Threads, and not Threadscount? Try removing the SELECT partin front of the count, and remove the parenthesis ( (<-- These things --> )) (Not too sure if the proper english term is parenthesis ).

That might fix it.

-Vf-

Hi,
Thanks for looking, no the problem is not the column name. I am passing the right column name (ThreadCount). i do a if statement in the code, eg if orderyby ="threads" then orderby="ThreadCount".

Which Parenthesis do you mean, is it the one before and after the sub query? If i take those out i get syntax error !!

[Ecniv]

Just a thought but could you run a query (or stored process in SQL) which does the count bit and therefore allows you to sort which ever fields you like afterwards ?

Might be easier....

So your query (qrySortItAll for example) does the counts and grabs the fields...
You SQL State ment for access : "Select *.qrySortItAll Order By fieldname;"

Probably not what you wanted but should work... may be easier than trying to get the counted field into the order by - which I don't think you can do.

Alternatively - if you are adding in code, does the list/grid have a sort function OR you can store to an array then sort the array before putting into the list/grid

Regards

Vince

[ViagraFalls]

I'll look at it at home tonight. Could you script the two tables and post those here? That way it'll be easier to verify problems.

Thanks

[Danial]

Hi,
I have just run the same query in SQL Server Query Analyzer and guess what it works fine, but it wouldnt work in Access 97/2K.

Anywayone know why, this is really furstrating, both of them are MS products

Thats why i hate using Access, but as i am using asp and dont have access to SQL Server online, i am stuck with Access. I have to use it until i buy some space next month