sort data from a table by count value's?

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
Mon Jul 21 17:47:48 CDT 2003

look up "rank"


"Willem-Jan Selen" <wselen@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
Mon Jul 21 20:31:39 CDT 2003

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

Willem-Jan Selen


"Chris Barber" <chris@blue-canoe.co.uk.NOSPAM> wrote in message
news:uzJpsu9TDHA.1912@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" <wselen@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
> >
> >
>
>