November 16, 2016 at 4:07 pm
i have a very simple table only with article no., order no. and order date. i want to get the order no. of each article with his youngest order. hereby order no are not mandatory to increase always. so i use following t-sql statement:
SELECT a.ArticleNo, a.OrderDate, (SELECT b.OrderNo from Table_1 b where b.ArticleNo = a.ArticleNo and b.OrderDate = a.OrderDate) as OrderNo from (select ArticleNo, Max(OrderDate) as OrderDate from Table_1 a group by ArticleNo) as a
should be an easier way therefore ? thanks in advance
November 16, 2016 at 5:17 pm
Yes, I'd think there should be a much simpler way. This doesn't sound that hard. We need the consumable DDL for the tables, some sample data and expected results to be able to provide you with a tested solution. Please see the 3rd link in my signature if you have any questions.
November 17, 2016 at 1:48 am
hi ed, here the ddl, i want to recieve orderno 100 for article 2 as youngest order:
USE testDB
USE [testDB]
GO
/****** Object: Table [dbo].[Table_1] Script Date: 17.11.16 09:43:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[ArticleNo] [int] NULL,
[OrderNo] [int] NULL,
[OrderDate] [datetime] NULL,
[AnyText] [nchar](3) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (1, 101, CAST(N'2016-01-20T00:00:00.000' AS DateTime), N'abc')
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (2, 101, CAST(N'2016-01-20T00:00:00.000' AS DateTime), N'def')
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (3, 101, CAST(N'2016-01-20T00:00:00.000' AS DateTime), N'ghi')
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (4, 101, CAST(N'2016-01-20T00:00:00.000' AS DateTime), N'jkl')
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (2, 102, CAST(N'2016-02-01T00:00:00.000' AS DateTime), N'mno')
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (3, 102, CAST(N'2016-02-01T00:00:00.000' AS DateTime), N'pqr')
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (4, 102, CAST(N'2016-02-01T00:00:00.000' AS DateTime), N'stu')
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (5, 105, CAST(N'2016-02-01T00:00:00.000' AS DateTime), N'vwx')
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (2, 100, CAST(N'2016-02-03T00:00:00.000' AS DateTime), N'yza')
GO
INSERT [dbo].[Table_1] ([ArticleNo], [OrderNo], [OrderDate], [AnyText]) VALUES (6, 100, CAST(N'2016-02-03T00:00:00.000' AS DateTime), N'bcd')
GO
select ArticleNo, OrderDate, OrderNo from Table_1 order by ArticleNo, OrderDate, OrderNo
SELECT a.ArticleNo, a.OrderDate, (SELECT b.OrderNo from Table_1 b where b.ArticleNo = a.ArticleNo and b.OrderDate = a.OrderDate) as OrderNo from (select ArticleNo, Max(OrderDate) as OrderDate from Table_1 a group by ArticleNo) as a
-- thanks in advance
November 17, 2016 at 5:36 am
Thanks, that helps a lot. You said in your OP that the order numbers are not always sequential and the youngest is determined by OrderDate. This means you want to sort by OrderDate in descending order. The following will, within each ArticleNo, number the rows by OrderDate in descending order. From there, just pick the first one.
WITH cteArticles AS (
SELECT ArticleNo, OrderNo, RN = ROW_NUMBER() OVER(PARTITION BY ArticleNo ORDER BY OrderDate DESC)
FROM dbo.Table_1
)
SELECT ArticleNo, OrderNo
FROM cteArticles
WHERE RN = 1;
Hope this helps.
November 17, 2016 at 9:39 am
thanks, thats what i am looking for
November 17, 2016 at 11:05 am
Glad I could help. Thanks for the feedback.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply