Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

blodzoom
Starting Member

USA
28 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
38200 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
30421 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
28 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
28 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
28 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  
 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.03 seconds. Powered By: Snitz Forums 2000