October 16, 2009 at 7:36 am
I have a table with millions of order lines
The lines can be in one of 6 different status's
I need to return top 1 orderID for each status. I have
no idea how to do this. can anyone help?
Below is sample data:
create table #orderline
(
orderid int,
ordernumber varchar(10),
orderstatus varchar(1)
)
insert into #orderline
values(1,'order1','A')
insert into #orderline
values(2,'order2','A')
insert into #orderline
values(3,'order3','B')
insert into #orderline
values(4,'order4','B')
insert into #orderline
values(5,'order5','B')
insert into #orderline
values(6,'order6','C')
insert into #orderline
values(7,'order7','C')
insert into #orderline
values(8,'order8','C')
insert into #orderline
values(9,'order9','D')
insert into #orderline
values(10,'order10','D')
Thanks all
October 16, 2009 at 7:42 am
serious apologies for posting this question. was making it much more complicated that it should be.
its
select max(OrderID),OrderStatus from #orderline
group by OrderStatus
I was trying joins and subqueries and a host of other things. not thinking straight today.apologies.
October 16, 2009 at 7:43 am
What do you mean Top 1 by status? What is the desired output, how can we choose which of the row to return by status? Do you want the latest, based on the ID incrementing?
Please describe your requirements a little more, it's confusing.
Cheers,
J-F
October 16, 2009 at 7:50 am
i needed the largest OrderId for each status, so in this case, the results would be:
(No column name)OrderStatus
2 A
5 B
8 C
10 D
October 16, 2009 at 7:57 am
winston Smith (10/16/2009)
serious apologies for posting this question. was making it much more complicated that it should be.its
select max(OrderID),OrderStatus from #orderline
group by OrderStatus
I was trying joins and subqueries and a host of other things. not thinking straight today.apologies.
Well, you have it straight right Winston! Don't worry about the not thinking straight part! I usually am unable to think at all before my second coffee!
Have a nice day!
Cheers,
J-F
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply