Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    76

    Question Unanswered: rownum and sub rownum

    I have a table with six records. 4 A records and 2 B records.
    how do i count them by A and B. when I do a
    select rownum, col from table;
    i get:
    1 A
    2 A
    3 A
    4 A
    5 B
    6 B

    How can I get the following result?
    1 A 1
    2 A 2
    3 A 3
    4 A 4
    5 B 1
    6 B 2

    help Please

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: rownum and sub rownum

    What DBMS are you on? For Oracle there is the ROW_NUMBER function:

    select row_number() over (order by col),
    col,
    row_number() over (partition by col order by 1)
    from table;

  3. #3
    Join Date
    Nov 2003
    Posts
    76

    Thumbs up

    I am on Oracle. This worked. Thank you very much

  4. #4
    Join Date
    Nov 2003
    Posts
    76

    Question what does this mean

    Hi, I am unable to understand what this criteria means:

    ID = '"& request.querystring("oID") & "'

    in the following statement:

    select count(distinct hazmatclass) as hzcount
    from manifestexp
    where orderkey in
    (
    select orderkey
    from manifestexp
    where ID = '"& request.querystring("oID") & "'
    )
    group by hazmatclass;

    * manfestexp is a view.
    * ID is a column in that view.

    Any hints please??

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: what does this mean

    That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

    I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.

  6. #6
    Join Date
    Nov 2003
    Posts
    76

    Question Re: what does this mean

    Thank you very much for your feedback.
    I am not sure I understand the prepared statement part. How do I go about doing that.



    Originally posted by andrewst
    That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

    I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •