SQL solution - 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

SQL solution

Author

Topic

Sybar
Starting Member

6 Posts

Posted - 2012-07-06 : 07:57:02

Hi
I have the following problem:
A table according to
Orderno ItemNo Order Deliv Picked
194502 1289654 33,00 30,00 20120426
194502 1289654 33,00 30,00 20120426
194502 1289654 33,00 3,00 20120427
194502 1289654 33,00 3,00 20120427
......................
the same item may appear as can be seen several times and I would like to select just those rows with the same orderno,itemno having the earliest picked (date). Help with the select statement is appreciated.
Regards

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 08:00:58

You can use the RANK function as follows:

SELECT * FROM
(
	SELECT *,
	RANK() OVER(PARTITION BY orderno,itemno ORDER BY Picked) AS Rnk
	FROM YourTable
)s
WHERE Rnk = 1;

In your example, that would pick both rows for 20120426. If you want only one row, perhaps because of rows are identical, replace the RANK() function with ROW_NUMBER() function.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-06 : 08:03:06

select Orderno, ItemNo, Order, Deliv, Picked
from
(
select row_number() over (partition by Orderno,ItemNo order by Picked ASC) as rnum,* from Ordertable
)dt
where rnum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 09:45:42

[code]
SELECT Orderno, ItemNo, Order, Deliv, Picked
FROM
(
SELECT *,MIN(Picked) OVER (PARTITION BY Orderno,ItemNo) AS MinDate
FROM Table
)t
WHERE MinDate= Picked
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sybar
Starting Member

6 Posts

Posted - 2012-07-11 : 13:31:33

Thanks to all. It worked fine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources