Results 1 to 13 of 13

Thread: Top 3

  1. #1
    Join Date
    Mar 2008
    Posts
    15

    Top 3

    Hi
    I am creating a database which has approx 8 tables now from each of the tables i need to create a chart which shows between certain dates the top 3 figures between all the tables i have tried the topvalue but that shows all the tables top 3 values so its not appropriate can anyone help please

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What's your RDBMS please young sir\ madam?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2008
    Posts
    15

    Rdbms

    I am not sure what this means is it to do with the relationships within the database? If so then i do not have any although i know i perhaps should the database consists a number of tables one which calulates fields and then approx 8 which hold the number for each error found this where i am possibly going to need a make table query to pull in all the relevent data from those error tables and somehow pulling out the top 3 numbers within that table between certain dates. Thank you not sure this will help i will be back in myoffice on Monday and should be able to give you a bit more info on the database so far. once again thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, what pootle wants to know is which database system you are using

    access? sybase? informix? db2? firebird? oracle? sql server?

    you see, the answer to the question you asked might depend on what database system you're using

    also, you need to be a wee bit more specific

    first, you say "from each of the tables i need to create a chart"

    so that sounds like 8 charts

    but then you say "the top 3 figures between all the tables"

    furthermore, you also need to give some information on which top 3 -- "top" doesn't make sense unless you tell us what it is based on

    it sounds like it would be based on a COUNT(*) but it's hard to tell from over here

    here is the way i would solve it --
    Code:
    with combined_tables as
    ( select foo, bar, qux from table1
      union
      select foo, bar, qux from table2
      union
      select foo, bar, qux from table3
      union
      select foo, bar, qux from table4
      union
      select foo, bar, qux from table5
      union
      select foo, bar, qux from table6
      union
      select foo, bar, qux from table6
      union
      select foo, bar, qux from table8 )
    select foo, bar, qux
      from combined_tables as t
     where ( select count(*)
               from combined_tables
              where qux > t.qux ) < 3
    that's standard sql, and i've used placeholder names for the table and column names that you forgot to tell us, so if that doesn't work in your particular database system, then next time you will know how to ask the question better, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2008
    Posts
    15

    Thank you

    Thank you very much for your reply. Sorry i am using access.
    At the moment my database consists of one table for working out calculations.
    It consists of 15 tables for a specific error found within each product checked, inside these tables the operator just inputs a number then i need to create one chart consisting of the highest 3 numbers from within all 15 tables based on the between dates that the operator supplies when prompted. I think i may be a little to inexperienced to deal with this database so sorry if my questions seem a little hazzey thank you .

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Do you have 15 tables for recording 15 different errors? If so - ordinarily you would have one errors table and then a column to indicate which of the 15 errors any particular record relates to. Does this make sense? What are you reasons for using 15 tables?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also....
    Quote Originally Posted by Damregal
    I am not sure what this means
    If yuou aren't sure about anything then Google is your best friend and should be first port of call. What you know is important - knowing how to find out what you don't know is critical in this game
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2008
    Posts
    15

    Ok

    So there is no particular reason why i have not put all the errors in one table so if i did that how would i then specify the highest three numbers from all the columns between user defined dates?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    columns? why would there be multiple columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2008
    Posts
    15

    Each error

    A column for each error so for example

    Date Error1 Error2 Error3 Error4 Error5
    31/01/08 1 10 4 6 8
    01/02/08 7 9 2 1 6

    so then what i want to do it pull out the highest three numbers with specific criteria on the data so the user will enter the dates to look between.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    We could go on for a while here, picking up point by point. I think perhaps a good idea would be for you to do some reading up on the basics of database design. It might seem like a pain now but it will make life much, much easier in the long run.

    http://www.tonymarston.net/php-mysql...se-design.html

    The reason I say this is that your table design is definately wrong, wrong, wrong
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2008
    Posts
    15
    Ok then i will give it a read thank you very much for you help

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My pleasure. We will be here when you get back

    Your mission should be to be able to explain what is wrong with your table design and how it should look once you have finished the article.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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