Order by Status Date in DESC - 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 2008 Forums

Transact-SQL (2008)

Order by Status Date in DESC

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2014-11-11 : 20:35:14

Hi,

I have got a Table with Each Product and Its Status Updated Date. Need to Order the Table with Recent Product and Its Status in Order.

TABLEA:
[PRODUCT] [STATUS] [DATE]
COKE Created 2014-11-01
COKE Received 2014-11-04
COKE Production 2014-11-06
COKE Retail 2014-11-10
PEPSI Created 2014-10-08
PEPSI Received 2014-10-30
PEPSI Production 2014-11-01
PEPSI Retail 2014-11-05
LIMCA Created 2014-11-04
LIMCA Received 2014-11-15
LIMCA Production 2014-11-17
LIMCA Retail 2014-11-20

The above table must be in Order with Recent Product Created Status and its Other status Followed. My Output Table Should be.

OUTPUT:
[PRODUCT] [STATUS] [DATE]
LIMCA Created 2014-11-04
LIMCA Received 2014-11-15
LIMCA Production 2014-11-17
LIMCA Retail 2014-11-20
COKE Created 2014-11-01
COKE Received 2014-11-04
COKE Production 2014-11-06
COKE Retail 2014-11-10
PEPSI Created 2014-10-08
PEPSI Received 2014-10-30
PEPSI Production 2014-11-01
PEPSI Retail 2014-11-05

In the Output Limca have been created Recently and its other status Follows. Please help me to built a Query to get the Recent Product Created Info 1st with its Following Status.

Regards,
Kalai

Regards,
Kalai

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-12 : 01:54:55

[code]
;with created as
(
select *, rn = dense_rank() over (order by [DATE] DESC)
from tbl
where STATUS = 'Created'
)
select t.*
from tbl t
inner join created c on t.PRODUCT = c.PRODUCT
order by c.rn, t.[DATE]
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources