January 30, 2012 at 7:00 pm
I have 2 table:
Table Category:
ID NameCategory
1 A
2 B
3 C
and table CategoryDetail
ID NameDetail CategoryID
1 A1 1
2 A2 1
3 B1 2
4 C1 3
5 C2 3
I want result:
CategoryID NameCategory NameDetail
1 A A1
2 B B1
3 C C1
Every one, help me please. Thank you.
January 30, 2012 at 7:10 pm
Algorithm:
1. Join Category and CategoryDetail on CategoryID
2. Use ROW_NUMBER on CategoryDetails's ID column ascendingly parititioning by CategoryID
3. Put them in a CTE
4. Retreive only row_number = 1 rows
Now that u have the algorithm, u give us the code 🙂
January 30, 2012 at 7:41 pm
Thanks for support, but can you write sql select for me, please! Thank you.
January 30, 2012 at 9:48 pm
vantuan02t1 (1/30/2012)
but can you write sql select for me, please.
Unfortunately, i can't mate , cuz i'm not getting paid for that :w00t:
I have given u an algorithm, cant you alteast ATTEMPT a query?
Things apart, let us see if others can help you!
January 30, 2012 at 10:28 pm
CREATE TABLE Category
(CategoryId int, NameCategory varchar(245))
INSERT INTO Category ( CategoryId, NameCategory) VALUES (1,'A')
INSERT INTO Category ( CategoryId, NameCategory) VALUES (2,'B')
INSERT INTO Category ( CategoryId, NameCategory) VALUES (3,'C')
CREATE table CategoryDetail (
ID INT, NameDetail VARCHAR(245), CategoryID INT)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (1,'A1',1)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (2,'A2',1)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (3,'B1',2)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (4,'C1',3)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (5,'C2',3)
SELECT a.categoryid,
a.NameCategory,
a.NameDetail
FROM (SELECT c.categoryid,
cd.id,
c.NameCategory,
cd.NameDetail,
RN = ROW_NUMBER() OVER (PARTITION BY c.categoryId ORDER BY cd.Id ASC)
FROM category c
INNER JOIN categorydetail cd
ON c.CategoryId = cd.categoryId) a
WHERE a.RN = 1
January 31, 2012 at 12:02 am
Thank you very much!
January 31, 2012 at 12:03 am
Siva Ramasamy (1/30/2012)
CREATE TABLE Category(CategoryId int, NameCategory varchar(245))
INSERT INTO Category ( CategoryId, NameCategory) VALUES (1,'A')
INSERT INTO Category ( CategoryId, NameCategory) VALUES (2,'B')
INSERT INTO Category ( CategoryId, NameCategory) VALUES (3,'C')
CREATE table CategoryDetail (
ID INT, NameDetail VARCHAR(245), CategoryID INT)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (1,'A1',1)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (2,'A2',1)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (3,'B1',2)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (4,'C1',3)
INSERT INTO categorydetail (id, namedetail, categoryid) VALUES (5,'C2',3)
SELECT a.categoryid,
a.NameCategory,
a.NameDetail
FROM (SELECT c.categoryid,
cd.id,
c.NameCategory,
cd.NameDetail,
RN = ROW_NUMBER() OVER (PARTITION BY c.categoryId ORDER BY cd.Id ASC)
FROM category c
INNER JOIN categorydetail cd
ON c.CategoryId = cd.categoryId) a
WHERE a.RN = 1
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 1:39 am
A second option:
SELECT
c.CategoryId,
c.NameCategory,
Top1.NameDetail
FROM dbo.Category AS c
CROSS APPLY
(
SELECT TOP (1) *
FROM dbo.CategoryDetail AS cd
WHERE cd.CategoryID = c.CategoryId
ORDER BY cd.ID
) AS Top1;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 1:43 am
Very good, I have do it, thank you very much.
January 31, 2012 at 1:44 am
Paul,
I've seen a few examples on the forums where some people have used a CTE with the row_number() function and others have used cross apply - I understand both features but am not sure if there is any significant performance difference given different circumstances - what's your opinion?
January 31, 2012 at 2:20 am
SQL Kiwi (1/31/2012)
A second option:
SELECT
c.CategoryId,
c.NameCategory,
Top1.NameDetail
FROM dbo.Category AS c
CROSS APPLY
(
SELECT TOP (1) *
FROM dbo.CategoryDetail AS cd
WHERE cd.CategoryID = c.CategoryId
ORDER BY cd.ID
) AS Top1;
That is a great alternative, I've never considered it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 3:14 am
Loundy (1/31/2012)
I've seen a few examples on the forums where some people have used a CTE with the row_number() function and others have used cross apply - I understand both features but am not sure if there is any significant performance difference given different circumstances - what's your opinion?
Bob Hovious wrote an SSC article about this:
http://qa.sqlservercentral.com/articles/T-SQL/69481/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 3:24 am
SQL Kiwi (1/31/2012)
Loundy (1/31/2012)
I've seen a few examples on the forums where some people have used a CTE with the row_number() function and others have used cross apply - I understand both features but am not sure if there is any significant performance difference given different circumstances - what's your opinion?Bob Hovious wrote an SSC article about this:
Excellent post!.. Cleared things up nicely - thanks for that 🙂
January 31, 2012 at 9:02 am
I agree..but I was also learning ROW_NUMBER() yesterday only...so it was a learning experience for me anyway..!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply