Selecting Distinct From Multiple Tables

Question

Post reply

Selecting Distinct From Multiple Tables

Viewing 10 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Hrhb.mail Old Hand Points: 334 More actions · Answer 1

rn=1

What is it?

Which values of NewId, CatId and NewDate do you want when there are multiples?

Related NewTitle with that fields

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

Hrhb.mail (6/4/2014)
rn=1
What is it?

That is the alias he used for ROW_NUMBER.

Which values of NewId, CatId and NewDate do you want when there are multiples?

Related NewTitle with that fields[/quote]

But your output has only 1 of those values. The data has multiple matches. You mentioned wanting a distinct on a given column. You can't do that but you can select a given rows values which is what Luis did.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

rn is the column that I added with the value of ROW_NUMBER() OVER(PARTITION BY n.NewTitle ORDER BY n.NewId).

If you need to know what ROW_NUMBER() does, read the following: http://technet.microsoft.com/en-us/library/ms186734(v=sql.105).aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hrhb.mail Old Hand Points: 334 More actions · Answer 4

WITH CTE AS(

SELECT ROW_NUMBER() OVER(PARTITION BY n.NewTitle ORDER BY n.NewId) rn,

n.NewId,

n.NewDate,

n.NewTitle,

nrc.NrcCategoryIdFk

FROM dbo.Category c

JOIN NewsRelCategory nrc ON c.CatId = nrc.NrcCategoryIdFk

JOIN dbo.News n ON nrc.NrcNewsIdFk = n.NewId

)

SELECT NewId,

NewTitle,

NewDate,

NrcCategoryIdFk

FROM CTE

WHERE rn = 1

It's not work well

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

My magical crystal ball can't find the error. Could you share it with us?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hrhb.mail Old Hand Points: 334 More actions · Answer 6

My magical crystal ball can't find the error. Could you share it with us?

I need to add

Where CatId=1 (or etc.)

It causes an error

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Hrhb.mail (6/4/2014)
My magical crystal ball can't find the error. Could you share it with us?
I need to add
Where CatId=1 (or etc.)
It causes an error

Where are you adding your conditions? Can you post the code that gives you an error?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hrhb.mail Old Hand Points: 334 More actions · Answer 8

Where are you adding your conditions? Can you post the code that gives you an error?

Thanks, I solved it.

This is my final stored procedure:

Create PROCEDURE [dbo].[NewsPageWise]

@PageIndex INT = 1

,@PageSize INT = 100

,@PageCount INT OUTPUT,

@CatId int

AS

BEGIN

SET NOCOUNT ON;

WITH CTE AS(

SELECT ROW_NUMBER() OVER(PARTITION BY n.NewTitle ORDER BY n.NewId) RowNumber,

n.NewId,

n.NewDate,

n.NewTitle,

nrc.NrcCategoryIdFk

FROM dbo.Category c

JOIN NewsRelCategory nrc ON c.CatId = nrc.NrcCategoryIdFk

JOIN dbo.News n ON nrc.NrcNewsIdFk = n.NewId

Where NrcCategoryIdFk=@CatId

)

SELECT NewId,

NewTitle,

NewDate,

NrcCategoryIdFk

INTO #Results

FROM CTE

WHERE RowNumber = 1

DECLARE @RecordCount INT

SELECT @RecordCount = COUNT(*) FROM #Results

SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))

PRINT @PageCount

SELECT * FROM #Results

WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results

END

When I excute it in Visual Studio I catch an error:

Invalid column name 'Row Number'

How can I solve it?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Hrhb.mail (6/5/2014)
Where are you adding your conditions? Can you post the code that gives you an error?
Thanks, I solved it.
This is my final stored procedure:
Create PROCEDURE [dbo].[NewsPageWise]
@PageIndex INT = 1
,@PageSize INT = 100
,@PageCount INT OUTPUT,
@CatId int
AS
BEGIN
SET NOCOUNT ON;
WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY n.NewTitle ORDER BY n.NewId) RowNumber,
n.NewId,
n.NewDate,
n.NewTitle,
nrc.NrcCategoryIdFk
FROM dbo.Category c
JOIN NewsRelCategory nrc ON c.CatId = nrc.NrcCategoryIdFk
JOIN dbo.News n ON nrc.NrcNewsIdFk = n.NewId
Where NrcCategoryIdFk=@CatId
)
SELECT NewId,
NewTitle,
NewDate,
NrcCategoryIdFk
INTO #Results
FROM CTE
WHERE RowNumber = 1
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM #Results
SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
PRINT @PageCount
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
When I excute it in Visual Studio I catch an error:
Invalid column name 'Row Number'
How can I solve it?

You forgot to add [rownumber] to the temp table:

SELECT NewId,

NewTitle,

NewDate,

NrcCategoryIdFk,

rownumber

INTO #Results

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Hrhb.mail Old Hand Points: 334 More actions · Answer 10

Hrhb.mail

Old Hand

Points: 334

June 5, 2014 at 7:25 am

#1719212

I fixed it.

Thanks every one that helped me:-)