Max Statement - SQL Server Forums

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 Statement

Author

Topic

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 18:06:58

Having trouble with the Max statement. Can you assist?

Goal: Return one record for each of the individuals in the "Guest" table, along with a single record that represents the last time they stayed with us from the "Stay" table. If the guest stayed multiple times, I only want to see the data on the last stay.

My current query seems to return multiple stays. I need the uniqueID from the stay so I can extract additional information later. When I run the query below without the grouping on S.StayID, it does as I expect, however by having it in, it returns multiple rows for each guest, which is bad.

Select
S.UniqueID [GuestID],
S.StayID [StayID],
Max(S.Adate) [ArrivalDate]
From Guest G Inner Join Stay S
on G.UniqueID = S.UniqueID
Group by S.UniqueID, S.StayID

How can I get the unique GuestID, the last StayID with the most recent Arrival Date?

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 18:12:24

Why are you joining to Guest when you don't use it anywhere? It's not in the column list or in the group by.

Tara

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 18:13:27

This is a single step in a multi step process. To make it easy to explain, I thought I would strip away all of the other stuff. I do in fact use the info from the guest record.

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 18:15:01

Could you post the DDL for Stay and Guest as well as sample data for both using INSERT INTO statements and then the expected result using the sample data? That would definitely help us help you.

Tara

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-22 : 18:16:51

One thing that strikes me - you dont use anything from the Guest table anywhere... are you filtering because you have stays that arent guests ?

Try this -
Select
S.UniqueID [GuestID],
Max(S.StayID) [StayID],
Max(S.Adate) [ArrivalDate]
From Guest G Inner Join Stay S
on G.UniqueID = S.UniqueID
Group by S.UniqueID

But this does assume that you use an Identity col and all data (at least by guest) is entered chronologically

Graham

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-22 : 18:17:32

Damn you you folks type fast
G

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 18:21:37

The Guest table:
UniqueID:1234
Lastname: Jones
Firstname: Bob
Address :7 Star Drive

The Stay Table:
UniqueID: 1234
StayID: abcd
ADate: 6/1/2004
RmType: 1BDR

Linking between two tables is UniqueID. The StayID represents a unique record within the Stay table.

There could be multiple stays for a single guest like below:

1234 abcd 6/1/2003 1BDR
1234 abce 8/1/2004 2BDR

The results that I am looking for would be:
1234 abce 8/1/2004 2BDR

Only interested in the last stay. Does that help?

MBeal

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 18:23:05

Graham: If I put a max on the stayID and the Adate, is it possible that I could get a mix result? Meaning the maximum stayID may not be the same stayID associated with the Max Adate?

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 18:26:52

Next time, please post in the form of CREATE TABLE and INSERT INTO statements to make this easier on us. Here ya go:


SET NOCOUNT ON

CREATE TABLE Stay
(
	UniqueID INT,
	StayID CHAR(4),
	ADate DATETIME,
	RmType VARCHAR(5)
)

INSERT INTO Stay VALUES(1234, 'abcd', '6/1/2003', '1BDR')
INSERT INTO Stay VALUES(1234, 'abce', '8/1/2004', '2BDR')

SELECT s1.UniqueID, s1.StayID, s1.ADate, s1.RmType
FROM Stay s1
INNER JOIN 
		(
			SELECT UniqueID, MAX(ADate) AS ADate
			FROM Stay
			GROUP BY UniqueID
		) s2
ON s1.UniqueID = s2.UniqueID AND s1.ADate = s2.ADate

DROP TABLE Stay

Tara

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-22 : 18:35:38

quote:
Originally posted by MBeal

Graham: If I put a max on the stayID and the Adate, is it possible that I could get a mix result? Meaning the maximum stayID may not be the same stayID associated with the Max Adate?

MBeal

Yes, if my assumptions were not true

However, looks like Tara has the more correct solution (as ever!)
G

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 18:41:09

Tara,

I am still getting multiples. I think the problem is that the Adate is not unique enough. I have 39,089 unique customers in the table that I am working with, however when I run the query, I return 40,736 rows of data. I'm still missing something... ugh! I appreciate your help, and sorry about not putting it all in the right format earlier.

Michael

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 18:42:47

If you could post some more data which represents the problem that you are having, then I could see what needs to be changed in the query.

Tara

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 18:47:25

SET NOCOUNT ON

CREATE TABLE Stay
(
UniqueID INT,
StayID CHAR(4),
ADate DATETIME,
RmType VARCHAR(5)
)

INSERT INTO Stay VALUES(1234, '0001', '8/1/2004', '1BDR')
INSERT INTO Stay VALUES(1234, '0008', '6/1/2000', '2BDR')

Notice that the Maximum StayID is "0008" while the maximum ADate is "8/1/2004". I need the results to be as follows:
1234, 0001, 8/1/2004, 1BDR

I'm not certain why I am getting multiple results. I wish I knew for certain, I'm looking into that as we speak. Does this help?

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 18:51:21

The code that I posted does give you what you need. Try this in the pubs database and take a look at the results:


USE pubs
GO

SET NOCOUNT ON

CREATE TABLE Stay
(
	UniqueID INT,
	StayID CHAR(4),
	ADate DATETIME,
	RmType VARCHAR(5)
)

INSERT INTO Stay VALUES(1234, '0001', '8/1/2004', '1BDR')
INSERT INTO Stay VALUES(1234, '0008', '6/1/2000', '2BDR')

SELECT s1.UniqueID, s1.StayID, s1.ADate, s1.RmType
FROM Stay s1
INNER JOIN 
		(
			SELECT UniqueID, MAX(ADate) AS ADate
			FROM Stay
			GROUP BY UniqueID
		) s2
ON s1.UniqueID = s2.UniqueID AND s1.ADate = s2.ADate

DROP TABLE Stay

You don't care about the maximum StayID, right? The grouping should only occur on UniqueID.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 19:00:30

Is it possible you have orphaned data in the Stay table? Do you have a foreign key constraint on UniqueID?

Run this:

SELECT COUNT(*)
FROM Stay
WHERE UniqueID NOT IN (SELECT UniqueID FROM Guests)

Do you get 0?

Tara

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 19:08:46

There must be something I am missing...

Here is the actual query... it is a little more complicated than the original. Doing it in pubs is not going to help me, I am afraid. So here it is...

SELECT s1.S_IRecid, s1.S_StayID, s1.S_ADate
FROM Stay s1
INNER JOIN
(
SELECT S_Irecid, MAX(S_ADate) AS ADate
FROM V1Data..Stay
GROUP BY S_Irecid
) s2
ON s1.S_Irecid = s2.S_Irecid AND s1.S_ADate = s2.ADate
INNER JOIN RollingEbird R
ON s1.S_Irecid = R.I_Recid

"RollingEbird" contains 39,089 unique guest records -- just the I_Recid. The stay table contains 129,270 stay records. When I run this query, I get a result of 40,736? It should be 39,089. Somehow, the link between the S1 and S2 it is coming up with duplicates. Perhaps the guest made and cancelled the reservation with the same arrival date? I don't know for certain.

MBeal

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 19:10:58

There are 158 orphaned records. That is interesting, thank you for pointing that one out. I can't see how this would make up 1,647 additional records though?

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 19:15:38

Why are you joining to RollingEbird? You aren't using it in the query. You only join to it if you need a column from it for the column list, where clause, group by, etc... You aren't using RollingEbird except in the join, so remove it. Or is the query still different than what you actually have? Same arrival dates would only cause a problem if the time portion were the same as well.

Tara

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 19:18:21

I am trying to verify that I am getting just the 39K that I need. I tried it a different way and I found duplicates -- I am looking into it right now... any ideas?

Select R.I_Recid,
A.S_Irecid,
A.S_StayID,
A.S_Adate
From RSSI..RollingEbird R inner join

(SELECT s1.S_IRecid, s1.S_StayID, s1.S_ADate
FROM V1Data..Stay s1
INNER JOIN
(
SELECT S_Irecid, MAX(S_ADate) AS ADate
FROM V1Data..Stay
GROUP BY S_Irecid
) s2
ON s1.S_Irecid = s2.S_Irecid AND s1.S_ADate = s2.ADate ) A
on R.I_Recid = A.S_IRecid

Order by R.I_Recid

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 19:20:13

Do you get any rows back from this query?:

SELECT S_IRecid, S_ADate, COUNT(*)
FROM Stay s1
GROUP BY S_IRecid, S_ADate
HAVING COUNT(*) > 1

If you do, then you've got multiple UniqueIDs with the same ADate, exactly the same including time portion.

Tara

MBeal
Posting Yak Master

110 Posts

Posted - 2004-06-22 : 19:21:20

The duplicates appear to be when customers had more than one reservation for the same date. Again, there will be more than one "stayID" because there are two stays, both with the exact same stay date. I want to get just one of those stayID's and the maximum arrival date (which would be the same in this case. That's what I was trying to say from the beginning. It's hard to do this for you since you don't completely understand my data -- sorry. How can I get just one of those StayID's?

MBeal

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources