Select assistance - 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

General SQL Server Forums

New to SQL Server Programming

Select assistance

Author

Topic

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-04-05 : 08:11:07

Hi,

I have a column with a status of and Order in it ("order placed","order pending","order delivered")

In this table is all the history of Orders, therfore it shows 3 line records for each order in the three different status's.(Order placed, pending, delivered)

Example:
Order#,orderplaced,status
a,01/01,"placed"
a,01/01,"delivered"
b,02/01,"pending"
b,02/01,"placed"

I only want to select the the record with the latest status, therfore give that column a hirarcy:

example of result:
Order#,orderplaced,status
a,01/01,"delivered"
b,02/01,"pending"

Please assist, as I am sure I am over-looking an easy way to do this.

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-05 : 08:16:44

what defines the latest status?... you don't have any indicator in your table...

Corey

I Has Returned!!

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-04-05 : 08:19:21

No, the hirarcy is

"Delivered" over "Pending" and "Placed"
"Pending" over "Placed"

Hope this helps?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-05 : 08:22:08

If you assume that status itself defines the 'latest' then you can do:


declare @t table (
	o varchar(10),
	p datetime,
	s varchar(20)
)
Insert Into @t Select 'a','01/01/2011','placed'
Insert Into @t Select 'a','01/01/2011','delivered'
Insert Into @t Select 'b','02/01/2011','pending'
Insert Into @t Select 'b','02/01/2011','placed'


Select 
	o, 
	p, 
	case max(case s when 'placed' then 1 when 'pending' then 2 when 'delivered' then 3 else null end)
		when 1 then 'placed'
		when 2 then 'pending'
		when 3 then 'delivered'
		else 'no idea' end
From @t
Group By o, p

-- Or you can do it this way 

Select * 
From 
	(
	Select 
		*,
		r = Row_Number() Over (Partition By o Order By case s when 'placed' then 1 when 'pending' then 2 when 'delivered' then 3 else 0 end desc) 
	From @t
	) A
Where r = 1

Corey

I Has Returned!!

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2011-04-05 : 08:23:53

THANKS Corey!!!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-05 : 08:25:26

create table #T
( Orders nvarchar(1),
orderplaced datetime,
States Nvarchar(25)
)

insert into #T
select
'a',getdate(),'placed' union all
select
'a',getdate(),'delivered' union all
select
'b',getdate(),'pending' union all
select
'b',getdate(),'placed'

select * from #T
select * from (select *,row_no=row_number() over(partition by Orders Order by States) from #T)t where row_no=1

Raghu' S

McDebil
Starting Member

23 Posts

Posted - 2011-04-05 : 09:52:17

Without any temp table or table variable:

SELECT [Order#],
[orderplaced],
[status]
FROM [dbo].[Orders]
WHERE [Order#] + [status] IN (
SELECT [Order#] + CASE ( MAX(CASE [status]
WHEN 'order placed' THEN 1
WHEN 'order pending' THEN 2
WHEN 'order delivered' THEN 3
END) )
WHEN 1 THEN 'order placed'
WHEN 2 THEN 'order pending'
WHEN 3 THEN 'order delivered'
END
FROM [Orders]
GROUP BY [Order#] )

McDebil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources