Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

This may be a chanlenge question

Hello, everyone, I know you are all busy I have I think is a little complicated question but maybe somebody has done something like this before. What I want to do is in Java query a table that some information. Which is fine however. I want to output this to a file in a certain format that is requested. The problem I am having one of the key fields is not always the same and the other key field which is based on the first key field is not always the same either. I know this probably isn't making much sens but let me give you an example.
Let say I have a column that has the number 4 in for about 20 rows and then accross that is another column that may have 4 in it or also 3. Now there is more info in the row so what I want to do in java is select "" info that is related to number 4 and other coulumn 4 and output in a format. with out repeating the number 4 20 times. Then I need to output the info that is still related to the first coulmn 4 but now is related to the 3 instead of the for. So in other words I don't want to just read eact record and output it into the file. I need to to do it by bassing the relation ship on the coulumn that has 4 in it and the second coulmn that has 4 and 3. In other words I don't want reduntant coulmn 1 and 2 being repeating data. If anyone can help I will most happly hook you up with some points. Thanks for anyone that is willing to help.
0
Corey_819
Asked:
Corey_819
  • 10
  • 8
  • 6
  • +1
1 Solution
 
TimYatesCommented:
I'm still confused...

Can you give a better example?
0
 
CEHJCommented:
I'm not sure if i can follow all that, but this is one way of restricting the number of rows returned:

SELECT DISTINCT * WHERE key_col = 4
0
 
CEHJCommented:
Actually, i've got a dim memory that you can't use * with DISTINCT, so name the columns. It's always better to name the columns anyway in fact.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dualsoulCommented:
please post a sample. and also post the result you want to get from this sample.
0
 
TimYatesCommented:
Distinct will only skip rows that ALL the fields are the same, so

...ID...|...ID2...|...DATA...
...1.....|...1.......|...Fish.....
...1.....|...1.......|...Fish.....
...1.....|...1.......|...Cheese.....

will come out as:

1, 1, Fish
1, 1, Cheese

Tim
0
 
Corey_819Author Commented:
I am sorry I know it sounds confusion it took me a few days to figure out what I was trying to do. Okay like let say I have a record it has a hold load of info in it. But in the first two colmns I have lets say data that looks like this:
Key 1                                           key 2
444444                                         444444   Data.........................
444444                                         444444   ""
444444                                         444444   ""
444444                                         444444
444444                                         666666
555555                                         555555
555555                                         555555
555555                                         444444
555555
Now that is what some what my table looks like with more information in the records. Now I need to select all that data and output it to a file in this format such as:
Key 1 * 444444
Key 2 * 444444
      The all the data related to key 1 and key 2 those keys and records then the next output set would be this
Key 2 * 666666
      Then all the data related to the key 2 66666 would be underneath and  then the same format output with the 555555 and the key 2.

So basically I am saying I can't do a select * and set a where to equal something because my keys are changing and there are about 8 different key 1 fields and 12 different key 2 fields. I mean I could set the were to only output on the key I put in the string but to me that is a lot of wasted writting code. Plus there are about over 20000 records I would need to find all the keys that are distinguisedly listed. Any ideas.

Let me know if you have any other questions I wish I could send you a better sample of  the data but it is very perosnal data. Sorry so I am just trying to give you an example of what needs to be done. Thanks for the help
0
 
TimYatesCommented:
SELECT DISTINCT key1, key2 from yourtable ;

will give you all the available keypairs
0
 
TimYatesCommented:
Actually,

 SELECT DISTINCT key1, key2 from yourtable ORDER BY key1, key2 ;

will give you a neater list...

in this example, you will get:

444444, 444444
444444, 666666
555555, 444444
555555, 555555

Tim
0
 
TimYatesCommented:
You can then go through the returned resultset, and read each set of keys like:

SELECT data FROM yourtable WHERE key1=? AND key2=?
0
 
CEHJCommented:
Ah, select distinct - that rings a bell ;-)
0
 
TimYatesCommented:
Hee hee ;-)

Yeah...sorry about that ;-)

***Please note that CEHJ mentioned DISTINCT first, if that solves your problem!!***

(*cough* split *cough*) ;-)
0
 
CEHJCommented:
Some points to Tim for expanding it though .. :-)
0
 
TimYatesCommented:
:-)
0
 
Corey_819Author Commented:
Okay guys I am going to try the distinct, however one quick question does the key fields  need to be set as primary keys or one of the fields need to be set as primary key? Thanks
0
 
Corey_819Author Commented:
Okay wait, I think I forgot to tell you all something. The way the table is set up I still need the record where key 1 and key 2 equal each other, I just don't need key 2 repeating its data inf the field if it is the same as the current or previous record. Would distinct still work?

0
 
CEHJCommented:
There's no requirement that either needs  to be a primary key or even that there needs to be an index on either. Performance would be better if there were indexes though.
0
 
Corey_819Author Commented:
Wait I just noticed that I did not completly explain the way the table is laid. There are never records where key 1 = key 2. however there are records where key 1 will have the sam value or data that the previous and current reocord has and key 2 is the same way. I am sorry this probably changes the solution
0
 
TimYatesCommented:
Right this is a challenge as I haven't got a clue what's going on...

Do you have a complete example that is correct?
0
 
Corey_819Author Commented:
I am so sorry I got my thoughts twisted I have been just beathing my head trying to figure this out for about a week. I have just go twisted on what I didn't want to repeat. I really do apologize. Anyway if you are still willing to help I will provide the right example. This is what I need it too do::

key 1  key 2
444    333
444    333
444    222
555    111
555    111
555    111

Okay I need to display every single record in the data base I just don't need the key 1 and key 2 display the same data more then one time. Example
444
   333
       data in record every record that has 333
   222
       data in record for every 222.
555
   111
     all the records with 111
endof file.
This is what I need it to do I am sorry for the confusion I have been just running 5,000 thoughts threw my head last few days

0
 
CEHJCommented:
OK, you don't actually need DISTINCT. You can do the following and then iterate the result set. Try the query on its own first in your db console

select col1, col2 from sometable group by 1,2
0
 
CEHJCommented:
In fact, simply

select col1, col2 from sometable group by 1

should suffice
0
 
CEHJCommented:
The following should then produce the output your require

while (rs.next()) {
      String col1 = rs.getString("col1");
      out.println(col1); // 'out' is a  PrintWriter opened on a FileWriter
      while (rs.next()) {
            String col2 = rs.getString("col2");
            out.println("\t" + col2); // function to output to file
            String _col1 = rs.getString("col1");
            if (_col1.equals(col1) == false)
                  break;
      }
}
0
 
CEHJCommented:
The following should then produce the output your require

while (rs.next()) {
      String col1 = rs.getString("col1");
      out.println(col1); // 'out' is a  PrintWriter opened on a FileWriter
      while (rs.next()) {
            String col2 = rs.getString("col2");
            out.println("\t" + col2);
            String _col1 = rs.getString("col1");
            if (_col1.equals(col1) == false)
                  break;
      }
}
0
 
Corey_819Author Commented:
Okay that will give me a good idea on how to begin and what I need to do Thank you for all your help
0
 
CEHJCommented:
:-)

Sorry about the DISTINCT red herring ;-)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 8
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now