query - 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

query

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-05-31 : 09:33:29

ORDERID INACTIVE TRANTIMESTAMP
804172 1 2012-05-31 14:08:33.903
804172 0 2012-05-31 14:10:53.933
804172 0 2012-05-31 14:12:01.417

I want the rows of orderid with min(trantimestamp) and inactive = 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-31 : 09:34:32

[code]
SELECT TOP 1 *
FROM yourtable
WHERE inactive - 0
ORDER BY trantimestamp
[/code]

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-31 : 09:36:57

with cte as
(
select *, seq = row_number() over (partition by orderid order by timestamp) from tbl where inactive = 0
)
select *
from cte
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gagani
Posting Yak Master

112 Posts

Posted - 2012-05-31 : 09:41:07

ORDERID INACTIVE TRANTIMESTAMP
804172 1 2012-05-31 14:08:33.903
804172 0 2012-05-31 14:10:53.933
804172 0 2012-05-31 14:12:01.417
804173 0 2012-05-31 14:12:01.417
804173 0 2012-05-31 14:12:02.417

With the first reply, I am getting only the first row as the output,

804172 1 2012-05-31 14:08:33.903

But I want all the rows in that table meeting that criteria.

I am using sql 2000, row_number() is not found in it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-31 : 09:45:39

you mean for each ORDERID ?


SELECT *
FROM   yourtable t
       INNER JOIN
       (
           SELECT ORDERID, TRANTIMESTAMP = MIN(TRANTIMESTAMP)
           FROM   yourtable
           WHERE  INACTIVE = 0
           GROUP BY ORDERID
       ) m  ON t.ORDERID = m.ORDERID and t.TRANTIMESTAMP = m.TRANTIMESTAMP
WHERE  t.INACTIVE = 0

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources