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
Hany MostafaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sarabandeCommented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post an example of the desired result based on the sample you gave?
0
Hany MostafaAuthor Commented:
result
Product        Category
P1                  Cat1
P3                 Cat3
P2                  Cat1
P4                  Cat2
P5                  Cat1
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What should be the expected result if you have a new row:
P6  Cat1
0
Hany MostafaAuthor Commented:
add it to the end of rows
0
sarabandeCommented:
any ordering is based on a valid operator less function what means that some rules must apply for < relation:

(1) row_a < row_b is true ==> row_b < row_a is false
(2) row_a < row_b is false and row_b < row_a is false ==> row_a == row_b is true

in your sample we have that row 1 is less than row 3 (because category changed) but also row 3 is less than row 1 as well. so the less operation is not well defined and must fail for some selections (as shown by Vitor).

however you can try to get what you want programmatically:

get an array of rows sorted by product and category

pseudo code

for row = 2 to max(row)-1
     for nextrow = row +1 to max(row )-1 do
           if (row.category <> nextrow.category) 
                swap(row + 1, nextrow)  // can be same row
                break for
           end if 
     end for
end for

Open in new window


Sara
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
Hany MostafaAuthor Commented:
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
0
PortletPaulfreelancerCommented:
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}
0
PortletPaulfreelancerCommented:
Sorry for the misspellings above

Do you use SQL server 2012 or later?
0
Hany MostafaAuthor Commented:
no 2008
0
PortletPaulfreelancerCommented:
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.
0
Hany MostafaAuthor Commented:
Id       ProductId        CtegoryId
1            920                   3
2            512                   2
3            630                   15
4            88                     1
5            26                     1
6            1320                 3
7             332                   4
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
Hany MostafaAuthor Commented:
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
0
PortletPaulfreelancerCommented:
I don't see a way to achieve this from just that single table. Perhaps others can.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.