SEO

#1 (**permalink**) 02-29-2008, 04:40 AM

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.

#2 (**permalink**) 02-29-2008, 04:40 AM

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)

#3 (**permalink**) 02-29-2008, 04:41 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

query help