How to write a Query to get single record from Database?

Question

4.00/5 (2 votes)

See more:

I have a table which has multiple records with the same fields, same values, but only one field is different. "VoucherNo" VoucherNo has different values in two records like "9007" and "9852" I want to query to get record which has max value VoucherNo.

My Query is Like This

SQL

select a.userid as [UserId],c.fullname as [Name],CONVERT(VARCHAR(11),a.voucherdate,106) as [VoucherDate],CONVERT(VARCHAR(11),
b.regdate,106) as [JoiningDate],b.rankno as [Rank],d.rankname as [RankName],b.branchuserid as [Branch],c.address as [Address],
a.voucherno,a.tds as [TDS],a.othercharges as [OtherCharges],a.netamount as [NetAmount],a.sc as [AdminCharges],a.levelamount 
as [LevelAmount] from vouchers a join users b on a.userid=b.userid join profiles c on b.accountid=c.accountid join ranks d 
on b.rankno=d.rankno where a.voucherdate >= '11/01/2013' and a.voucherdate <= '12/01/2013' and 
a.userid in ('V2W645346','V2W545647','V2W853227','V2W394436','V2W255329','V2W965239')

Here if I use MAX() then it will return only single value, I want record for different users, but condition is it should return one record to each user which has max no in VoucherNo, not multiple. So please help me to solve this problem.

Thank in advance.

Posted 5-Dec-13 19:03pm

Aboobakkar Siddeq D U

Updated 5-Dec-13 19:05pm

Thanks7872

v2

Add a Solution

4 solutions

Solution 2

There are multiple ways the most generic is :

SQL

select top 1 * from ( select ... ) ; your query in the parenthesis

You can also use LIMIT if your database supports it. http://www.w3schools.com/sql/sql_top.asp[^]

Posted 5-Dec-13 19:21pm

Mehdi Gholam

Comments

Aboobakkar Siddeq D U 6-Dec-13 1:34am

Sorry!!! I tried this, but this query is returning totally One record only. But here I want to return one record per each user.

Mehdi Gholam 6-Dec-13 1:39am

Then you need to group by your users and use max() for the data.

Solution 1

If you want the userid wise(max(VoucherNo)) data ,use group by in "VoucherNo" and "userid "

Posted 5-Dec-13 19:20pm

Ganesh Raja

Comments

Aboobakkar Siddeq D U 6-Dec-13 1:33am

No I tried this, but this query is returning same two records for one users. Is there any other way???

Solution 3

Try this

SQL

select  a.userid as [UserId],c.fullname as [Name],a.[VoucherDate],CONVERT(VARCHAR(11),
b.regdate,106) as [JoiningDate],b.rankno as [Rank],d.rankname as [RankName],b.branchuserid as [Branch],c.address as [Address],
a.voucherno,a.tds as [TDS],a.othercharges as [OtherCharges],a.netamount as [NetAmount],a.sc as [AdminCharges],a.levelamount 
as [LevelAmount] 
from users b 
	join (select  a.userid as [UserId],CONVERT(VARCHAR(11),a.voucherdate,106) as [VoucherDate],
				a.voucherno,a.tds as [TDS],a.othercharges as [OtherCharges],a.netamount as [NetAmount],
				a.sc as [AdminCharges],a.levelamount as [LevelAmount], 
				ROW_NUMBER() over (partition by userid order by VoucherNo desc) as Row_no
				from vouchers as a) as a on a.userid=b.userid and Row_no=1 
	Join profiles c on b.accountid=c.accountid join ranks d 
on b.rankno=d.rankno 
where a.voucherdate >= '11/01/2013' and a.voucherdate <= '12/01/2013' and 
a.userid in ('V2W645346','V2W545647','V2W853227','V2W394436','V2W255329','V2W965239')

Posted 5-Dec-13 19:43pm

Shanalal Kasim

Comments

Aboobakkar Siddeq D U 6-Dec-13 1:54am

Sorry!!! This query is not returning any values. Then I did some changes in this query, but no values returned.

Shanalal Kasim 6-Dec-13 2:07am

Please provide your table structure and sample data?

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)

skydger · Accepted Answer · 2013-12-05T20:45:00

Hello, try this one:

SQL

SELECT a.userid as [UserId], c.fullname as [Name], CONVERT(VARCHAR(11), a.voucherdate,106) as [VoucherDate],
       CONVERT(VARCHAR(11), b.regdate,106) as [JoiningDate], b.rankno as [Rank], d.rankname as [RankName],
       b.branchuserid as [Branch], c.[address] as [Address], a.voucherno, a.tds as [TDS],
       a.othercharges as [OtherCharges], a.netamount as [NetAmount], a.sc as [AdminCharges],
       a.levelamount as [LevelAmount]
  FROM vouchers a
  JOIN users b
    ON a.userid = b.userid
  JOIN profiles c
    ON b.accountid = c.accountid 
  JOIN ranks d 
    ON b.rankno = d.rankno
  JOIN ( SELECT userid, MAX(voucherno) AS mx_vno
           FROM vouchers
          WHERE voucherdate BETWEEN '11/01/2013' AND '12/01/2013'
          GROUP BY userid ) AS x
    ON x.userid = b.userid AND
       a.voucherno = x.mx_vno
 WHERE voucherdate BETWEEN '11/01/2013' AND '12/01/2013' AND
       a.userid IN ('V2W645346','V2W545647','V2W853227','V2W394436','V2W255329','V2W965239')

Good luck :)