Returning the earliest value - 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)

Returning the earliest value

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2011-10-26 : 12:06:08

Hi
I'm wondering if anyone can help.

I have a list of names with session dates against them.

What I'm trying to do is create a SQL query that for the below example that would only return the earliest line for each person.

RefNo Name Register Date
123456 Smith Tutorial 17/09/2011
123456 Smith Tutorial 18/09/2011
123456 Smith Tutorial 01/09/2011
123456 Smith Tutorial 08/09/2011
123456 Smith Tutorial 15/09/2011
234576 Jones Tutorial 02/09/2011
234576 Jones Tutorial 15/09/2011
234576 Jones Tutorial 16/09/2011

So what I want to do is have a list like this as my results

RefNo Name Register Date
123456 Smith Tutorial 01/09/2011
234576 Jones Tutorial 02/09/2011

I tried using TOP but this just returned the earliest record (which was the smith 01/09/2011 one) not the earliest record for each person.

Any advice appreciated.

Dan

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 12:51:46

SELECT * FROM myTable o WHERE EXISTS (SELECT * FROM myTable i WHERE o.RefNo = i.RefNo GROUP BY RefNo HAVING MAX(i.Date) = o.Date)

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-26 : 12:57:45

I am not sure what type of column your date field is. If it is a datetime then you can just use this query.


select *
from
(
slect row_number() over (partition by refno order by date asc) as rowid,* from
mytable
) a
where a.rowid = 1

But if it's a varchar field, I'd make sure I identified the format as DD/MM/YYYY to ensure proper sort so the format always converts properly.


select *
from
(
slect row_number() over (partition by refno order by convert(datetime,date,105) asc) as rowid,* from
mytable
) a
where a.rowid = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 13:26:42

was mine too complicated?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-26 : 13:45:29

Nah, that's the way I would've done it in SQL 2000, but I just figured the Rank functions in 2005+ are really helpful tools and it would benefit to show him how they can be used.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 13:47:43

..and where does performance play into this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-26 : 14:15:33

I do not know the scope of his data, or have enough information to know if any difference in performance would have a noticeable effect with what he was doing. Based on what he has illustrated, the below query would be the best performance for the results he has shown, even if it is not specifically what he asked, and maybe this is all he was looking for:


select RefNo, Name, Register, min(Date) as date
from
mytable
group by RefNo, Name, Register

The Row_number and other ranking functions in most scenarios perform well and give a ton of flexibility (i.e. if he didn't want the first date, but instead decided he wanted to see the second lowest date). I was simply illustrating an option. I hope you did not interpret as saying there is something wrong with your query.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 14:37:11

no, just a discussion about performance...options are always good

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-26 : 15:37:16

Sounds good! Also I noticed on your query you have it set to MAX date, I think it was just a typo and he wanted the Min date.


SELECT * FROM myTable o WHERE EXISTS (SELECT * FROM myTable i WHERE o.RefNo = i.RefNo GROUP BY RefNo HAVING MIN(i.Date) = o.Date)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

chipembele
Posting Yak Master

106 Posts

Posted - 2011-10-27 : 04:24:37

Thankyou for your responses. I'll have a go today.

chipembele
Posting Yak Master

106 Posts

Posted - 2011-10-31 : 08:39:49

Thankyou

In the end I used the ranking method as I was more familiar with that having used it on other tasks.

Regards
Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources