Exotic SQL Question

Table 1:
id1 name
10 - Monday
20 - Tuesday
30 - Wed..
40 - etc

Table2:
id2 - name
5 - blue
25 - red
33 - yellow
77- gree

How do I use SQL to join the tables so that the "id1" columns join on
the largest "id2" value smaller than "id1"

Result:
Monday - blue
Tuesday - blue
Wed - red
etc

Thanks
JFO*UNTESTED*

select ...
from table1 join table2 on table1.id1 > table2.id2
and not exists(select 1 from table2 t2 where table1.id1 > t2.id2 and
t2.id2>table2.id2)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.

CREATE TABLE Colors
(color_nbr INTEGER NOT NULL PRIMARY KEY,
color_name CHAR(10) NOT NULL);

CREATE TABLE Weekdays
(zeller_nbr INTEGER NOT NULL PRIMARY KEY,
weekday_name CHAR(10) NOT NULL);

>> How do I use SQL to join the tables so that the color_nbr columns join on the largest zeller_nbr value smaller than color_nbr <<

SELECT C1.color_nbr, W1.zeller_nbr, C1.color_name, W1.weekday_name
FROM Colors AS C1, Weekdays AS W1
WHERE zeller_number
= (SELECT MAX(zeller_nbr)
FROM Weekdays AS W2
WHERE W2.zeller_nbr < C1.color_nbr
AND W2.zeller_nbr = W1.zeller_nbr)

This sounds like the old joke about the teacher and the students:

teacher: "Mary, what is 6 times 7?"
student: "Red?"
teacher: "Billy, what is 6 times 7?"
student: "Thursday!"
teacher: "Johnny, what is 6 times 7?"
student: "42!"
teacher: "Johnny, tell the class how you go that answer."
student: "I divided Red by Thursday!"

untested.|||Thanks a lot, dude! will try that solution. The thing is, the two
tables that we want to join are quite big - one is 1.7 million entries,
another is 3.2 million. (working on a thesis in Finance)
I have been introduced to SQL less than a week ago, so can be quite a
lamer here. These are the DDL's for 2 tables
CREATE TABLE "public"."orders" (
"order_id" SERIAL,
"seq_id" INTEGER,
"datetime" TIMESTAMP(0) WITHOUT TIME ZONE,
"by_sell" INTEGER,
"price" DOUBLE PRECISION,
"volume" INTEGER,
CONSTRAINT "orders_pkey" PRIMARY KEY("order_id")
) WITHOUT OIDS;

Second:
CREATE TABLE "public"."transactions" (
"transaction_id" INTEGER NOT NULL,
"date" DATE,
"company_name" VARCHAR,
"trade_type" VARCHAR,
"volume" INTEGER,
"price" DOUBLE PRECISION,
"time" TIMESTAMP(0) WITHOUT TIME ZONE,
CONSTRAINT "transactions_pkey" PRIMARY KEY("transaction_id")
) WITHOUT OIDS;

Using posgres 8.1 Will wait for a friend of mine who is helping us
(the author of the question) to try the solution

But basically, we want to join orders with transactions, with field
price in transactions looking up PRECEEDING price in orders|||On 11 May 2006 10:20:06 -0700, --CELKO-- wrote:

>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, data types, etc. in
>your schema are.
>CREATE TABLE Colors
>(color_nbr INTEGER NOT NULL PRIMARY KEY,
> color_name CHAR(10) NOT NULL);
>CREATE TABLE Weekdays
>(zeller_nbr INTEGER NOT NULL PRIMARY KEY,
> weekday_name CHAR(10) NOT NULL);

Hi Joe,

Are you seriously trying to teel me that after spending months
researching the data, this is your schema?

Both color names and weekday names can vary in length. Clearly, char is
a bad choice of datatype for those columns - use varchar (or character
varying, if you prefer ANSI-speak).

The longest English weekday name is wednesday, which has 9 characters.
Do you really want to end with a day like "wednessday" in your table?
Because if you allow 10 characters, you will!

Colornames can far exceed the lousy 10 characters you allow. Just take a
look on Wikipedia: http://en.wikipedia.org/wiki/List_of_colors. For
instance "International Klein Blue" has 24 characters. I didn't count
the length of all names on that page, nor will I assume that it's a
complete list. I'd allow at least 30 characters. Maybe even 50. (Oh
wait, I'll change it to 49 or 51 to prevent people thinking I'm using
the Access defaults...)

Oh, and please stop using silly column names such as zeller_nbr. We
don't design database schemas to show off how many algorithm names we
remember, we should strive for maintainability. Anything wrong with
weekday_nbr?

>teacher: "Mary, what is 6 times 7?"
>student: "Red?"
>teacher: "Billy, what is 6 times 7?"
>student: "Thursday!"
>teacher: "Johnny, what is 6 times 7?"
>student: "42!"
>teacher: "Johnny, tell the class how you go that answer."
>student: "I divided Red by Thursday!"

You're obviously wrong (again). Johnny built a supercomputer and let it
to calculate for a few billion years.

--
Hugo Kornelis, SQL Server MVP|||Supercomputer......that will do us nicely...Hugo, may I trouble you
for Johnny's e-mail?|||On 11 May 2006 14:24:18 -0700, alunev@.gmail.com wrote:

>Supercomputer......that will do us nicely...Hugo, may I trouble you
>for Johnny's e-mail?

Hi Alunev,

I don't know who "Johnny" is - but even if I did, I wouldn''t pass on
his e-mail to anybody without his own consent.

If you refer to Joe (not John!!) Celko, I believe that the email in his
Usenet headers is his real e-mail address. If you're refering to someone
else, I suggest that you ask him or her instead of me.

--
Hugo Kornelis, SQL Server MVP|||Johnny who has built a supercomputer...mighty serious, you dudes|||Hi There,

You can use somewhat Celko Suggested ,
SELECT C1.color_nbr, W1.zeller_nbr, C1.color_name, W1.weekday_name
FROM Colors AS C1, Weekdays AS W1
WHERE zeller_number
= (SELECT MAX(zeller_nbr)
FROM Weekdays AS W2
WHERE W2.zeller_nbr < C1.color_nbr
AND W2.zeller_nbr = W1.zeller_nbr)

You my try this

Select * From
DayTable AA , ColorTable BB
Where AA.id1>BB.id1
And BB.id1 = ( Select Max(id1) From ColorTable BB1 where BB1.id1 <
AA.id1)

With Warm regards
Jatinder Singh