Hi, i have a database with a table that records all the types of stones, eg.

ID Size Shape

1 1.1 round
2 1.2 square
3 1.3 round
4 2 round
5 2.1 square
6 2.5 round
7 3.7 square
8 3.9 square
9 4.1 round
10 4.9 square
11 5 round

What i MUST do is sort it into individual shape, also for every size's range i must display a sub-heading like..

ROUND SHAPE - 1.0 to 1.9
{display all stones within 1.0 to 1.9 which is round}
ROUND SHAPE - 2.0 to 2.9
{display all stones within 2.0 to 2.9 which is round}
ROUND SHAPE - 3.0 to 3.9
{display all stones within 3.0 to 3.9 which is round}
ROUND SHAPE - 4.0 to 4.9
{display all stones within 3.0 to 3.9 which is round}
ROUND SHAPE - 5.0 to 5.9
{display all stones within 5.0 to 5.9 which is round}

SQUARE SHAPE - 1.0 to 1.9
{display all stones within 1.0 to 1.9 which is Square}
SQUARE SHAPE - 2.0 to 2.9
{display all stones within 2.0 to 2.9 which is Square}
SQUARE SHAPE - 3.0 to 3.9
{display all stones within 3.0 to 3.9 which is Square}
SQUARE SHAPE - 4.0 to 4.9
{display all stones within 4.0 to 4.9 which is Square}
SQUARE SHAPE - 5.0 to 5.9
{display all stones within 5.0 to 5.9 which is Square}

to create multiple sql statement then displaying them in a loop seems to be a messy task, could someone please give me a hint on how to write a good sql statement to sort this table out in an organized way.

Thank you.

have a look at the order by clause of sql

id expectg to see something like
select ID, Size, Shape, from <mytable> order by Shape,Size

the rest of it is down to your presentation layer, incidentally what are you suing for your presentation layer (Access, VB, C++, MFC/OWL, Delphi?)
you could probably do it via a sub select wihtin MySQL, but that wold be messy

incidentally Id expect the Shape column to be a reference to another table and be somehting like ShapeTypeID

perhaps a quick refresher on table desing would be in order

tblShapeTypes
ShapeTypeID: ShapeDesc
0: Unknown
1: Round
2: Ovoid
3: Square
4: BuckyBall

tblGems
GemID,Size,ShapeTypeID
1 1.1 1
2 1.2 3
3 1.3 1
4 2 1
5 2.1 3
.....
11 5 1

thank you for your reply,

so far what i find is the most difficult is not the shape part, i got that handle in a different table, using foreign key and all, however selecting the range from 1.0 to 1.9 then select another 2.0 to 2.9.....this is just very messy. any suggestions on this?

one technique could be to create a grouping table

tblSizeReportingGroup
SizeGrpID SizeGrpSeq SizeGrpDesc MinSize MaxSize
1 1 "size range 1 to 1.9" 1.0 1.9
2 3 "size range 3.0 to 3.9" 3.0 3.9
2 2 "size range 2.0 to 2.9" 2.0 2.9
......
10 5 "size range 5.0" to 5.9 5.0 5.9
the sequence (SizeGrpSeq) may not be neccessary if you know that there are never going to be any more size ranges, and you put the ranges in numerical order. Id put it in so that I could add a new range to the table and adjust the sequence accordingly
the description is optional

add a column to the tblGems which references the SizeGrpID
or do a function or SQL query which finds the size grp & sequence
adding a column to the tblGems isnt neccesarily smart - it means you have a maintenance task if the requirement changes (say the users want to spolit the groups 1.0 to 1.4, 1.5 to 1.9) - you have to update the tblGems to reflect the new groupings personally Id use a fucntion or part of a where clause to identify the group

you don't actually need the table
you could issue a series of SQL statements that extract the groupings
you could encode the groupoing using a lookup with the SQL (nasty solution DONT go down that route)
you could handle this within the presentation layer

actually the separate lookup table is a good solution, provided you don't do something boneheaded like store the FK in your main table

do a simple join using a range conditionthen you can completely re-assign size ranges at will, and the query will still work

thanks guys, i will give it a try, will keep you guys inform on the result.
thank you very much.