How to write a query to get only one record?

Question

5.00/5 (1 vote)

See more:

I have a table called advisor_ranks, in that table three columns are there, UserId, RankNo and AchievedDate, but there is not PK or FK. In that table one UserId contains several records, Now I want to write a query to get perticular records like one userid one records. So can anyone help me to get this query?

I tried this

SQL

select * from advisor_ranks group by userid,rankno,achieveddate

but result is like same like

SQL

select * from advisor_ranks

Posted 10-Sep-13 19:20pm

Aboobakkar Siddeq D U

Add a Solution

Comments

[no name] 11-Sep-13 1:24am

what you actually want...because this will generate wrong output in every condition..

chaau 11-Sep-13 1:34am

You need to group by UserId only. But you need to decide which values you want for other columns, like MAX(), MIN(), AVG(). You may also want the record with the last AchievedDate. In this case the approach will be slightly different

1 solution

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)

OriginalGriff · Accepted Answer · 2013-09-10T19:37:00

We can't help that much, based on that limited information.
But I think you need to look at exactly what you are trying to return, and change teh way you are doing queries.

The first thing to do is to stop issuing wildcard selects:

SQL

SELECT * FROM MyTable ...

Partly because it is inefficient in that you may not in future want all the columns (what happens if you add an "Image" column - the return size grows enormously), and partly because it helps you to focus on what you are trying to achieve.

When you say:

SQL

SELECT * FROM advisor_ranks GROUP BY userid, rankno, achievedate

It is the equivelant of saying:

SQL

SELECT userid, rankno, achievedate FROM advisor_ranks GROUP BY userid, rankno, achievedate

Which is exactly the same as saying:

SQL

SELECT userid, rankno, achievedate FROM advisor_ranks

because it returns the same rows, given that the GROUP BY clause specifies which rows mush be identical in order to group them together. Since you specify all your rows in the GROUP BY clause and no two rows can ever be identical, it is redundant.

In order to use GROUP BY and return a single row for each userid is simple to start with:

SQL

SELECT userid FROM advisor_ranks GROUP BY userid

But you have probably noticed that SQL Complains if you try to return any further columns because they are "not aggregate functions or included in the GROUP BY statement" - which is SQL talk for "I don't know how to combine the different values". Think about it: if you have two rows for userid 6:

userid   rankno  achieveddate
   6        4     2013-01-01
   6        3     2013-03-17

Then which data did you want? Which rank? Which date? SQL can't combine them for you, so you have to specify it:

SQL

SELECT userid, SUM(rankno), MAX(achievedate) FROM advisor_ranks GROUP BY userid

Will work - but only you can decide what you actually need to return.