December 29, 2010 at 7:52 am
Hi ,
with the below table with sample data , I have 10k records like belowise
isbn GROUP FORMAT
9874562014789 1 PDF
9874562014789 1 word
9874562014790 2 PDF
9874562014790 2 XLS
I have to with draw the data from the table,
conditions:
when a single isbn having the both PDF and Word format
with 1 Group only
then we have to drop any one of the format and remaining data has to display continiously with out looping and if conditions
December 29, 2010 at 7:56 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2010 at 8:44 am
try this:
use top operator to get one format and delete the other rows
sample code:
delete from tablename where format not in (select top 1 format from tablename
group by isbn,group,format)
let me know your thoughts
Kumar
December 29, 2010 at 9:42 am
First, let's put the data into a table so that it can actually be worked with:
And here are 3 different methods to extract the ISBN and Group for just the ones that have both a PDF and Word format.
You'll have to test to see which performs better for you, but I suspect that the third method would be the best.
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
DECLARE @test-2 TABLE (isbn char(13),
[Group] tinyint,
Format varchar(10),
UNIQUE (Format, isbn, [Group]));
INSERT INTO @test-2
SELECT '9874562014789', 1, 'PDF' UNION ALL
SELECT '9874562014789', 1, 'word' UNION ALL
SELECT '9874562014790', 2, 'PDF' UNION ALL
SELECT '9874562014790', 2, 'XLS';
And here are 3 different methods to extract the ISBN and Group for just the ones that have both a PDF and Word format.
You'll have to test to see which performs better for you, but I suspect that the third method would be the best.
;
WITH CTE AS
(
-- get a distinct list of the ISBN and GROUPs
SELECT t.isbn, t.[Group]
FROM @test-2 t
GROUP BY t.isbn, t.[Group]
), CTE2 AS
(
-- get a comma-delimited list of all of the formats
SELECT CTE.isbn, CTE.[Group],
Formats = STUFF((SELECT ',' + FORMAT
FROM @test-2 t
WHERE t.isbn = CTE.isbn
AND t.[Group] = CTE.[Group]
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
)
SELECT isbn, [Group]
FROM CTE2
WHERE Formats like '%PDF%'
AND Formats like '%Word%';
;
WITH CTE1 AS
(
SELECT isbn, [Group]
FROM @test-2 t
WHERE Format = 'PDF'
), CTE2 AS
(
SELECT isbn, [Group]
FROM @test-2 t
WHERE Format = 'Word'
)
SELECT CTE1.*
FROM CTE1
JOIN CTE2
ON CTE1.isbn = CTE2.isbn
AND CTE1.[Group] = CTE2.[Group];
;
WITH CTE AS
(
SELECT isbn, [Group], Format,
RN = ROW_NUMBER() OVER (PARTITION BY isbn, [Group] ORDER BY Format)
FROM @test-2 t
WHERE Format in ('PDF','Word')
)
SELECT isbn, [Group]
FROM CTE
WHERE RN = 2;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply