query help

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.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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||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