Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Sara
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
Sara
(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
Sara
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?
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?
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?
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?
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
what i want is sort rows where category not similar to the previous row and the next row
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}
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}
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.
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.
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?
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial