Group wise no in desc order.

Question

Post reply

Group wise no in desc order.

Pravin Patel-491467

Say Hey Kid

Points: 674
More actions
May 25, 2010 at 3:25 am

#224948

Hi Friends,
Need your help.... please run below script in ur query window.
-------------------------------------------------------
CREATE TABLE Test59(
[Order_No] [int] NULL,
[Party] [varchar](50) NULL
) ON [PRIMARY]
GO
Insert into Test59 values (1,'Nitin')
Insert into Test59 values (1,'Shivang')
Insert into Test59 values (1,'Maulik')
Insert into Test59 values (2,'Mitul')
Insert into Test59 values (2,'Ashish')
Insert into Test59 values (2,'Kaushik')
Insert into Test59 values (4,'Dhaval')
Insert into Test59 values (4,'Navin')
Insert into Test59 values (4,'Swati')
Insert into Test59 values (4,'Varsha')
GO
select Order_No,Party, 'Row_No' = ROW_NUMBER() OVER (Partition by order_no order by order_No DESC)
from test59
-------------------------------------------------------
In select query result i want last column in desceding order (group by order_no) without changing "Party" column order.
For Ex.
For order_no 1.
Row_no should be 3 for "Nitin", 2 for "Shivang" and 1 for "Maulik".. and so on for all the rest order_no.
If you require more specification plese let me know here.
Thanks & Regards,
Pravin Patel.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Paul White SSC Guru Points: 150457 More actions · Answer 1

Rows in a table have no intrinsic order, so you need something to define (or remember) the insertion order:

CREATE TABLE dbo.Test59

(

[RowID] INTEGER IDENTITY PRIMARY KEY,

[Order_No] INTEGER NOT NULL,

[Party] VARCHAR(50) NOT NULL

)

GO

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (1,'Nitin');

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (1,'Shivang');

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (1,'Maulik');

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (2,'Mitul');

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (2,'Ashish');

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (2,'Kaushik');

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (4,'Dhaval');

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (4,'Navin');

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (4,'Swati');

INSERT INTO dbo.Test59 (Order_No, Party) VALUES (4,'Varsha');

GO

SELECT Order_No,

Party,

RowNo = ROW_NUMBER() OVER (PARTITION BY Order_No ORDER BY RowID DESC)

FROM dbo.Test59

ORDER BY

Order_No, RowNo DESC;

GO

DROP TABLE dbo.Test59;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ken McKelvey SSCoach Points: 18484 More actions · Answer 2

without changing "Party" column order.

In relational theory, tables are unordered sets so the Party column has no order unless you use an ORDER BY clause.

(ie SQL does not understand the order you inserted the rows.)

In practice, due to the way the MS SQL holds data, the insert order is sort of known for small tables with low numbers of users but cannot be relied upon.

You need to redesign your table so you can specify an ORDER BY to give the order you want.

EDIT: Sorry Paul - I did not notice you had posted.

Paul White SSC Guru Points: 150457 More actions · Answer 3

Ken McKelvey (5/25/2010)
EDIT: Sorry Paul - I did not notice you had posted.

No worries. I do it all the time. Glad to get in first for once 🙂

In practice, due to the way the MS SQL holds data, the insert order is sort of known for small tables with low numbers of users but cannot be relied upon.

You mean something like...

RowNo = ROW_NUMBER() OVER (PARTITION BY Order_No ORDER BY %%****%% DESC)

...?

edit: removed temptation 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Pravin Patel-491467 Say Hey Kid Points: 674 More actions · Answer 4

Paul,

I appreciate your help. But i can't change table definition. this is just an example. i want to apply this logic in my live environment SP. It's already very complicated. So, i can't add one more column in table.

Is there any other way without changing table definition.

Thanks & Regards,

Pravin Patel.

Paul White SSC Guru Points: 150457 More actions · Answer 5

Pravin Patel-491467 (5/25/2010)
I appreciate your help. But i can't change table definition. this is just an example. i want to apply this logic in my live environment SP. It's already very complicated. So, i can't add one more column in table.

Nope, no other way.

As it stands you have no record of the order items were added, so you can't order by it.

Change the table definition.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi