ansaurus

Question

Getting distinct rows from a table using combination of two columns

Answer 1

+1 A:

select * from table where id in (select min(id) from table group by handle, title)

OR

select * from table where id in (select max(id) from table group by handle, title)

Salil 2010-04-07 08:16:56

A more efficent solution can be obtained with OLAP functions (also known as analytic functions) if the environment support them (DB2 and Oracle does), but judging from the tags we are talking about ms-access so your answer is the best possible I think.

Andrea Polci 2010-04-07 08:24:51

@ Salil - I added the primary key and it worked with that, though very slow in execution but worked for me. Thanks alot.

HTMZ 2010-04-07 09:23:29

@Andrea Polci: the MS-ACCESS tag can mean that the front end is Access and the back end datastore is just about any database engine in existence, so your mention of the OLAP functions is potentially helpful, if not to this particular questioner, possibly to someone coming on the question later.

David-W-Fenton 2010-04-07 21:06:37

ansaurus

tags:

views:

answers:

Getting distinct rows from a table using combination of two columns

related questions