Query to return distinct rows

Question

0.00/5 (No votes)

See more:

Hi All

C#

Id  name     date             category   .....
1   Amritha  10/22/2013 8:30  ab         .....
2   Amritha  10/22/2013 8:32  ab         .....
3   raeah    10/22/2013 8.45  bc         .....
4   junu     10/22/2013 8:50  ac         .....
5   Junu     10/22/2013 8:55  ac         .....

this is my table with data
select distinct * from table returns all the rows .I want a query which returns 3 rows

C#

Id  name     date             category   .....
2   Amritha  10/22/2013 8:32  ab         .....
3   raeah    10/22/2013 8.45  bc         .....
5   Junu     10/22/2013 8:55  ac         .....

distinct names with latest other information. How to do this?

Thanks in Advance

Amritha

Posted 22-Oct-13 1:13am

amritha444

Updated 22-Oct-13 1:57am

v3

Add a Solution

3 solutions

Solution 3

Try this query:-

SQL

SELECT Min(Id),name, min(date)
FROM  Table_Name
GROUP BY name

Posted 22-Oct-13 1:50am

TrushnaK

Updated 22-Oct-13 2:48am

v2

Comments

ZurdoDev 22-Oct-13 8:34am

Since you are grouping by ID you'll still get a record for every ID, which the OP does not want.

TrushnaK 22-Oct-13 8:48am

i have updated my query will it work fine...

Solution 2

SQL

Declare @tt table (Id int,name varchar(10),date varchar(20))
insert @tt
Select  1,'Amritha','10/22/2013 8:30'
union all Select 2,'Amritha','10/22/2013 8:32'
union all Select 3,'raeah','10/22/2013 8.45'
union all Select 4,'junu','10/22/2013 8:50'
union all Select 5,'Junu','10/22/2013 8:55'

Select Max(ID) AS ID,Name,Max(Date) AS Date From @tt
Group by Name

Posted 22-Oct-13 1:40am

VeeraAnnapureddy

Comments

OriginalGriff 22-Oct-13 7:53am

Reason for my vote of one: Hard coded values? Oh come on!
If you are going to post a solution, please at least try to make it useful!

ZurdoDev 22-Oct-13 8:33am

That was my first reaction too; however, they then provided the SQL. I think it was there way of testing to make sure there Select came out the way the OP wanted.

ZurdoDev 22-Oct-13 8:34am

The only issue I have with this is MAX(ID) and MAX(Date) may not give the same record. However, the OP was not clear enough as to what exactly was needed.

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)

ZurdoDev · Accepted Answer · 2013-10-22T01:38:00

You can't do that. You can't say distinct and then also get ID 2 for Amritha. How will it know if it should be ID 1 or 2 for Amritha. From your output it almost looks like you actually want the max date.

SQL

SELECT Name, MAX(date) AS LatestDate
FROM table
GROUP BY Name

That will give 1 record per name and whatever the max date is.

If you happen to need the ID of that record as well you can use the above as a derived table and rejoin back to the table to get it.

SQL

SELECT t.Id, x.Name, x.LatestDate
FROM (
   SELECT Name, MAX(date) AS LatestDate
   FROM table
   GROUP BY Name
) x
INNER JOIN table t ON x.Name = t.Name and x.LatestDate = t.date