Please write a query to get my desired output
-
I have 3 people with 3 items each. I want to select 1item from each and make sure don't select same item from next person.
I have table with following data
EX: person item
1 a
1 b
1 c
2 a
2 b
2 c
3 a
3 b
3 cWrite a query to get the below result. I need output as below
1 a
2 b
3 cIt should be applicable to many persons and many items
-
Hi,
Please use below query for that
select id,item from
(
select *,row_number() over(partition by id order by id) as rn
from Tablename
)tt
where id=rn -
You're not going to learn anything if you get other people to do your homework for you, especially if they give you the wrong answer. The solution provided is incorrect because (a) if relies on there being no gaps in the id column and (b) there's no guarantee that the same items are going to be assigned the same value of rn for each id. Now, please show us what you've tried and we'll see if we can help you.
John
-
Hi John,
Its not home work. The requirement is very broader(its not just Id and Item) and I minimized it to smaller so that it will be easily understood.
I agree with you, Exactly what to said is correct. I already used row number, it is not working in all cases because of the order of the ID and Item.
Thanks
-
People here are more than happy to help, but you have to do your homework first. If you don't at least try to solve the problem yourself (post what you tried), people will assume that you're just trying to get other people to do your work for you. This is an awesome place to learn - but that means you have to at least try to answer the question yourself. If you don't want to try anything first, open your wallet and offer people money to do it for you.
Might help you get better answers if you read this:Forum Etiquette: How to post data/code on a forum to get the best help
-
I don't want keep real-time data outside. Please look into below example.
create table #temp (Name varchar(10),Work Varchar(10),ID int, Part Varchar(2))
Insert into #temp(Name,Work,ID,Part)
Select 'John','IT',1,'x' union
Select 'John','IT',1,'y' union
Select 'John','IT',1,'z' union
Select 'John','IT',2,'x' union
Select 'John','IT',2,'y' union
Select 'John','IT',2,'z' union
Select 'John','IT',3,'x' union
Select 'John','IT',3,'y' union
Select 'John','IT',3,'z'Select distinct Name,Work,ID into #a from #temp
select distinct Name,Work,Part into #b from #temp
Select Name,Work,COUNT(ID) as ID_Count into #c from #a GROUP BY Name,Work
select Name,Work,COUNT(Part) as Part_count into #d from #b GROUP BY Name,Work
Select a.Name,a.Work,a.ID_Count,b.Part_count into #count from #c a
join #d b
on a.Name=b.Name
and a.Work=b.Work--drop Table #final
Select y.name,y.work,y.ID,y.Part,ROW_NUMBER () OVER ( PARTITION BY y.Name,y.work Order by y.Name,y.work,y.ID ) as Row_count ,c.ID_Count,c.Part_count into #final
from #temp y
join #count c
on y.name=c.Name
and y.work=c.Workselect Name,Work,ID, Part from #final where Row_count%Part_count=1
-
what happened when you tested the code provided by deepika0928?
What exactly is the point of your post? It's just a bunch of random-ish code... Did you try any of it or try to modify it to suit your needs? -
Declare @Row_number int=1
Declare @Max_Row int
Select @Max_Row=MAX(Row_count) from #final
While @Row_number<=@Max_Row
Begin
Insert into X(Name,Work,ID,Part)
Select Name,Work,ID,Part from #final where Row_count=@Row_number and ID not in (Select ID from X) and Part not in (Select Part from X)Select @Row_number=@Row_number+1
END -
This works but hoping for better solution
-
What do "a", "b", "c" represent? Actual values or first value, second value, third value?
Write a query to get the below result. I need output as below
1 a
2 b
3 cAre you trying to return Nth value for each unique "ID"?
Can you explain in plain English the logic you're trying to use to get this result? -
Sounds like an interview question or a school question.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Agreed. Doesn't sound terribly complicated given a proper description of what data you start with and the output desired...
- rbaddamsql - Friday, March 9, 2018 7:48 PM
I think the definition of this problem is a bit flawed but whatever. Since you gave it the good ol' college try and posted your code, here's a possible alternative to your While Loop solution for your original problem definition above. Details are in the comments.
--=============================================================================
-- Create and populate a test table with a bit more data than the original
--=============================================================================
--DROP TABLE #TestTable
GO
CREATE TABLE #TestTable
(
Person INT
,Item CHAR(1)
)
;
GO
INSERT INTO #TestTable
(Person,Item)
SELECT 35,'a' UNION ALL
SELECT 35,'b' UNION ALL
SELECT 35,'c' UNION ALL
SELECT 35,'d' UNION ALL
SELECT 35,'e' UNION ALL
SELECT 9,'a' UNION ALL
SELECT 9,'b' UNION ALL
SELECT 9,'c' UNION ALL
SELECT 9,'d' UNION ALL
SELECT 9,'e' UNION ALL
SELECT 14,'a' UNION ALL
SELECT 14,'b' UNION ALL
SELECT 14,'c' UNION ALL
SELECT 14,'d' UNION ALL
SELECT 14,'e'
;
--=============================================================================
-- Distribute the unique items in a Round-Robin fashion.
--=============================================================================
WITH ctePerson AS
(--==== Enumerate the people since their ID's may not be sequential.
-- Enumeration starts a zero so we can use a join based on Modulo.
-- We also count the number of unique people for the Modulo join.
SELECT Person
,Person# = ROW_NUMBER() OVER (ORDER BY Person) - 1
,PersonCount = COUNT(Person) OVER ()
FROM #TestTable
GROUP BY Person
)
,cteItem AS
(--==== Enumerate the items.
-- Enumeration starts a zero so we can use a join based on Modulo.
SELECT Item
,Item# = ROW_NUMBER() OVER (ORDER BY Item) - 1
FROM #TestTable
GROUP BY Item
)--==== After all that, we just do the join.
SELECT p.Person
,i.Item
FROM ctePerson p
JOIN cteItem i ON p.Person# = i.Item# % (p.PersonCount)
ORDER BY Person, Item
;
Here are the results for the test data above. We'd need a lot more to prove any claims of performance but I'm out of time for the evening... I've got a deployment to do in 10 minutes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply