Select top row and second top row from DIM table

Question

Select top row and second top row from DIM table

duggal.priyanka06

Say Hey Kid

Points: 684
More actions
September 26, 2011 at 3:37 am

#248372

I have a dim table (publish.dim_strategy). The strategies here get expired. I want to select the top row from the expired strategies.i.e. the one marked with 2011-08-05 04:10:16.000 in the attached image.
(Select Strategy_id,efct_start_dt,efct_end_dt From publish.dim_strategy
Where Strategy_ID in
(
Select Strategy_ID
From publish.dim_strategy
Group by Strategy_ID having COUNT(Strategy_ID)>1
)
and efct_end_dt is null )
This query gives me the row which has been created after expiration.Want the top row (i.e. highest efct_end_dt) from the expired strategies

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

duggal.priyanka06 (9/26/2011)
I have a dim table (publish.dim_strategy). The strategies here get expired. I want to select the top row from the expired strategies.i.e. the one marked with 2011-08-05 04:10:16.000 in the attached image.
(Select Strategy_id,efct_start_dt,efct_end_dt From publish.dim_strategy
Where Strategy_ID in
(
Select Strategy_ID
From publish.dim_strategy
Group by Strategy_ID having COUNT(Strategy_ID)>1
)
and efct_end_dt is null )
This query gives me the row which has been created after expiration.Want the top row (i.e. highest efct_end_dt) from the expired strategies

Good morning and welcome to SSC!

It seems that your DDL script seems to have become detached from your post, or perhaps you were unaware of the benefits of providing one.

When you have time, please read this article[/url] about the best way to provide us with working sample data. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.

Thanks!

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

This should do:

SELECT TOP(1) *

FROM publish.dim_strategy

WHERE strategy_id = 'gt-537791'

ORDER BY efct_end_dt DESC

-- Gianluca Sartori

duggal.priyanka06 Say Hey Kid Points: 684 More actions · Answer 3

actually there are a number of strategy_id in the table which are getting created and expired.My below code worked:-

(Select Strategy_id,efct_start_dt,efct_end_dt From publish.dim_strategy

Where strategy_key in

(

Select max(Strategy_key) strategy_key

From publish.dim_strategy

where efct_end_dt is not null

Group by Strategy_ID having COUNT(Strategy_ID)>1

)

Because it is creating a new strategy_key whenever a record is getting expired.any other suggestions are welcome:)

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

You code works only if strategy key is ever-increasing.

This should work regardless of that:

DECLARE @dim_strategy TABLE (

Strategy_id char(9),

efct_start_dt datetime,

efct_end_dt datetime,

strategy_key int

)

INSERT INTO @dim_strategy VALUES ('GT-537791', '2011-01-21 03:11:27.000', '2011-07-02 02:53:57.533', 244741)

INSERT INTO @dim_strategy VALUES ('GT-537791', '2011-08-05 04:10:16.000', NULL, 304501)

INSERT INTO @dim_strategy VALUES ('GT-537791', '2011-07-07 15:09:30.000', '2011-01-19 23:46:19.427', 213502)

INSERT INTO @dim_strategy VALUES ('GT-537791', '2011-08-05 00:35:55.000', '2011-08-05 04:10:16.000', 304162)

INSERT INTO @dim_strategy VALUES ('GT-537791', '2011-07-02 06:49:12.000', '2011-08-05 00:35:55.000', 287020)

SELECT Strategy_id

, efct_start_dt

, efct_end_dt

FROM

@dim_strategy

WHERE strategy_key = (

SELECT strategy_key

FROM (

SELECT TOP(1) Strategy_key, cnt = COUNT(*) OVER(PARTITION BY strategy_ID)

FROM @dim_strategy

WHERE efct_end_dt IS NOT NULL

ORDER BY efct_end_dt DESC

) AS subqry

WHERE cnt > 1

)

-- Gianluca Sartori

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Thanks Gian for the wonderful set-up.

SELECT *

FROM (

SELECT *

, cnt = COUNT(*) OVER(PARTITION BY strategy_ID)

, RN = ROW_NUMBER() OVER(PARTITION BY strategy_ID ORDER BY efct_start_dt DESC)

FROM @dim_strategy

) AS subqry

WHERE cnt > 1 AND RN <= 2

Would this help you?

duggal.priyanka06 Say Hey Kid Points: 684 More actions · Answer 6

Thanks all for the replies:)

I just modified the above query a bit so that it gives me the row with largest efct_end_dt for a group of strategy_id's

SELECT efct_start_dt,efct_end_dt,*

FROM (

SELECT *

, cnt = COUNT(*) OVER(PARTITION BY strategy_ID)

, RN = ROW_NUMBER() OVER(PARTITION BY strategy_ID ORDER BY (case WHEN EFCT_END_DT IS NULL THEN '2000-01-01 02:53:59.000' ELSE EFCT_END_DT END) DESC)

FROM publish.dim_strategy

) AS subqry

WHERE cnt > 1 AND

RN = 1

and efct_end_dt is not null

In the order by I set Null value to a minimum value for date because efct end dt is null for active strategies.It works perfectly fine now..

Thomas Abraham SSChampion Points: 10761 More actions · Answer 7

Cadavre (9/26/2011)
read this article[/url] about the best way to provide us with working sample data. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks!

Now that's what I call a warm welcome - while still getting the new person in line with the program. Nicely done, including the delicious dose of sarcasm. Bravo!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn