Difficult SQL-Problem

Hello !

This is my table:

Ordernr Date Article
O1 1.1.05 22
O2 2.2.05 33
O3 5.5.05 22
O4 2.2.05 33
O7 8.8.05 55

I need one result-row for each article with the newest Order
(max(date)):

article lastDate lastOrdernumber
22 5.5.05 O3
33 2.2.05 O4
55 8.8.05 O7

How can I get this ?

I tried this:

SELECT distinct article, max(date), max(ordernr)
FROM table
GROUP BY article

article and max(date) is ok, but I am not sure that max(ordernr) and
max(date) comes from the same row.

I think, I will need complex subqueries.

Many thanks
aaapaulPerhaps something like this?

select
t.article,
t.ordernr,
t.orderdate
from
dbo.MyTable t
join
(
select
article,
max(ordernr) as 'ordernr'
from
dbo.MyTable
group by
article
) dt
on t.article = dt.article
and t.ordernr = dt.ordernr

If this doesn't give the results you expect, I suggest you post CREATE
TABLE and INSERT statements to set up a test case - you will probably
get a better response if people can copy and paste something into QA
for testing.

Simon|||Thanks Simon !

But the Problem is, that I need the order with the highest date not the
order with the highest ordernumber.

Perhaps you can modify the statement...

aaapaul|||Or perhaps you can :-) Just replace max(ordernr) with max(orderdate)
and change the join to be on that column.

Simon|||SELECT article, date, MAX(ordernr)
FROM Table AS T
WHERE date =
(SELECT MAX(date)
FROM Table
WHERE article = T.article)
GROUP BY article, date

--
David Portas
SQL Server MVP
--|||Thanks David !

This is what I need.

paul|||Hi Simon !

Thanks, but this doesn t work, too.

I think I need something like this:

(SELECT a.article,a.odate,max(a.ordernr) as maxordernr
FROM TEST a
GROUP BY a.article,a.odate) t1
JOIN
(
SELECT article,max(odate) as maxdat
FROM TEST
GROUP By article
) t2
on t1.article = t2.article and
t1.odate = t2.maxdat

But why cant I join this 2 tables.

Any suggestion ?

Thanks
aaapaul|||in SQL 2000

create table orders(Ordernr int, orderdt Datetime, Article int)

insert into orders values(1,'11/11/2005',1)
insert into orders values(3,'11/12/2005',1)
insert into orders values(5,'11/13/2005',1)

insert into orders values(2,'1/11/2005',2)
insert into orders values(4,'1/12/2005',2)
insert into orders values(6,'1/13/2005',2)

SELECT article, lastdate,
(select MAX(ordernr) from orders AS T
where t.article = latest.article
and t.orderdt = latest.lastdate)
from
(SELECT article, MAX(orderdt) lastdate
FROM orders
GROUP BY article) latest

article lastdate

---- ----------------
----
1 2005-11-13 00:00:00.000 5
2 2005-01-13 00:00:00.000 6

(2 row(s) affected)

drop table orders

in 2005,
use an OLAP function
(row_number() over(partition by article order by orderdt desc) = 1|||Hi David,
Excellent !!
But we can trim the query further If the lastOrderNumber is the
OrderNumber with Max(Orderdate) for a given article .

SELECT *
FROM orders AS T
WHERE orderdt =
(SELECT MAX(orderdt)
FROM orders
WHERE article = T.article)

With warm regards
Jatinder Singh

David Portas wrote:
> SELECT article, date, MAX(ordernr)
> FROM Table AS T
> WHERE date =
> (SELECT MAX(date)
> FROM Table
> WHERE article = T.article)
> GROUP BY article, date
> --
> David Portas
> SQL Server MVP
> --