July 14, 2017 at 10:12 am
I have table with data like the below :
ID Name Category_Sort
1 Campaign1 Beverage
2 Campaign2 Beverage
3 Campaign3 Food
I need to select with separate depend on cateory_sort column for example
Beverage , Food, beverage
and soon if i have more rows
July 14, 2017 at 10:17 am
Your requirement is not clear.
Please help us understand by providing DDL, and sample data in the form of INSERT statements.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 14, 2017 at 10:22 am
Phil beat me to it. We need more/better sample data and clearer expected results. Something like this for the sample data:
DECLARE @yourtable TABLE (id int, [name] varchar(100), category_sort varchar(100));
INSERT @yourtable VALUES
(1,'Campaign1','Beverage'),
(2,'Campaign2','Beverage'),
(3,'Campaign3','Food');
-- Itzik Ben-Gan 2001
July 14, 2017 at 10:36 am
the below working find with three rows :
DECLARE @test-2 TABLE (ID int, Name varchar(30), Category_Sort varchar(30)); Insert into @test-2 values(1,'Campaign1','Beverage') ,(2,'Campaign2','Beverage') ,(3,'Campaign3','Food') ;with mycte as (Select *, row_number() Over(Partition by Category_Sort Order by ID ) rn from @test-2)Select ID,Name,Category_Sort from mycteOrder by rn,ID
but if i add more rows with same values will duplicate :
if i do like the below will return twice food
DECLARE @test-2 TABLE (ID int, Name varchar(30), Category_Sort varchar(30));
Insert into @test-2 values
(1,'Campaign1','Beverage')
,(2,'Campaign2','Beverage')
,(3,'Campaign3','Food')
,(4,'Campaign4','Food')
,(5,'Campaign4','Food')
,(6,'Campaign2','Beverage')
;with mycte as (
Select *, row_number() Over(Partition by Category_Sort Order by ID ) rn from @test-2)
Select ID,Name,Category_Sort from mycte
Order by rn,ID
July 14, 2017 at 10:44 am
ikrami2000 - Friday, July 14, 2017 10:36 AMthe below working find with three rows :
DECLARE @test-2 TABLE (ID int, Name varchar(30), Category_Sort varchar(30)); Insert into @test-2 values(1,'Campaign1','Beverage') ,(2,'Campaign2','Beverage') ,(3,'Campaign3','Food') ;with mycte as (Select *, row_number() Over(Partition by Category_Sort Order by ID ) rn from @test-2)Select ID,Name,Category_Sort from mycteOrder by rn,IDbut if i add more rows with same values will duplicate :
if i do like the below will return twice food
DECLARE @test-2 TABLE (ID int, Name varchar(30), Category_Sort varchar(30));
Insert into @test-2 values
(1,'Campaign1','Beverage')
,(2,'Campaign2','Beverage')
,(3,'Campaign3','Food')
,(4,'Campaign4','Food')
,(5,'Campaign4','Food')
,(6,'Campaign2','Beverage')
;with mycte as (
Select *, row_number() Over(Partition by Category_Sort Order by ID ) rn from @test-2)Select ID,Name,Category_Sort from mycte
Order by rn,ID
Does adding this to your code give you the desired results? WHERE RN = 1
-- Itzik Ben-Gan 2001
July 14, 2017 at 10:46 am
return two records :
1 Campaign1 Beverage
3 Campaign3 Food
July 14, 2017 at 11:16 am
ikrami2000 - Friday, July 14, 2017 10:46 AMreturn two records :1 Campaign1 Beverage
3 Campaign3 Food
what results are you expecting?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 14, 2017 at 11:18 am
1 Campaign1 Beverage
3 Campaign3 Food
2 Campaign2 Beverage
4 Campaign4 Food
6 Campaign6 Beverage
5 Campaign5 Food
so result to be depend on category, and can not be duplicated by previous or next row, until no other choice
July 14, 2017 at 11:27 am
ikrami2000 - Friday, July 14, 2017 11:18 AM1 Campaign1 Beverage
3 Campaign3 Food
2 Campaign2 Beverage
4 Campaign4 Food
6 Campaign6 Beverage
5 Campaign5 Foodso result to be depend on category, and can not be duplicated by previous or next row, until no other choice
What is the logic for generating Campaign5 and Campaign6, as they do not exist in your source data?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 14, 2017 at 11:34 am
i need to get the data depend on Category (Food,beverage, hot drinks,....)
but to be not duplicated for example if i have first row Beverage so when i select second row it must be another category like'Food' and third row if there is no other category i will select Food, but if there is beverage so will select it and so on, i am not concern about Campaign5 or 6 it is just column but i want the condition depend on Category (food, beverage,....) like the below
1 Campaign1 Beverage
3 Campaign3 Food
2 Campaign2 Beverage
4 Campaign4 Food
6 Campaign6 Beverage
5 Campaign5 Food
July 14, 2017 at 11:50 am
ikrami2000 - Friday, July 14, 2017 11:34 AMi need to get the data depend on Category (Food,beverage, hot drinks,....)
but to be not duplicated for example if i have first row Beverage so when i select second row it must be another category like'Food' and third row if there is no other category i will select Food, but if there is beverage so will select it and so on, i am not concern about Campaign5 or 6 it is just column but i want the condition depend on Category (food, beverage,....) like the below1 Campaign1 Beverage
3 Campaign3 Food
2 Campaign2 Beverage
4 Campaign4 Food
6 Campaign6 Beverage
5 Campaign5 Food
If you are not prepared to make your sample data match your desired results, nor to explain transformation and ordering rules using terms which I can understand, I will have to leave others to help you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 14, 2017 at 11:59 am
ikrami2000 - Friday, July 14, 2017 11:34 AMi need to get the data depend on Category (Food,beverage, hot drinks,....)
but to be not duplicated for example if i have first row Beverage so when i select second row it must be another category like'Food' and third row if there is no other category i will select Food, but if there is beverage so will select it and so on, i am not concern about Campaign5 or 6 it is just column but i want the condition depend on Category (food, beverage,....) like the below1 Campaign1 Beverage
3 Campaign3 Food
2 Campaign2 Beverage
4 Campaign4 Food
6 Campaign6 Beverage
5 Campaign5 Food
Something like this perhaps?
DECLARE @yourtable TABLE (id int, [name] varchar(100), category_sort varchar(100));
INSERT @yourtable VALUES
(1,'Campaign1','Beverage'),
(2,'Campaign2','Beverage'),
(3,'Campaign3','Food'),
(4, 'Campaign4','Food'),
(5, 'Campaign5','Food'),
(6, 'Campaign6','Beverage');
WITH x AS
(
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY category_sort ORDER BY id)
FROM @yourtable
)
SELECT id, name, category_sort
FROM x
ORDER BY rn, category_sort;
Returns:
id name category_sort
-----------------------------------------
1 Campaign1 Beverage
3 Campaign3 Food
2 Campaign2 Beverage
4 Campaign4 Food
6 Campaign6 Beverage
5 Campaign5 Food
-- Itzik Ben-Gan 2001
July 14, 2017 at 12:06 pm
Would you please test with this data :
1 Campaign1 Beverage
2 Campaign2 Beverage
3 Campaign3 Food
4 Campaign4 Beverage
5 Campaign5 Hot Drinks
6 Campaign6 Advertizing
7 Campaign7 Marketing
With your code it will return :
6 Campaign6 Advertizing
1 Campaign1 Beverage
3 Campaign3 Food
5 Campaign5 Hot Drinks
7 Campaign7 Marketing
2 Campaign2 Beverage
4 Campaign4 Beverage
if you see Beverage in last two records , it must not come twice so the record for one of beverage must be between for example "Hot Drinks" and "Marketing"
July 14, 2017 at 12:36 pm
As others have indicated multiple times, it would be much more helpful if you could post sample data the way we have in our posts, and actually explain the business logic more clearly.
That way we can start testing queries against it relatively painlessly. If you force us to create the sample data from raw text, that barrier will discourage many helpful folks from even trying.
Cheers!
EDIT: I did get a bit curious. So, is the ONLY requirement that no category_sort appear consecutively (as much as is possible), or are there further requirements around the desired sort?
July 14, 2017 at 3:51 pm
Ok, May be i need to add more details, the below is the code which have the data:
DECLARE @yourtable TABLE (id int, [name] varchar(100), category_sort varchar(100));
INSERT @yourtable VALUES
(1, 'Campaign1','Beverage'),
(2, 'Campaign2','Beverage'),
(3, 'Campaign3','Food'),
(4, 'Campaign4','Beverage'),
(5, 'Campaign5','Hot Drinks'),
(6, 'Campaign6','Advertizing'),
(7, 'Campaign7','Marketing');
WITH x AS
(
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY category_sort ORDER BY id)
FROM @yourtable
)
SELECT id, name, category_sort
FROM x
ORDER BY rn, category_sort;
Now when i run the above code it is return :
6 Campaign6 Advertizing
1 Campaign1 Beverage
3 Campaign3 Food
5 Campaign5 Hot Drinks
7 Campaign7 Marketing
2 Campaign2 Beverage
4 Campaign4 Beverage
But actually what i need is :
6 Campaign6 Advertizing
1 Campaign1 Beverage
3 Campaign3 Food
5 Campaign5 Hot Drinks
2 Campaign2 Beverage
7 Campaign7 Marketing
4 Campaign4 Beverage
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply