SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Is there a better way?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

blodzoom
Starting Member

USA
16 Posts

Posted - 08/20/2014 :  16:50:27  Show Profile  Reply with Quote
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

USA
37457 Posts

Posted - 08/20/2014 :  17:13:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/20/2014 :  17:26:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

blodzoom
Starting Member

USA
16 Posts

Posted - 08/20/2014 :  17:34:01  Show Profile  Reply with Quote
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#
Go to Top of Page

blodzoom
Starting Member

USA
16 Posts

Posted - 08/20/2014 :  17:40:18  Show Profile  Reply with Quote
swepeso, your response is beyond my knowledge but is very interesting to me. I haven't used cte before but it looks very useful.
Go to Top of Page

blodzoom
Starting Member

USA
16 Posts

Posted - 08/20/2014 :  18:03:48  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000