Thanks Hugo.



Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<u540o0p73v88i1c6cb6vss27fggt1o4vl7@4ax.com>. ..
> On 27 Oct 2004 10:34:55 -0700, Parth wrote:
>
> >I have the following table -
> >
> >Name Enrolment# File#
> >x 422 011
> >y 421 022
> >z 444 023
> >a 345 024
> >
> >
> >I have to produce the following table -
> >
> >S.No Name Enrolment# File#
> >1 a 345 024
> >2 y 421 022
> >3 x 422 021
> >4 z 444 023
> >
> >
> >Could someone please help me out with the SQL query to do the
> >operation above? I know how to sort by enrolment# but how do I produce
> >the first column of the target table?
> >
> >Thanks in Advance.
> >
> > - P.
>
> Hi Parth,
>
> Try the following queries. Since you didn't provide CREATE TABLE and
> INSERT statements to base my tests on, I didn't test them.
>
> SELECT COUNT(*) AS "S.No",
> a.Name, a.Enrolment#, a.File#
> FROM YourTable AS a
> INNER JOIN YourTable AS b
> ON b.File# >= a.File#
> GROUP BY a.Name, a.Enrolment, a.File#
> ORDER BY a.File# DESC
>
> SELECT (SELECT COUNT(*)
> FROM YourTable AS b
> WHERE b.File# >= a.File#) AS "S.No",
> a.Name, a.Enrolment#, a.File#
> FROM YourTable AS a
> ORDER BY a.File# DESC
>
>
> Best, Hugo