Distinct Max Group by - 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 2005 Forums

Transact-SQL (2005)

Distinct Max Group by

Author

Topic

jimsurf
Starting Member

18 Posts

Posted - 2009-07-09 : 13:26:13

I have an asset table with property information on it, and a joined table for listings for the property. There can be zero to many listings for each property. Each Listing has a bit column called IsConfirmed, that is initially set to zero.

A number of things can change any of the IsConfirmed column to a 1.

I am trying to write a query to select the most recent listing for each property, but only if the most recent listing has an IsConfirmed value of zero.

There may be multiple listings for a property with a zero value, but I don't care unless it is the most recently entered listing.

I can write a query to join the tables, but doing so will return every listing.

How do I write the query to select the most recent listing but only if the IsConfirmed value = 0.

Asset Table:
PKID: AssetNumber
Address
City
State
etc...

AssetListing
PKID: AssetListingID
FKID: AssetNumber
DateCreated
IsConfirmed
etc...

Something like this, how to I say "Only look at the most recent listing for each asset"

SELECT DISTINCT A.AssetNumber
FROM Asset A
INNER JOIN AssetListing AL ON A.AssetNumber = AL.AssetNumber
WHERE IsConfirmed = 0

Some Kind of group by or something???

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-09 : 13:34:33

[code]SELECT *
FROM Asset A
-- if want all assets LEFT JOIN here
JOIN
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY AL.AssetNumber ORDER BY AL.DateCreated DESC) As RowNo
FROM AssetListing AL
WHERE AL.IsConfirmed = 0
) D
ON A.AssetNumber = D.AssetNumber
AND D.RowNo = 1
[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-09 : 13:36:40

SELECT
t1.AssetNumber
FROM
Asset B
INNER JOIN
(

select distinct A.AssetNumber,datecreated
,[Rank] = RANK() OVER(partition by AssetNumber order by datecreated desc)
from Asset A
) t1

ON
t1.AssetNumber = b.AssetNumber
and t1.DateCreated = b.DateCreated

WHERE t1.Rank = 1 and t1.isconfirmed = 0

Jim

jimsurf
Starting Member

18 Posts

Posted - 2009-07-09 : 13:50:51

Hey Jim,
Your query is not querying the AssetListing table at all.

jimsurf
Starting Member

18 Posts

Posted - 2009-07-09 : 13:56:21

I am a little confused also, If the inner query contains a where clause, then it will grab all records where the condition is true, and exclude all other records. If the most recent record does not meet the where clause condition, it will not be returned. I would get some other record from the child table??

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-09 : 14:02:38

The inner query (with the Rank() clause) will just rank the assetnumbers and datecreated
AssetNumber Datecreated Rank
1 07/01/2009 1
1 06/01/2009 2
1 05/01/2009 3
etc.

You say you want the most recent listing, but only if isconfirmed = 0,
which is what the WHERE clause does.

SELECT
B.*
FROM
Asset B
INNER JOIN
(

select distinct A.AssetNumber,datecreated
,[Rank] = RANK() OVER(partition by AssetNumber order by datecreated desc)
from AssetListing A
) t1

ON
t1.AssetNumber = b.AssetNumber
and t1.DateCreated = b.DateCreated

WHERE t1.Rank = 1 and t1.isconfirmed = 0

jimsurf
Starting Member

18 Posts

Posted - 2009-07-09 : 15:44:06

Perfect, thanks for the reply. That did the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources