slow row - 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 2005 Forums

Transact-SQL (2005)

slow row

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-03-09 : 02:45:51

i have this table, and i want to sign the slow rows(flag)
(the problem is when i have 2 rows with same days ,if i have the most slow rows i dont care who sign,he can sign the first most slow rows like C and B)

OrderNumber OrderRow DAYS
A              1      10
A              2       1
B              1       20
B              2       20
B              3        5
C              1       5
C              2       10
C              3       10

i want to get

OrderNumber OrderRow DAYS    flag
A              1      10       1
A              2       1       0

B              1      20       1 
B              2      20       0
B              3       5       0

C              1       5       0       
C              2      10       1
C              3      10       0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 03:05:53

SELECT OrderNumber, OrderRow, DAYS, case when flag=1 then 1 else 0 end as flag from
(
select OrderNumber, OrderRow, DAYS, row_number() over(partition by OrderNumber, OrderRow order by DAYS desc) as flag from your_table
) as t
order by OrderNumber, OrderRow, DAYS

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-03-09 : 03:08:31

madhivanan

can you translate it sql 2000

(i use sql 2000 for this query)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 03:28:04

quote:
Originally posted by inbs

madhivanan

can you translate it sql 2000

(i use sql 2000 for this query)

Why did you post in 2005 forum?

Try this

select t1.OrderNumber, t1.OrderRow, case when t2.DAYS is null then 0 else 1 end as flag
from your_table as t1 left outer join
(
select OrderNumber, OrderRow, max(DAYS) as DAYS from your_table
group by OrderNumber, OrderRow
) as t2
on t1.OrderNumber=t2.OrderNumber and t1.OrderRow=t2.OrderRow

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-03-09 : 03:39:40

thanks madhivanan

the internal query is wrong (by my example)
select OrderNumber, OrderRow, max(DAYS) as DAYS from your_table
group by OrderNumber, OrderRow

never mind i think that i figure it out.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 03:41:18

quote:
Originally posted by inbs

thanks madhivanan

the internal query is wrong (by my example)
select OrderNumber, OrderRow, max(DAYS) as DAYS from your_table
group by OrderNumber, OrderRow

never mind i think that i figure it out.

Ok. Post the workable code

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-03-09 : 06:45:00

SELECT s.OrderNo,Min(s.OrderRow) AS OrderRow
INTO #TempSlowRow
FROM MYTABLE s JOIN
(SELECT OrderNo,MAX(TotalDays) AS TotalDays
FROM MYTABLE
GROUP BY OrderNo) f ON (s.OrderNo=f.OrderNo AND s.DAYS=f.DAYS)
GROUP BY s.OrderNo

next row update the flag (SlowRow),

UPDATE s
SET SlowRow=1
FROM MYTABLE s JOIN #TempSlowRow t ON (s.OrderNo=t.OrderNo AND s.OrderRow=t.OrderRow)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 09:05:23

Ok. Why dont you do it as a SELECT statement as I specified? If you want to update the table, you need to update it everytime the data are added

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources