Top 3

Damregal · #1 (**permalink**) 03-14-08, 07:27

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

pootle flump · #2 (**permalink**) 03-14-08, 07:36

What's your RDBMS please young sir\ madam?

Damregal · #3 (**permalink**) 03-15-08, 08:17

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

r937 · #4 (**permalink**) 03-15-08, 08:58

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?

Damregal · #5 (**permalink**) 03-17-08, 03:02

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 .

pootle flump · #6 (**permalink**) 03-17-08, 05:55

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?

pootle flump · #7 (**permalink**) 03-17-08, 06:41

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

Damregal · #8 (**permalink**) 03-17-08, 11:20

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?

r937 · #9 (**permalink**) 03-17-08, 11:25

columns? why would there be multiple columns?

Damregal · #10 (**permalink**) 03-17-08, 11:52

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.

pootle flump · #11 (**permalink**) 03-17-08, 12:04

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