Is there a better way? - 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 2012 Forums

Transact-SQL (2012)

Is there a better way?

Author

Topic

blodzoom
Starting Member

28 Posts

Posted - 2014-08-20 : 16:50:27

I have a table that is approx 2 mil lines and slowly growing. For the sake of this discussion, let's say it looks like this:
document# (not unique)
date1
date2
date3
Group#

I'm trying to get the most recent entry for each unique document# but if date1 is the same, I look at date2, date3 and then group#

I currently have something that does this:

SElECT document, date1, max(date2)
FROM
table1 LEFT JOIN
(
SELECT max(date1),document#
FROM table1
group by document#
)
GROUP BY
....

So it has ~4 Left joined selects on a single table and it seems silly but it worked on a smaller data set, now I'm trying to apply the same thing to a bigger chunk of the data and it times out.

It doesn't seem like it should be hard. I hope I'm just making it hard and somebody can simplify it for me.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-20 : 17:13:57

I'm not following your post. Could you post sample data/etc like is shown here?: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-20 : 17:26:02

with cte as select *, row_number() over (partition by [document#] order by date1 desc, date2 desc, date3 desc, [group#] desc) as rn from dbo.table1)
select * from cte where rn = 1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

blodzoom
Starting Member

28 Posts

Posted - 2014-08-20 : 17:34:01

I'm not a member at that site so I can't view the link.

My partial code is probably confusing, so to simplify, forget that part. I'll try to ask in a simpler way, and I'm really just looking for english or pseudo-code, not trying to get anyone to write this for me.

Given the table:
Document# (not a PK)
Date1
Date2
Date3
Group#

What is the best way to get to the most recent entry for each distinct document#. It is possible that there could be multiple entries for a document# on the same date1, so in that case, look at date2. It is also possible that date2 could be the same, so look at date3. If all 3 dates are the same, look at group#

blodzoom
Starting Member

28 Posts

Posted - 2014-08-20 : 17:40:18

swepeso, your response is beyond my knowledge but is very interesting to me. I haven't used cte before but it looks very useful.

blodzoom
Starting Member

28 Posts

Posted - 2014-08-20 : 18:03:48

Swepeso,

I already got it working and it takes about a minute and a half compared to the eternity that it took before. I can't say that I fully understand it yet but I'm pretty sure I can apply this elsewhere once I do.

Huge thanks, friend.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources