MAX and Join without duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

MAX and Join without duplicates

Author

Topic

Ogriv
Starting Member

40 Posts

Posted - 2010-04-13 : 11:07:51

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.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-13 : 12:02:47

Try this:

Select A.VisitId, A.RoomID, A.Status, B.Mnemonic, B.Name,B.OrderDateTime
From AdmVisit a inner join OeOrders B
on A.VisitID = B.VisitID
and b.OrderDateTime in (Select Max(c.OrderDateTime) from OeOrders C where C.VisitID = B.VisitID)

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Ogriv
Starting Member

40 Posts

Posted - 2010-04-13 : 12:27:41

pk bohra, Your example works perfectly. thank you for your assistance. I am still trying to figure out your logic flow, but I will review your sample until I understand it.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-13 : 13:00:43

You are welcome.

nikswapnil
Starting Member

1 Post

Posted - 2010-04-15 : 02:18:31

Hi,
try this

select B.VisitID, B.RoomID, B.Status, A.Mnemonic, A.Name,A.OrderDateTime from AdmVisit B
inner join
(SELECT VisitID,MAX(OrderDateTime) AS OrderDateTime,Mnemonic,Name
FROM OeOrders WHERE Category = 'DIET'
Group By VisitID,Mnemonic,Name)A on B.VisitID=A.VisitID and
A.OrderDateTime in (Select Max(C.OrderDateTime) from OeOrders C where C.VisitID = B.VisitID)

Regards,
Swapnil Nikam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources