sort data from a table by count value's?

[Willem-Jan Selen]

Is it possible so sort data from a table based on the count results?

I have the following

<% set rs = db.execute("Select * from tbleventinfo order by ename")
while not rs.EOF
tmp = rs("eid")
set rs1 = db.execute("Select count(eid) from tbleventdj where eid = " &
tmp)
if rs1.EOF = false then
total = rs1.fields.item(0)
else
total = 0
end if
set rs1 = Nothing
echo rs("ename") & "(" & total & ")"

rs.moveNext
if rs.EOF = false then Response.write "<br>"
wend
set rs = Nothing
%>

which works fine. but as you can see it sorts it all out bases on the
ename...

Any help apriciated!!

Willem-Jan Selen

[Tammy B.]

look up "rank"
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058827442.339033@cache2...

Is it possible so sort data from a table based on the count results?

I have the following

<% set rs = db.execute("Select * from tbleventinfo order by ename")
while not rs.EOF
tmp = rs("eid")
set rs1 = db.execute("Select count(eid) from tbleventdj where eid = " & tmp)
if rs1.EOF = false then
total = rs1.fields.item(0)
else
total = 0
end if
set rs1 = Nothing
echo rs("ename") & "(" & total & ")"

rs.moveNext
if rs.EOF = false then Response.write "<br>"
wend
set rs = Nothing
%>

which works fine. but as you can see it sorts it all out bases on the
ename...

Any help apriciated!!

Willem-Jan Selen

[Willem-Jan Selen]

Thanx!! Works like a charm... and in one go aswell!

Willem-Jan Selen
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:uz**************@tk2msftngp13.phx.gbl...

One SQL statement should do it in one fell swoop:

SELECT tbleventinfo.ename as Name, Count(tbleventdj.eid) as CountOfEID
FROM tbleventinfo INNER JOIN tbleventdj
ON tbleventinfo.eid = tbleventdj.eid
GROUP BY tbleventinfo.ename
ORDER BY Count(tbleventdj.eid) DESC

Should get you:

Name CountOfEID

With CountOfEID decsending sort order (eg highest at top).

Untested so apologies for any typos.

Chris.
"Willem-Jan Selen" <ws****@hotmail.com> wrote in message
news:1058827442.339033@cache2...

Is it possible so sort data from a table based on the count results?

I have the following

<% set rs = db.execute("Select * from tbleventinfo order by ename")
while not rs.EOF
tmp = rs("eid")
set rs1 = db.execute("Select count(eid) from tbleventdj where eid =

" &

tmp)
if rs1.EOF = false then
total = rs1.fields.item(0)
else
total = 0
end if
set rs1 = Nothing
echo rs("ename") & "(" & total & ")"

rs.moveNext
if rs.EOF = false then Response.write "<br>"
wend
set rs = Nothing
%>

which works fine. but as you can see it sorts it all out bases on the
ename...

Any help apriciated!!

Willem-Jan Selen