|
Home > Archive > Microsoft SQL Server forum > August 2005 > Difficult SQL-Problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Difficult SQL-Problem
|
|
| lvpaul@gmx.net 2005-08-02, 3:23 am |
| 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
aaapaul
| |
| Simon Hayes 2005-08-02, 3:23 am |
| Perhaps 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
| |
| lvpaul@gmx.net 2005-08-02, 3:23 am |
| 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
| |
| Simon Hayes 2005-08-02, 3:23 am |
| Or perhaps you can :-) Just replace max(ordernr) with max(orderdate)
and change the join to be on that column.
Simon
| |
| David Portas 2005-08-02, 7:23 am |
| 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
--
| |
| lvpaul@gmx.net 2005-08-02, 7:23 am |
| Thanks David !
This is what I need.
paul
| |
| lvpaul@gmx.net 2005-08-02, 7:23 am |
| Hi Simon !
Thanks, but this doesn =B4t 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 =3D t2.article and
t1.odate =3D t2.maxdat
But why can=B4t 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
| |
| jsfromynr 2005-08-03, 3:23 am |
| 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
> --
|
|
|
|
|