October 13, 2009 at 8:38 pm
I'm new to SQL but a software developer for years. LINQ here I come 😉
I have a seemingly simple concept but for some reason its being a pain in the butt.
I'm trying to run a select query from multiple tables and return only the records based off the MAX value held in a seperate table.
Example: I query employees, and get there name and such and grab info from another table, then I want to only return the day of the highest sales that week. - or something like that.
CREATE TABLE CUSTOMER_ORDER
(
ROWID INT ,
ID INT ,
CUSTOMER_ID INT
)
CREATE TABLE DEMAND_SUPPLY_LINK
(
DEMAND_BASE_ID INT ,
SUPPLY_BASE_ID INT
)
CREATE TABLE OPERATION
(
WORKORDER_BASE_ID INT ,
SEQUENCE_NO INT
)
INSERT INTO CUSTOMER_ORDER VALUES (13, 2801,0001)
INSERT INTO CUSTOMER_ORDER VALUES (12, 2802,0002)
INSERT INTO DEMAND_SUPPLY_LINK VALUES (2801, 15)
INSERT INTO DEMAND_SUPPLY_LINK VALUES (2802, 16)
INSERT INTO OPERATION VALUES (15, 25)
INSERT INTO OPERATION VALUES (15, 115)
INSERT INTO OPERATION VALUES (15, 13)
INSERT INTO OPERATION VALUES (16, 87)
INSERT INTO OPERATION VALUES (16, 5)
INSERT INTO OPERATION VALUES (16, 659)
This returns everything:
SELECT CUSTOMER_ORDER.*, DEMAND_SUPPLY_LINK.*, OPERATION.*
FROM CUSTOMER_ORDER INNER JOIN
DEMAND_SUPPLY_LINK ON CUSTOMER_ORDER.ID = DEMAND_SUPPLY_LINK.DEMAND_BASE_ID INNER JOIN
OPERATION ON DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = OPERATION.WORKORDER_BASE_ID
This return the max value
SELECT WORKORDER_BASE_ID, MAX(SEQUENCE_NO) as maxseqnum
FROM OPERATION
GROUP BY WORKORDER_BASE_ID
order by workorder_base_id
Well the above was very simple but I'm trying to return this below. Only return the records with the highest SEQUENCE_NO
ROWID ID CUSTOMER_ID DEMAND_BASE_ID SUPPLY_BASE_ID WORKORDER_BASE_ID SEQUENCE_NO
132801 1 2801 15 15 115
122802 2 2802 16 16 659
October 13, 2009 at 11:58 pm
SELECT
CUSTOMER_ORDER.*,
DEMAND_SUPPLY_LINK.*,
OPERATION.*
FROM
CUSTOMER_ORDER
INNER JOIN
DEMAND_SUPPLY_LINK
ON CUSTOMER_ORDER.ID = DEMAND_SUPPLY_LINK.DEMAND_BASE_ID
INNER JOIN
(SELECT WORKORDER_BASE_ID,MAX(SEQUENCE_NO) as SEQUENCE_NO FROM OPERATION GROUP BY WORKORDER_BASE_ID)OPERATION
ON DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = OPERATION.WORKORDER_BASE_ID
..
October 14, 2009 at 12:07 am
Hi,
This is what I would do, I hope I understand your problem and that this helps you:
SELECTCO.ROWID,
CO.CUSTOMER_ID,
DSL.DEMAND_BASE_ID,
DSL.SUPPLY_BASE_ID,
OP.WORKORDER_BASE_ID,
MAX(OP.SEQUENCE_NO)
FROMCUSTOMER_ORDER CO INNER JOIN
DEMAND_SUPPLY_LINK DSL ON CO.ID = DSL.DEMAND_BASE_ID INNER JOIN
OPERATION OP ON DSL.SUPPLY_BASE_ID = OP.WORKORDER_BASE_ID
GROUP BYCO.ROWID,
CO.CUSTOMER_ID,
DSL.DEMAND_BASE_ID,
DSL.SUPPLY_BASE_ID,
OP.WORKORDER_BASE_ID
ORDER BYOP.WORKORDER_BASE_ID
October 14, 2009 at 7:08 am
WOW! you guys are good... I was'nt expecting such a quick response. Awesome... Thanks.
They both work like a charm. You have seriously saved my but with this. It was the last thing I needed to do to complete this project. Now I can sleep at night. :smooooth:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply