Not "Distinct", but similar filter...

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 2005 Forums

Transact-SQL (2005)

Not "Distinct", but similar filter...

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-06-28 : 14:12:34

I have a table used to track car reservations. One column is a reservation number column. I want to grab all of the records that are not marked as deleted. That would be easy enough, but if there are multiple records with the same confirmation number, I only need the most recent one.

Here is an example of the SQL I am using right now (generates a view):

SELECT TOP (100) PERCENT rc.confirmation, rc.reservation_id, 
rc.rate_history_id, rc.person_id, rc.book_date_time, 
rc.pickup_date_time, rc.return_date_time, rc.vehicle_id, 
rc.reservation_status_id
FROM dbo.reservation_car AS rc INNER JOIN 
dbo.rate_history_car AS rhc ON 
rc.rate_history_id = rhc.history_id 
WHERE (rc.IsDeleted = 0) 
ORDER BY rc.book_date_time DESC

All of the other columns can be fine if they are duplicates, but not the Confirmation column.

I am not sure what to do to get the result I am looking for (other than adding another column). Can anyone help me?

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-28 : 14:27:25

Try looking at some of the new 2005 functions (RANK(), ROW_NUMBER(), etc). This is untested, but I think will do what you want (I did SELECT * for sample purposes):

SELECT 
	*
FROM
	(
		SELECT 
			rc.confirmation, 
			rc.reservation_id, 
			rc.rate_history_id, 
			rc.person_id, rc.book_date_time, 
			rc.pickup_date_time, 
			rc.return_date_time, 
			rc.vehicle_id, 
			rc.reservation_status_id,
			ROW_NUMBER() OVER (PARTITION BY rc.reservation_id, rc.confirmation ORDER BY rc.book_date_time) AS RowNumber
		FROM 
			dbo.reservation_car AS rc 
		INNER JOIN 
			dbo.rate_history_car AS rhc 
			ON rc.rate_history_id = rhc.history_id 
		WHERE 
			rc.IsDeleted = 0
	) t
WHERE
	RowNumber = 1

EDIT: Missed the DESC and Brain issue today.. Forgot to wrap the the whole thing.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-28 : 14:38:18

don't use order by in a view. use order by in the query that targets the view.

if you do this, you aren't guaranteed to get the results back ordered. optimizer reserves the right to ignore order by in a view definition.

elsasoft.org

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-06-29 : 08:27:26

Lamprey - Excellent! That is exactly what I was looking for. Thank you so much!

Jezemine - Thank you for that suggestion. I was not aware of that.

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-06-29 : 09:00:11

(This post is meant mostly for any future readers as an FYI.)

Although this worked extremely well in the query editor and it returned the results I expected, I received an error when applying this change to the view designer. Although I was still receiving the results I wanted, I Googled the error. Apparently, the View Designer does not support a whole lot of things. One of them is the OVER statement. That is pretty disappointing.

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources