Record selection advise needed

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I have a winform database app that loads one record at a time. I use the
following two sqls to allow users to scroll to next and previous records;

MoveNext

SELECT TOP 1 <field list>
FROM tblClients
WHERE (ID > ?)
ORDER BY ID


Move Prev

SELECT TOP 1 <field list>
FROM tblClients
WHERE (ID < ?)
ORDER BY ID DESC

This works fine on a unique id and when records are scrolled in id order.

The client now wants the records scrolled in company type+company name
order. The problem is that niether company type nor company name is unique
and it is difficult to select next record in sequence without a unique id as
to which is the next record. How can I achieve the scrolling in company
type+company name order one record at a time?

Thanks

Regards
 
John,

In my idea do you first have to determ what is the next in those
collections,

Cor
 
Cor said:
John,

In my idea do you first have to determ what is the next in those
collections,
Click to expand...

I'm not sure if it means the same as I was also about to write:
If you have

company type: 1, name: Ligthert
company type: 1, name: Ligthert

which is the first and which the second? That's what you, John, have to
determine first.


Armin
 
Add the primary key or a unique key to sort order:

MoveNext:

SELECT TOP 1 <field list>
FROM tblClients
WHERE
CompanyType > @LastReadCompanyType
OR (
CompanyType = @LastReadCompanyType
AND CompanyName > @LastReadCompanyName
)
OR (
CompanyType = @LastReadCompanyType
AND CompanyName = @LastReadCompanyName
AND CompanyID > @LastReadCompanyID
)
ORDER BY
CompanyType, CompanyName, CompanyID


MovePrev:

SELECT TOP 1 <field list>
FROM tblClients
WHERE
CompanyType < @LastReadCompanyType
OR (
CompanyType = @LastReadCompanyType
AND CompanyName < @LastReadCompanyName
)
OR (
CompanyType = @LastReadCompanyType
AND CompanyName = @LastReadCompanyName
AND CompanyID < @LastReadCompanyID
)
ORDER BY
CompanyType DESC, CompanyName DESC, CompanyID DESC


Regards

Jesús López
 
Back
Top