Show First Order With All of User Orders

Question

Show First Order With All of User Orders

MotivateMan1394

Hall of Fame

Points: 3876
More actions
November 14, 2015 at 7:41 am

#302303

Hi
I Have This Table
CREATE TABLE [dbo].[Table_1](
[UId] [int] NULL,
[Orderid] [int] NULL,
[OrderMonth] [int] NULL
)
With These Data :
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (1, 100, 3)
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (1, 101, 3)
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (1, 105, 4)
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (1, 110, 4)
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (1, 120, 4)
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (1, 135, 8)
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (2, 103, 4)
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (2, 104, 4)
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (2, 107, 4)
INSERT [dbo].[Table_1] ([UId], [Orderid], [OrderMonth]) VALUES (2, 122, 7)
-----------------------------------------------
This is the Result Of table select :
Userid Orderid OrderMonth
11003
11013
11054
11104
11204
11358
21034
21044
21074
21227
I Want This Result
UIdOrderidOrderMonthFirstOrder
11003 3
11013 3
11054 3
11104 3
11204 3
11358 3
21034 4
21044 4
21074 4
21227 4
How Can I show FirstOrder of each user in every Row? (Like Above)
I user Row_Number() And Join With Itself. But I didnot get result.
Please help me.
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try MIN(OrderMonth) OVER (<windowing clause>)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

I'm curious as to what will happen when the year rolls over. It strongly recommend NOT using just a simple month identifier. There has to be somewhere in your database that contains the full order date.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76998 More actions · Answer 3

MIN won't always give the correct answer, because the first order may not be the smallest order. You want to use FIRST_VALUE instead of MIN.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA