aspose file tools*
The moose likes JDBC and the fly likes select query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select query" Watch "select query" New topic
Author

select query

vernon mweetwa
Greenhorn

Joined: Nov 26, 2004
Posts: 24
I have a table called person, which has fields person number, name and age. I want a query which retrieves person details according to age group. i.e the program should be able to print people in the following age groups separately:

<25
25 - 30
31 -35
>35

This means that the query should be able to select people who are below 25 years and print them alone, followed by those of 25 to 30 years, followed by 32 to 35 years and finally those aged above 35 years from the same query.

how would be the query wriitten or just a snippet of it will do.
[ February 10, 2005: Message edited by: Bear Bibeault ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

If you want to do this in SQL, read up on the BETWEEN comparisson operator - paying particular attention to the effect the ordering of it poarameters.

Alternatively, if you wanted to avoid four DB round trips, you could just return the lot ordered by age and do the grouping in Java.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
You can do a union. It wont be too efficient though

like this



or you can have another age_grouping table and do a join to that table

like this



age_grouping should have 3 columns: age_group, start_age and end_age. Pre-populate the age_grouping table with your age ranges
age_group start_age end_age
1 0 25
2 25 30
3 30 35

and so on

It might be better to do it in Java though(like Paul said)

or you might want to add another column called age_group in your person table and set the age_group whenever you insert a person(or change the age)
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


You can do a union

...of course if you did that, you are basically selecting everything that is in the table in one go, but using 4 queries to do it. Dunno if that's a sensible plan...
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
Originally posted by Paul Sturrock:

...of course if you did that, you are basically selecting everything that is in the table in one go, but using 4 queries to do it. Dunno if that's a sensible plan...


Right!! I agree it's not the most efficient.

But, it all depends on how much leeway Vernon has. We dont know enough about his problem to say for sure whether he will be able to do in Java or not. I'm just providing alternate solutions.

For example, if he needs to use this as a sub-query, then retreiving the result in Java and querying the database again for each record will be inefficient. It all depends on what his problem is.
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Vernon,
I know this is a little off-topic but, are you really storing the age -- as opposed to storing the date of birth?


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29219
    
134

Vernon,
I agree with Paul that this would be better to do in Java, or whatever language you are doing the processing in. For example, if the whole program resides on the database, you could use a stored procedure.

Ben,
I don't think your comment is off topic at all. Storing an age is risky if the data is long lived.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Bianca Hagen
Ranch Hand

Joined: Apr 28, 2004
Posts: 41
I could be just right to store the age in stead of the birthday!
You don't know where he is using it for.
Lets say its a survey and he wants to link the answers to the age of the people. In that case it's not sensable at all to store the birthday!
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Bianca Hagen:
I could be just right to store the age in stead of the birthday!


I am not in favor of storing age in database, as age is going to change every day ,every year.

I strongly recommend storing as date

vernon:

If you are using Oracle there are some pretty functions (Windowing Functions)in oracle which can result in range based data.

I have only read about these...

plese refer to one PDF at

this URL

thanks


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
I think Bianca's point is valid: there are some cases where storing the age either makes sense or is the only option. The survey example is a good one. You may not have even asked for the birthdate, or you may not know when the survey was taken, but you must know the age of the respondent at the time of the survey -- not on any future date.

Clearly, if you need to be able to calculate a person's age at any time, you'll need their birthdate.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29219
    
134

Even if you don't have the exact date, I still think you are better off storing a year of birth (granted, you could be off by a year.) Or, you could store the age and the date that age was entered. Just storing the age yields out of date information very quickly. In just one year a 24 year old switches ages groups in the given example.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Jeanne Boyarsky:

Even if you don't have the exact date, I still think you are better off storing a year of birth (granted, you could be off by a year.)



I agree !!

If not date then atlease year of birth should be stored !!!

thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: select query
 
Similar Threads
Why is this called Meaningless Drivel?
The Age Question?
Making friends after 25
Too Old To Punch More Cows?
Age Discrimination