Greetings,
I am trying to compile data from two different tables. I want to locate the MAX datetime in the OeOrders table and then join those rows to the corrosponding row in the AdmVisits table. The VisitID is the matching field.
Return rows that meet the following criteria:
AdmVisits.Status = 'ADM IN'
OeOrders.Category = 'DIET'
With the tables below, I am trying to return the following:
VisitID RoomID Status Mnemonic Name OrderDateTime
12121212 2A013 ADM IN CHEM1 Chemical Lvl 1 2010-03-15 10:17:25
44444444 2C022 ADM IN CHEM1 Chemical Lvl 1 2010-03-15 09:22:25
77777777 2C071 ADM IN CHEM3 Chemical Lvl 3 2010-03-15 20:16:51
63636363 2A037 ADM IN CHEM8 Chemical Lvl 8 2010-03-15 13:33:35
Here are the tables:
CREATE TABLE AdmVisit
(
VisitID INT,
RoomID VARCHAR(5),
Status VARCHAR(6)
)
CREATE TABLE OeOrders
(
VisitID INT,
Mnemonic VARCHAR(5),
Name VARCHAR(30),
Category VARCHAR(5),
OrderDateTime DATETIME
)
INSERT INTO AdmVisit VALUES(12121212, '2A013', 'ADM IN')
INSERT INTO AdmVisit VALUES(44444444, '2C022', 'ADM IN')
INSERT INTO AdmVisit VALUES(33333444, '2B034', 'ADM IN')
INSERT INTO AdmVisit VALUES(77777777, '2C071', 'ADM IN')
INSERT INTO AdmVisit VALUES(63636363, '2A037', 'ADM IN')
INSERT INTO OeOrders VALUES(44444444, 'CHEM5', 'Chemical Lvl 5', 'DIET', '2010-03-15 09:10:15')
INSERT INTO OeOrders VALUES(63636363, 'CHEM3', 'Chemical Lvl 3', 'DIET', '2010-03-15 12:05:26')
INSERT INTO OeOrders VALUES(44444444, 'CHEM1', 'Chemical Lvl 1', 'DIET', '2010-03-15 09:22:25')
INSERT INTO OeOrders VALUES(77777777, 'CHEM3', 'Chemical Lvl 3', 'DIET', '2010-03-15 20:16:51')
INSERT INTO OeOrders VALUES(12121212, 'CHEM6', 'Chemical Lvl 6', 'DIET', '2010-03-15 03:13:35')
INSERT INTO OeOrders VALUES(63636363, 'CHEM8', 'Chemical Lvl 8', 'DIET', '2010-03-15 13:33:43')
INSERT INTO OeOrders VALUES(12121212, 'CHEM1', 'Chemical Lvl 1', 'DIET', '2010-03-15 10:17:25')
INSERT INTO OeOrders VALUES(44444444, 'CHEM5', 'Chemical Lvl 5', 'DIET', '2010-03-15 08:50:34')
Trying to get the initial results from OeOrders, I started with this:
SELECT VisitID,
MAX(OrderDateTime) AS OrderDateTime,
Mnemonic,
Name
FROM OeOrders
WHERE Category = 'DIET'
Group By VisitID,
Mnemonic,
Name
But it is returning duplicate VisitID values with different Mnemonics and Names.
I can't seem to wrap my head around the logic flow. If somebody would care to explain the process, I would greatly appreciate it.
MAX and Join without duplicates
Posted - 2010-04-13 : 11:07:51
