Avatar of Hany Mostafa
Hany Mostafa
Flag for Egypt asked on

Sorting Rows based on previous row value

i have table have product column and Category Column like the below:

Product        Category
P1                  Cat1
P2                  Cat1
P3                  Cat3
P4                  Cat2
P5                  Cat1

i want to sort rows  where previous category value not equal the next row
.NET ProgrammingMicrosoft SQL Server

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
sarabande

i don't see any difference in order of both lists beside that P3 was typed instead of P2 in the second row of the second list. can you post a better sample to explain what you mean?

Sara
Vitor Montalvão

Can you post an example of the desired result based on the sample you gave?
Hany Mostafa

ASKER
result
Product        Category
P1                  Cat1
P3                 Cat3
P2                  Cat1
P4                  Cat2
P5                  Cat1
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Vitor Montalvão

What should be the expected result if you have a new row:
P6  Cat1
Hany Mostafa

ASKER
add it to the end of rows
ASKER CERTIFIED SOLUTION
sarabande

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

Is there a reason why you want P3/CAT3 before P2/CAT1?

What happens to P4/CAT2 ?

Without a knowledge of the reason You may have difficulty finding an effective solution.

E.g is there a hierarchy in the data?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

I think we don't have all information to proceed with a solution.
For example, how the Category is ordered? Only alphabetic? Shouldn't exist a Category table?
And what's the purpose to sort the rows like that?
Hany Mostafa

ASKER
first i want to thanks you all
what i want is sort rows where category not similar to the previous row and the next row
PortletPaul

Where category is different to the previous and next rows...
Previous and next require an order too, so we need this order before we calculate an output order

So what is in the data to ensure the correct initial order. E.g. A date/time Created?

Also, do you Use SQL server 2012 or later?

{please note this comment has been edited}
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PortletPaul

Sorry for the misspellings above

Do you use SQL server 2012 or later?
Hany Mostafa

ASKER
no 2008
PortletPaul

still need more knowledge about the table and data.

Can you provide the DDL for the table plus inserts of some complete rows. To achieve the wanted output order we must first establish what is previous and what is next  in the data.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Hany Mostafa

ASKER
Id       ProductId        CtegoryId
1            920                   3
2            512                   2
3            630                   15
4            88                     1
5            26                     1
6            1320                 3
7             332                   4
Vitor Montalvão

When you provide samples please provide the source sample as the desired result. So, what should be the result for the sample you've provided?
Hany Mostafa

ASKER
CREATE TABLE [dbo].[Conductor](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Program] [varchar](50) NOT NULL,
	[ProgId] [int] NOT NULL,
	[Material] [varchar](50) NOT NULL,
	[MaterialId] [varchar](50) NOT NULL,
	[Category] [varchar](50) NOT NULL,
	[CategoryId] [int] NOT NULL,
	[BreakNo] [int] NOT NULL,
	[BreakSequ] [int] NULL,
	[Ddate] [date] NULL,
 CONSTRAINT [PK_Conductor] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Conductor] ADD  CONSTRAINT [DF_Conductor_BreakNo]  DEFAULT ((0)) FOR [BreakNo]
GO

ALTER TABLE [dbo].[Conductor] ADD  CONSTRAINT [DF_Conductor_BreakSequ]  DEFAULT ((0)) FOR [BreakSequ]
GO

Open in new window


1      prog1      1      mat001      1      cat01      1      0      0      2015-10-04
2      prog1      1      mat002      2      cat01      1      0      0      2015-12-04
3      prog1      1      mat003      3      cat02      2      0      0      2015-10-15
4      prog1      1      mat004      4      cat02      2      0      0      2015-10-04
5      prog1      1      mat005      5      cat03      3      0      0      2015-10-02
6      prog1      1      mat006      6      cat04      4      0      0      2015-10-03
7      prog2      2      mat001      1      cat01      1      0      0      2015-10-04
8      prog2      2      mat002      2      cat01      1      0      0      2015-12-04
9      prog2      2      mat003      3      cat02      2      0      0      2015-10-15
10      prog2      2      mat004      4      cat02      2      0      0      2015-10-04
11      prog2      2      mat005      5      cat03      3      0      0      2015-10-02
12      prog2      2      mat006      6      cat04      4      0      0      2015-10-03

result after my sort

1      prog1      1      mat001      1      cat01      1      0      0      2015-10-04
3      prog1      1      mat003      3      cat02      2      0      0      2015-10-15
5      prog1      1      mat005      5      cat03      3      0      0      2015-10-02
6      prog1      1      mat006      6      cat04      4      0      0      2015-10-03
2      prog1      1      mat002      2      cat01      1      0      0      2015-12-04
9      prog2      2      mat003      3      cat02      2      0      0      2015-10-15
11      prog2      2      mat005      5      cat03      3      0      0      2015-10-02
12      prog2      2      mat006      6      cat04      4      0      0      2015-10-03
4      prog1      1      mat004      4      cat02      2      0      0      2015-10-04
7      prog2      2      mat001      1      cat01      1      0      0      2015-10-04
8      prog2      2      mat002      2      cat01      1      0      0      2015-12-04
10      prog2      2      mat004      4      cat02      2      0      0      2015-10-04
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PortletPaul

I don't see a way to achieve this from just that single table. Perhaps others can.