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

Author

Topic

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-22 : 06:56:36

ID ITEMID DATE QTY

1 XYZ 1/1/2008 1
2 XYZ 2/4/2008 3
3 XYZ 34/2008 2
4 ABC 1/3/2008 4
5 ABC 1/4/2008 1
6 CAB 12/4/2008 3

Actually i neeed max(date) record....

as the id is unique
if i will do group by itemid it is not possible...as id is unique (again iam getting all values)

[i wrote select max(date),itemid,date,qty from table group by id,itemid,qty-this is 100% wrong].....

O/P:This is the o/p i require

ID ITEMID DATE QTY
3 XYZ 34/2008 2
5 ABC 1/4/2008 1
6 CAB 12/4/2008 3

so, is it possible.........[is it through courser's it is possible,if so how]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-22 : 07:07:00

[code]select t1.* from table t1
join
(select ItemID, max(date) as dt from table group by itemid) t2
on t1.ItemID = t2.ItemID and t1.date = t2.dt[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 07:12:29

And if you're using sql 2005 use ROW_NUMBER function:-

SELECT t.ID,
t.ITEMID,
t.DATE,
t.QTY
FROM
(SELECT ROW_NUMBER() OVER( PARTITION BY ID Order BY DATE DESC) AS RowNo,
ID,
ITEMID,
DATE,
QTY
FROM YourTable
)t
WHERE t.RowNo=1

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-22 : 07:22:49

quote:
Originally posted by visakh16

And if you're using sql 2005 use ROW_NUMBER function:-

SELECT t.ID,
t.ITEMID,
t.DATE,
t.QTY
FROM
(SELECT ROW_NUMBER() OVER( PARTITION BY ID Order BY DATE DESC) AS RowNo,
ID,
ITEMID,
DATE,
QTY
FROM YourTable
)t
WHERE t.RowNo=1

please partition by itemid but not with id

SELECT t.ID,
t.ITEMID,
t.DATE,
t.QTY
FROM
(SELECT ROW_NUMBER() OVER( PARTITION BY ITEMID Order BY DATE DESC) AS RowNo,
ID,
ITEMID,
DATE,
QTY
FROM YourTable
)t
WHERE t.RowNo=1
ORDER BY T.ID

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-22 : 07:26:58

quote:
Originally posted by raky

quote:
Originally posted by visakh16

And if you're using sql 2005 use ROW_NUMBER function:-

SELECT t.ID,
t.ITEMID,
t.DATE,
t.QTY
FROM
(SELECT ROW_NUMBER() OVER( PARTITION BY ID Order BY DATE DESC) AS RowNo,
ID,
ITEMID,
DATE,
QTY
FROM YourTable
)t
WHERE t.RowNo=1

please partition by itemid but not with id

SELECT t.ID,
t.ITEMID,
t.DATE,
t.QTY
FROM
(SELECT ROW_NUMBER() OVER( PARTITION BY ITEMID Order BY DATE DESC) AS RowNo,
ID,
ITEMID,
DATE,
QTY
FROM YourTable
)t
WHERE t.RowNo=1
ORDER BY T.ID

ok iam sorry visakh we can do either way..

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-22 : 07:47:26

Hi,
Thanx iam getting o/p if i retrive from single table ...
but the fields are from different tables i have to select
for example i wrote:

elect p.receiveddate AS [Last Received Date],
P.PURCHASENUMBER AS [Ref Doc#],
p.itemid AS [Item ID],
P.DESCRIPTION AS [Description],
I.QtyOnHand [Stock As On Date],
P.ITEMUNITPRICE as [Rate],
P.SUBTOTAL as [Value],
P.ReceivedQty as [Received Qty]

FROM
(SELECT ROW_NUMBER() OVER( PARTITION BY p.ITEMID Order BY p.receiveddate DESC) AS RowNo,
receiveddate,
PURCHASENUMBER,
ITEMID,
DESCRIPTION,
QtyOnHand,
ITEMUNITPRICE,
SUBTOTAL,
ReceivedQty
FROM pURCHASEDETAIL P,inventorybywarehouse i
)t
WHERE t.RowNo=1
ORDER BY p.PURCHASENUMBER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 07:54:26

quote:
Originally posted by Anushka

Hi,
Thanx iam getting o/p if i retrive from single table ...
but the fields are from different tables i have to select
for example i wrote:

elect p.receiveddate AS [Last Received Date],
P.PURCHASENUMBER AS [Ref Doc#],
p.itemid AS [Item ID],
P.DESCRIPTION AS [Description],
I.QtyOnHand [Stock As On Date],
P.ITEMUNITPRICE as [Rate],
P.SUBTOTAL as [Value],
P.ReceivedQty as [Received Qty]

FROM
(SELECT ROW_NUMBER() OVER( PARTITION BY p.ITEMID Order BY p.receiveddate DESC) AS RowNo,
receiveddate,
PURCHASENUMBER,
ITEMID,
DESCRIPTION,
QtyOnHand,
ITEMUNITPRICE,
SUBTOTAL,
ReceivedQty
FROM pURCHASEDETAIL P,inventorybywarehouse i
)t
WHERE t.RowNo=1
ORDER BY p.PURCHASENUMBER

form a derived table out of your join query with two tables and apply ROW_NUMBER() on it. Post you DDL if you want more help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources