how to use top 1 in join statement

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 2008 Forums

Transact-SQL (2008)

how to use top 1 in join statement

Author

Topic

cjcclee
Starting Member

33 Posts

Posted - 2011-06-08 : 17:43:41

I have a table, as following: each id can have different insurance on different effecive date. for given date, for example '2011-05-31',
how can I get the each id's insurance on '2011-05-31'. the following table result should be:
id insurance
1 CCC
2 BBB

----------------------------------------

id effective date insurance
1 2009-05-01 AAA
1 2010-05-02 BBB
1 2011-01-01 CCC
2 2008-01-01 AAA
2 2009-01-01 CCC
2 2011-01-01 BBB
2 2012-01-01 DDD

Thank you for your help!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-08 : 18:29:51

do you want insurances in the same year, same year and less than 2011-05-31 or what?
try this


declare @dateParm datetime
SET @dateParm = '2011-05-31'

declare @insur table(id int, effectivedate datetime, insurance nvarchar(255))
INSERT INTO @insur
SELECT 1, '2009-05-01', 'AAA'
UNION
SELECT 1, '2010-05-02', 'BBB'
UNION
SELECT 1, '2011-01-01', 'CCC'
UNION
SELECT 2, '2008-01-01', 'AAA'
UNION
SELECT 2, '2009-01-01', 'CCC'
UNION
SELECT 2, '2011-01-01', 'BBB'
UNION
SELECT 2, '2012-01-01', 'DDD'


SELECT * 
  FROM @insur
  WHERE YEAR(effectivedate) = YEAR(@dateParm)

OR

SELECT id,  insurance, ROW_NUMBER() OVER(PARTITION BY insurance ORDER BY effectivedate DESC)
  FROM @insur
  WHERE YEAR(effectivedate) = YEAR(@dateParm)

If you don't have the passion to help people, you have no passion

cjcclee
Starting Member

33 Posts

Posted - 2011-06-08 : 21:37:05

Sorry for the confusion.Thanks for your help.

I need find most recent insurance for given date. when new insurance has new effective date, the old one is expired. the effective date need eariler than given date, also should be most recent. datediff(effectivedate,@givendate)>0 and also date = select top 1 date from table a order by
datediff(effective,@givendate)

how I can get each id the most recent effective date?

quote:

Originally posted by yosiasz

do you want insurances in the same year, same year and less than 2011-05-31 or what?
try this


declare @dateParm datetime
SET @dateParm = '2011-05-31'

declare @insur table(id int, effectivedate datetime, insurance nvarchar(255))
INSERT INTO @insur
SELECT 1, '2009-05-01', 'AAA'
UNION
SELECT 1, '2010-05-02', 'BBB'
UNION
SELECT 1, '2011-01-01', 'CCC'
UNION
SELECT 2, '2008-01-01', 'AAA'
UNION
SELECT 2, '2009-01-01', 'CCC'
UNION
SELECT 2, '2011-01-01', 'BBB'
UNION
SELECT 2, '2012-01-01', 'DDD'


SELECT * 
  FROM @insur
  WHERE YEAR(effectivedate) = YEAR(@dateParm)

OR

SELECT id,  insurance, ROW_NUMBER() OVER(PARTITION BY insurance ORDER BY effectivedate DESC)
  FROM @insur
  WHERE YEAR(effectivedate) = YEAR(@dateParm)

If you don't have the passion to help people, you have no passion

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-08 : 21:43:04

or maybe you wanted this ?


SELECT *
from   
(
    select *, row_no = row_number() over (partition by id order by effective_date desc)
    from   yourtable
    where  effective_date <= '2011-05-31'
) t
where t.row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

cjcclee
Starting Member

33 Posts

Posted - 2011-06-09 : 13:31:24

Thanks so much for your help. It worked perfectly. Thanks again!

quote:
Originally posted by khtan

or maybe you wanted this ?

SELECT *
from
(
select *, row_no = row_number() over (partition by id order by effective_date desc)
from yourtable
where effective_date <= '2011-05-31'
) t
where t.row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources