Distinct selection query ms sql 2005

Question

5.00/5 (2 votes)

See more:

SQL-Server-2005

SQL

.NET

, +

I have a table and records as like this:
Table name: tbl_post
Columns: Id, Title,ep

records in the table:

SQL

Id  Title     Ep
1   Horror    4
2   Reality   6
3   Sci-Fi    3
4   Horror    7
5   Romance   8
6   Horror    2

and so on...
I just want to show it as

SQL

Id  Title     Ep
1   Horror    4
2   Reality   6
3   Sci-Fi    3
5   Romance   8

and so on (eliminating all the other repeating title)

I have tried distinct but no use.
Please suggest me some queries (ms-sql)

Posted 22-Apr-13 20:56pm

ujju.1

Add a Solution

2 solutions

Solution 1

Select * From tbl_post Where Title in(Select Distinct(Title) From tbl_post )

Posted 22-Apr-13 21:12pm

KManishS

Comments

ujju.1 23-Apr-13 3:14am

Not working dear. The query will match with repeating titles also and show all the records.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-04-22T21:44:00

Solution 2

Hi,

Try this...

SQL

with Post 
AS
(
  SELECT Id, Title,ep,  
     row_number() over (partition by Title order by Id) as rowid
  from tbl_post
)
SELECT *
FROM Post where rowid = 1;

Regards,
GVPrabu

Posted 22-Apr-13 21:44pm

gvprabu

Updated 22-Apr-13 22:29pm

v2

Comments

ujju.1 23-Apr-13 4:00am

its showing error: "Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Id'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'SELECT'."

But might work with some modifications...will try.

ujju.1 23-Apr-13 4:13am

thanx gvpravu...got it what i wanted

with Post
AS
(
SELECT Id, Title,ep,
row_number() over (partition by Title order by Id) as rowid
from tbl_post
)
SELECT *
FROM Post where rowid = 1;

gvprabu 23-Apr-13 4:22am

Hi,
In my machine I don't have SQL Server So I used Note pad for Frame this Query... Okie fine If u got the logic thats enof. have a nice day