order by pounds and ounces

chaza

Can anyone suggest how I would go about sorting data that is in pounds and ounces?
I want to order by weight, the problem is the ounces go up to 16 so I can't store the data as decimal. Do I have two fields, check one then if multiple entries are found the check the next field?
How would you write this query?

bastien

convert the ounces to decimals

1 ounce = .0625 lbs (1/16)

store as a decimal...then when printing it out convert it back by splitting at the decimal and taking the decimal portion and multiplying by 16

hth

YappyDog

Well, you don't say where the data is coming from, so it is tough to know the best way to do this.

If the data is in a table, with a column for pounds and a column for ounces, you would just do an "ORDER BY pounds, ounces" to get things sorted correctly.

If the data is not in a table and you need to manipulate it in your program somehow, you can use a decimal place as long as you make sure the amounts under 10 ounces have a zero in front of them. That way 1 pound 11 ounces (1.11) will sort after 1 pound, 2 ounces (1.02).

Sxooter

create table measures (id serial, lbs int, oz int, name text);
insert into measures (lbs,oz,name) values (2,5,'cherry pie');
insert into measures (lbs,oz,name) values (1,12,'lutefisk');
insert into measures (lbs,oz,name) values (4,2,'ham hock');

select ,lbs16+oz as ounces from measures order by lbs*16+oz;
id | lbs | oz | name | ounces
----+-----+----+------------+----------
2 | 1 | 12 | lutefisk | 28
1 | 2 | 5 | cherry pie | 37
3 | 4 | 2 | ham hock | 66
(3 rows)

YappyDog

Sxooter,

Wouldn't ORDER BY lbs, oz do the same thing?

I don't see the need to do the conversion to ounces........

Sxooter

Yeah, it would do the same thing. It's likely that the cost of computing lbs*16+oz is about the same as ordering by two fields. just guessing.

It's certainly more easily indexed. I actually wrote my answer before reading any of yours, since I had an edit window open for a few minutes while I made sure it all worked.

chaza

thanks for all of your answers.
actually someone on another forum has just come up with a good way:

put pounds and ounces together as one data:

34 pounds 12 oz = 3412
33 pounds 05 oz = 3305

then just sort by number.