i am using distinct keyword still its showing duplicate values

Question

0.00/5 (No votes)

See more:

SQL

SELECT DISTINCT facno,fbalance,fdate from MAINTRAN where MAINTRAN.fbankcode='" & "010" & "'" & _
                                         "and MAINTRAN.fdate<='" & Format(prdate, "MM/dd/yyyy") & "' " & _
                                         "and MAINTRAN.fbranchcode='" & "01" & "' "

i used three fields facno,fbalance,fdate but amoung three values i dont want to display facno duplicate but its still displaying duplicate facno please help me to solve this issue.

Posted 23-Nov-14 20:46pm

lakshjoshi

Updated 23-Nov-14 20:48pm

King Fisher

v2

Add a Solution

Comments

Shweta N Mishra 24-Nov-14 2:47am

post your sample records of results

lakshjoshi 24-Nov-14 3:11am

fbalance
fdate
facno
10,000.00
21/12/2013
010001801000007
15,000.00
21/01/2014
010001801000007
20,000.00
15/01/2014
010001801000007
29,550.00
21/01/2014
010001801000007
34,550.00
21/02/2014
010001801000007
4,000.00
21/12/2013
010001801000008
8,000.00
21/12/2013
010001801000008
12,000.00
21/01/2014
010001801000008
16,000.00
21/02/2014
010001801000008
50,000.00
22/11/2012
010001801000009
100,000.00
22/12/2012
010001801000009
70,000.00
22/01/2014
010001801000010
80,000.00
22/02/2014
010001801000010
1,000.00
23/11/2013
010001801000012
1,500.00
23/12/2013
010001801000012
2,000.00
23/01/2014
010001801000012
2,500.00
23/02/2014
010001801000012
3,000.00
23/03/2014
010001801000012
3,500.00
23/04/2014
010001801000012
500.00
23/01/2014
010001801000013

King Fisher 24-Nov-14 2:50am

show your Sample data's

lakshjoshi 24-Nov-14 3:11am

fbalance

fdate
facno
10,000.00
21/12/2013
010001801000007
15,000.00
21/01/2014
010001801000007
20,000.00
15/01/2014
010001801000007
29,550.00
21/01/2014
010001801000007
34,550.00
21/02/2014
010001801000007
4,000.00
21/12/2013
010001801000008
8,000.00
21/12/2013
010001801000008
12,000.00
21/01/2014
010001801000008
16,000.00
21/02/2014
010001801000008
50,000.00
22/11/2012
010001801000009
100,000.00
22/12/2012
010001801000009
70,000.00
22/01/2014
010001801000010
80,000.00
22/02/2014
010001801000010
1,000.00
23/11/2013
010001801000012
1,500.00
23/12/2013
010001801000012
2,000.00
23/01/2014
010001801000012
2,500.00
23/02/2014
010001801000012
3,000.00
23/03/2014
010001801000012
3,500.00
23/04/2014
010001801000012
500.00
23/01/2014
010001801000013

2 solutions

Solution 2

if you select only

SQL

SELECT DISTINCT facno from MAINTRAN where MAINTRAN.fbankcode='" & "010" & "'" & _
                                         "and MAINTRAN.fdate<='" & Format(prdate, "MM/dd/yyyy") & "' " & _
                                         "and MAINTRAN.fbranchcode='" & "01" & "' "

You must get the distinct values, But for sure your other two column have different values hence you are getting that kind of result.

Posted 23-Nov-14 21:10pm

Shweta N Mishra

Add a Solution

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

OriginalGriff · Accepted Answer · 2014-11-23T21:07:00

DISTINCT selects values which are different in all columns, not just the first. So if your data is:

id Name  x    y
1  Joe   1    2
2  Joe   1    3
3  Joe   1    3

Then

SQL

SELECT DISTINCT [Name], x, y FROM MyTable

Will return:

Joe   1    2
Joe   1    3

If you want to only return one row with "Joe" in, then you need to think more carefully and decide which of the rows that will be otherwise identical you are interested in!
SQL can't do that - it doesn't know which of the three "Joe" rows you do want.

i am using distinct keyword still its showing duplicate values

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

i am using distinct keyword still its showing duplicate values

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us