Recordset

[lazyRascal]

I have a web site that displays items for sell by various sellers. Each seller has multiple items. The items are displayed sorted by price descending.

What I want to do is display the currently selected seller's item's first, sorted by price descending. Then following these items, I want to display ALL items from ALL remaining seller's sorted by price descending.

I tried returning a dummy variable (all zeros) and I wanted to change the value to '1' for all records of the current seller. Then sort by dummy variable DESC and price DESC. However, I can't update the recordset, so this approach won't work.

Can anyone give me any ideas on how I can achive this.

Thank you in advance.

 

[Sheco]

If your database supports the SQL UNION keyword then it might be really simple.
[tt]
SELECT VenderID, ItemID, Price, Description FROM tblCatalog WHERE [highlight]VenderID [red]=[/red] X[/highlight]
[highlight]UNION[/highlight]
SELECT ItemNum, Price, Description FROM tblCatalog WHERE [highlight]VenderID [red]<>[/red] X[/highlight]
[/tt]

So search in the documentation for your DBMS to see if you can use UNION

 

[onpnt]

Just a add-on to Sheco's post. I believe from past readings that the UNION ALL is much more efficient than a straight UNION.

Reasoning after refreshing it myself recently is

a UNION query will filter the data first for uniqueness. If a query requests to return all unique rows as-is, you don't require this extra overhead, so a UNION ALL query is more appropriate.

General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[onpnt]

I should rephrase this...

I believe from past readings that the UNION ALL is much more efficient than a straight UNION.

to

I believe from past readings that the UNION ALL is much more efficient than a straight UNION in your case

Although the UNION all is more efficient it doesn't mean you should use it all the time. If you do and require duplicate records to be returned from your queries it of course will not with a UNION ALL

Still working on my first cup of coffee over here and apologise for the blabber



General FAQ faq333-2924
5 steps to asking a question faq333-3811