How to list the record orderby id when selecting distinct

[chock]

Hai ,

i am getting the correct result for the below query

rs.Open "SELECT DISTINCT RepTL From PEList WHERE RepFL='" & CriteriaName & "'", Cnn, adOpenStatic, adLockReadOnly, adCmdText

but the thing is i need to display the record in the Order by RepId, but when i add RepId in the query its shows more records and not the distinct RepTL when i write as below

rs.Open "SELECT DISTINCT RepTL,RepId From PEList WHERE RepFL='" & CriteriaName & "'", Cnn, adOpenStatic, adLockReadOnly, adCmdText

what is the way to display the records order by Id and the RepTL should be distinct.

Kindly suggest me.

Thank you,
Chock.

[mnemtsas]

Hai ,

i am getting the correct result for the below query

rs.Open "SELECT DISTINCT RepTL From PEList WHERE RepFL='" & CriteriaName & "'", Cnn, adOpenStatic, adLockReadOnly, adCmdText

but the thing is i need to display the record in the Order by RepId, but when i add RepId in the query its shows more records and not the distinct RepTL when i write as below

rs.Open "SELECT DISTINCT RepTL,RepId From PEList WHERE RepFL='" & CriteriaName & "'", Cnn, adOpenStatic, adLockReadOnly, adCmdText

what is the way to display the records order by Id and the RepTL should be distinct.

Kindly suggest me.

Thank you,
Chock.

How can you order by RepID and have the DISTINCT clause on RepTL? Lets say you have two records with RepTL="foo", one with RepID=8 and the other with RepID=12. How does the distinct query know what RepID to use? So, of course it will put a record for both, and thus your distinct clause is rendered useless.

I think you need to rethink what you are trying to do.

Mark

[chock]

Hai Mark,

I know what i am doing is not correct, should i use subquery or what, can u suggest me.

Thank you,
chock.

[mnemtsas]

Hi There,

Try doing it manually,

Something like:

Select RepTL,RepID From PEList WHERE RepFL='foo' ORDER BY RepTL ASC, RepID DESC;

Then loop through the record set taking note of when RepTL changes and recording RepID which is the highest ID (or change the sort order). Something like:

strLastRepTL=""
Do
  if strLastRepTL<>rs.fields("RepTL").value then
    debug.print "New LastRepTL found, RepID-" & rs.fields("RepID").value
    strLastRepTL=rs.fields("RepTL").value
  endif
  rs.MoveNext
Loop until rs.eof

Cheers,

Mark