unique value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

unique value

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-02-19 : 04:35:34

Dear Friends,

I have the query that are used to fetch the most recently added five content from the database, But i want to display the unique users content, That mean if the user posted 2 content i want to get the one content from that user and the 4 content from the other 4 unique user.

QUERY

Select TOP (5) c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif' ORDER BY c.DateAdded DESC

The query displaying 5 recent records, But some time if the user posted 2 content continusoly , i don't want to display the same users content.

So u.username must be unique.

Please modify the query for me, THANKS IN ADVANCE

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-19 : 04:44:01

Select TOP (5) c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join (select distinct * from subtext_Config) con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif' ORDER BY c.DateAdded DESC

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-19 : 04:48:35

look at row_number()

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-02-19 : 09:25:34

Hi bklr,

Thank you for your Query, I tried your query But it showing error message

Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.

Please help me to get the expected output,

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 09:30:26

[code]
SELECT all columns except seq2 and seq1
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS Seq2,all columns except seq1
FROM
(
Select ROW_NUMBER() OVER(PARTITION BY con.UserName ORDER BY c.ID DESC) AS Seq1, c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif' ORDER BY c.DateAdded DESC
)t
WHERE t.Seq1=1
)r
WHERE Seq<=5
[/code]

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-02-19 : 09:34:00

Dear Visakh16,

Thank you for your help, But it showing below ERROR message

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'seq2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'all'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'ORDER'.

Please help me to fix this.

Thanks for all your helps, thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 09:35:03

show your query

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 09:45:44

I bet you haven't replaced "all columns except seq1" from Visakh's post. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 09:47:55

quote:
Originally posted by sakets_2000

I bet you haven't replaced "all columns except seq1" from Visakh's post. :)

thats why i asked him to post the query used

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-02-19 : 10:17:40

I have used the same query as you posted visakh,

I dont understand that, Sorry for confusion.

Could you please modify and help me

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 10:22:24

Replace

<all columns except seq1>

by true column names.
Also, I think you'd have to remove that order by from your derived table.

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-02-19 : 10:35:41

Friends,

PLease find the below query i have used

SELECT all columns except seq2 and seq1
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS Seq2,c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif' ORDER BY c.DateAdded DESC
FROM
(
Select ROW_NUMBER() OVER(PARTITION BY con.UserName ORDER BY c.ID DESC) AS Seq1, c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif' ORDER BY c.DateAdded DESC
)t
WHERE t.Seq1=1
)r
WHERE Seq<=5

I am getting error message,

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'seq2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'ORDER'.

Thank you so much for all your helps, Sorry for not understanding.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 10:38:43

You still haven't changed this....
SELECT all columns except seq2 and seq1

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 10:43:52

You also messed up the 3rd line from the top, You added from,order clause there.

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-02-19 : 10:44:34

Please find my query below

SELECT c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif' ORDER BY c.DateAdded DESC
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS Seq2,c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif' ORDER BY c.DateAdded DESC
FROM
(
Select ROW_NUMBER() OVER(PARTITION BY con.UserName ORDER BY c.ID DESC) AS Seq1, c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif' ORDER BY c.DateAdded DESC
)t
WHERE t.Seq1=1
)r
WHERE Seq<=5

I am getting below Error message

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'ORDER'.

Thanks in advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 10:48:03

May be this,

SELECT c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname 
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS Seq2,c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname 
FROM
(
Select ROW_NUMBER() OVER(PARTITION BY con.UserName ORDER BY c.ID DESC) AS Seq1, c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif'  
)t
WHERE t.Seq1=1
)r
WHERE Seq<=5

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-02-19 : 10:48:49

I have modified the query

SELECT c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname except Seq2 and Seq1
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS Seq2,c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname
FROM
(
Select ROW_NUMBER() OVER(PARTITION BY con.UserName ORDER BY c.ID DESC) AS Seq1, c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif' ORDER BY c.DateAdded DESC
)t
WHERE t.Seq1=1
)r
WHERE Seq<=5

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Seq2'.
Msg 1033, Level 15, State 1, Line 8
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Still i am getting the error, Help me what i am missing

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 10:49:11

Remove the ORDER BY c.DateAdded DESC
from 1st and 3rd lines...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 10:50:54

OK..I see you have done that...but what about this...

u.firstname except Seq2 and Seq1

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-02-19 : 10:52:13

I have used the query as Sakets posted, But still i am getting the error

Please find the below Error message

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.Title" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.Text" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.Description" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "u.picture" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.EntryName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "con.UserName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "u.firstname" could not be bound.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Seq'.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "c.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "c.Title" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "c.Text" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "c.Description" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "u.picture" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "c.EntryName" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "con.UserName" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "u.firstname" could not be bound.

Thanks for all your help,.

And please help me to get the expected output.

Thanks in Advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 10:55:36

Correct the synonyms there, I just pasted from what your provided. The references to the derived tables isn't right.



SELECT r.ID, r.Title,r.Text,r.Description,r.picture as picture,r.EntryName,r.UserName,r.firstname 
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS Seq2,t.ID, t.Title,t.Text,t.Description,t.picture as picture,t.EntryName,t.UserName,t.firstname 
FROM
(
Select ROW_NUMBER() OVER(PARTITION BY con.UserName ORDER BY c.ID DESC) AS Seq1, c.ID, c.Title,c.Text,c.Description,u.picture as picture,c.EntryName,con.UserName,u.firstname from dbo.wlbuserfls u inner join subtext_Config con on u.username = con.UserName inner join subtext_Content as c on con.BlogId = c.BlogId where u.picture !='images/defaultphoto.gif'  
)t
WHERE t.Seq1=1
)r
WHERE Seq<=5

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources