Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2004
    Location
    Boston
    Posts
    13

    Unanswered: count the records

    I need to export records to a text file and simply index them. i.e. in the pipe delimited file I need a column with the ordinal value of each row.
    Example:

    1|"Jane Doe"|"23 Western ave"|...
    2|"Jamie Delom"|"5 East Street|...
    3|"Nat Girshon"|"5678 Main Street|...
    .
    .

    Would anyone be able to tell me how I could build this within the SQL query without creating a physical table and using IDENTITY functions?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Location
    Boston
    Posts
    13
    Should I consider creating a temp table? Is that the best solution? How would I code it? I'm a bit of a novice.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How many rows...and is the whole table or a subset...

    do you know how to script the ddl for a table in EM?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Location
    Boston
    Posts
    13
    Originally posted by Brett Kaiser
    How many rows...and is the whole table or a subset...

    do you know how to script the ddl for a table in EM?

    There is about 5000 records in the query results.
    No, have never scripted a ddl in Ent. Mgr.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Go to the tables folder, find the table and right click on it...

    Click on all tasks>generate sql scripts

    When you get the dialog up...chooseyour options and click preview..

    cut and paste the ddl it shows you here...

    Also why don't you add an identity column to that table?

    because there is a lot of select *'s going on in the app, right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Won't this work, or is Name not a unique key?

    select count(*) as Ordinal, YourTable.Name, YourTable.Address, ...
    from YourTable
    inner join YourTable TableCopy on YourTable.Name >= TableCopyName
    group by YourTable.Name, YourTable.Address, ...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2004
    Location
    Boston
    Posts
    13
    Originally posted by Brett Kaiser
    Go to the tables folder, find the table and right click on it...

    Click on all tasks>generate sql scripts

    When you get the dialog up...chooseyour options and click preview..

    cut and paste the ddl it shows you here...

    Also why don't you add an identity column to that table?

    because there is a lot of select *'s going on in the app, right?

    Thanks for the quick reply Brett. I'm not sure if setting an identity column would help here because I am grabbing a smaller subset of this table. I would guess that the results of this query would not show sequential identiy values.

  9. #9
    Join Date
    Mar 2004
    Location
    Boston
    Posts
    13
    Originally posted by blindman
    Won't this work, or is Name not a unique key?

    select count(*) as Ordinal, YourTable.Name, YourTable.Address, ...
    from YourTable
    inner join YourTable TableCopy on YourTable.Name >= TableCopyName
    group by YourTable.Name, YourTable.Address, ...

    This is good stuff. I will try it. Thanks!

  10. #10
    Join Date
    Mar 2004
    Location
    Boston
    Posts
    13
    Oooh.. Actually it just looks like this:


    1|"Jane Doe"|"23 Western ave"|...
    1|"Jamie Delom"|"5 East Street|...
    1|"Nat Girshon"|"5678 Main Street|...


    If only there was some way to initialize the first value (1)
    and then in the subsequent records reference the "previous"
    record and add one!?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Did you make sure to use ">=" in your join, and not just "="?

    Post your query statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Mar 2004
    Location
    Boston
    Posts
    13

    Talking

    Excellent! I did, in fact miss the >=. It takes a great deal of time to run... but it is certainly worth it! Thanks!

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Go Saux!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Mar 2004
    Location
    Boston
    Posts
    13
    Originally posted by Brett Kaiser
    Go Saux!
    Rauck on! If you eva waunt to drive yau cau
    up hia for a game... Yau welcome!

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where in Boston are you?

    Sister in law lives Winchester

    April 16th is looking like a good day to visit....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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