CODEWITHSUNDEEP
asp.netsql
Lv1983
Lv1983

Reputation: 87

SQL Select Query Asp.Net

I have a product page on a webpage that shows categories of products. This is done with a listview populated from a database. The issue that I have is that the main supplier has demanded that their products are first in the category list. So what I need to do is run a query that will return the results, display those two categories first and then display the rest alphabetically.

So I've been trying to do this using a UNION ALL query like this:

SELECT cat, cat_id, image FROM prod_categories WHERE cat_id = 19 OR cat_id = 65 
UNION ALL 
SELECT cat, cat_id, image FROM prod_categories WHERE cat_id <> 19 AND cat_id <> 65

I thought with a union like this it would display the results of the first select query first, but it's not doing that.

I can add an 'order by cat' clause on the end, but obviously that only displays them in the correct order if the two categories I want to display come first alphabetically, which they don't.

If anyone has any ideas how to do this it would be greatly appreciated.

Thanks

Upvotes: 0

Views: 187

Answers (3)

Clockwork-Muse
Clockwork-Muse

Reputation: 13046

Don't hard-code this into your query. What happens when the next supplier wants to come second? Or last? For that matter, you may want to list categories in some sort of "group", anyways.

Instead, you should be using an ordering table (or multiple). Something simple to get you started:

CREATE TABLE Category_Order (categoryId INTEGER   -- fk to category.id, unique
                             priority INTEGER)  -- when to display category

Then you want to insert the values for the current "special" categories:

 INSERT INTO Category_Order (categoryId, priority) VALUES (19, 2147483647), (65, 0)

You'll also need an entry for rows that are not currently prioritized:

INSERT INTO Category_Order (categoryId, priority) 
SELECT catId, -2147483648
FROM prod_categories
WHERE catID NOT IN (19, 65)

Which can then be queried like this:

SELECT cat, cat_id, image
FROM prod_categories
JOIN Category_Order
  ON category_id = cat_id
ORDER BY priority DESC, cat

If you write a small maintenance program for this table, you can then push re-ordering duties off onto the correct business department. Reordering of entries can be accomplished by splitting the difference between existing entries, although you'll want a procedure to re-distribute if things get too crowded.

Note that, in the event your db supports a clause like ORDER BY priority NULLS LAST, the entries for non-prioritized categories are unnecessary, and you can simply LEFT JOIN to the ordering table.

Upvotes: 0

user2989408
user2989408

Reputation: 3137

Try something like this.

SELECT cat, cat_id, image, 1 as [srt] 
FROM prod_categories WHERE cat_id = 19 OR cat_id = 65 

UNION ALL 

SELECT cat, cat_id, image, 2 as [srt] 
FROM prod_categories WHERE cat_id <> 19 AND cat_id <> 65 

ORDER BY srt ASC, cat_id

Upvotes: 0

PulseLab
PulseLab

Reputation: 1579

How about this:

SELECT cat, cat_id, image FROM prod_categories
order by case when cat_id in (19, 65) then 1 else 2 end, cat_id

Cuts out the need to UNION altogether. Might even produce a more efficient execution plan (possibly...).

(using Transact-SQL for SQL Server - the exact syntax may have to be tinkered for MySql etc)

Upvotes: 3

Related Questions