select distinct rows only need help pls

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select distinct rows only need help pls

Author

Topic

nasman
Starting Member

15 Posts

Posted - 2011-12-19 : 23:17:09

my table looks like this

col1 col2 col3
row1 a b c
row2 a b d
row3 d e f
row4 g h i

col1 transaction number
col2 account id
col3 represent transaction time

the problem is row 1 and 2 is a duplicate account with different transaction time, i want to select only distinct col1 and 2 regardless of the col3.

nasman
Starting Member

15 Posts

Posted - 2011-12-19 : 23:19:30

the result should be

col1 col2
a b
d e
g h

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 00:08:29

[code]
SELECT DISTINCT TransactionNumber,AccountID
FROM YourTable
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nasman
Starting Member

15 Posts

Posted - 2011-12-20 : 01:02:31

if i do that the result will be like this

d e
g h

i tried this query but still doesnt work

select t1.col1, t1.col2
from table1 t1
where col2 = (select max(col3) from table1 t2 where t1.col1 = t2.col2)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-20 : 01:12:17

quote:
Originally posted by nasman

if i do that the result will be like this

d e
g h

i tried this query but still doesnt work

select t1.col1, t1.col2
from table1 t1
where col2 = (select max(col3) from table1 t2 where t1.col1 = t2.col2)

Did you try the query Visakh posted ?

KH
[spoiler]Time is always against us[/spoiler]

nasman
Starting Member

15 Posts

Posted - 2011-12-20 : 02:16:11

@khatan

yes tried it already,

the result was like this

col1 col2
d e
g h

-----------

what my desired result is

a b
d e
g h

-----------
the situation:

transaction 'a b' has a duplicate but the duplicate has a different transaction time. i just want to select distinct 'a b' regardless of its transaction time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 03:21:18

quote:
Originally posted by nasman

@khatan

yes tried it already,

the result was like this

col1 col2
d e
g h

-----------

what my desired result is

a b
d e
g h

-----------
the situation:

transaction 'a b' has a duplicate but the duplicate has a different transaction time. i just want to select distinct 'a b' regardless of its transaction time

hmm...then i'm sure there're some other things related to this which you've not yet specified to us
taking DISTINCT wont cause any rows to disappear
as per you posted sample data, my query should give you

a b
d e
g h

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nasman
Starting Member

15 Posts

Posted - 2011-12-20 : 03:44:28

i just simplified it (the sample table), sorry if it became ambiguous

here is my code without distinct

select t1.corp_id,
t1.branch_id,
t2.trans_date_time,
t2.trans_no,
t2.sale_amt,
t2.points
t2.account_id

from branch t1,
transact t2

where t1.branch_id = t2.branch_id
and t2.trans_date = '2010-05-23'
order by t2.account_id desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 04:26:32

hmm... what does the other fields contain? and is the relationship between tables one to one? best thing would be to post some data from the tables (about 5 rows each) and then provide sample output you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources