Select records based on column priority – This article will take you through the common SQL errors that you might encounter while working with sql, sqlite, select. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
First of all, the title of this question is horrible, but I didn’t find a better way to describe my issue.
There’s probably a very easy way to do this, but I couldn’t figure it out. This is very similar to this question, but I’m running on sqlite3 (iOS) so I suspect my options are much more limited.
I have a table with product records. All records have an ID (note: I’m not talking about the row ID, but rather an identification number unique to each product). Some products may have two entries in the table (both with the same ID). The only difference would be in a special column (let’s say column COLOUR can be either RED or GREEN).
What I want to do is create a list of unique products based on the value of COLOUR, with priority to GREEN if both GREEN and RED records exist for the same product.
In short, if I have the following case:
id PRODUCT ID COLOUR 1 1001 GREEN 2 1002 GREEN 3 1002 RED 4 1003 RED
I would like my SELECT to return the rows 1, 2 and 4. How can I achieve this?
My current approach is to have separate tables, and do the join manually, but obviously this is a very bad idea..
Note: I’ve tried to use the same approach from here:
SELECT * FROM xx WHERE f_COLOUR = "GREEN" UNION ALL SELECT * FROM xx WHERE id not in (SELECT distinct id FROM xx WHERE f_COLOUR = "GREEN");
But the result I’m getting is rows 1,2,3,4 instead of just 1,2,4. What am I missing?
Edit: One more question please: how can this be made to work with a subset of records, ie. if instead of the entire table I wanted to filter some records?
For example, if I had something like SELECT * FROM table WHERE productID LIKE “1%” … how can I retrieve each unique product, but still respecting the colour priority (GREEN>RED)?
Your query is nearly correct. Just use
PRODUCTID and not
SELECT * FROM xx WHERE f_COLOUR = "GREEN" UNION SELECT * FROM xx WHERE PRODUCTID not in (SELECT PRODUCTID FROM xx WHERE f_COLOUR = "GREEN");
SELECT * FROM xx WHERE COLOUR = 'GREEN' UNION SELECT * FROM xx WHERE P_Id not in (SELECT P_Id FROM Persons WHERE COLOUR = 'GREEN');
I just want to offer that you can do this with a group by:
select (case when sum(case when colour = 'Green' then 1 else 0 end) > 0 then max(case when colour = 'Green' then id end) else max(case when colour = 'Red' then id end) end) as id, product_id (case when sum(case when colour = 'Green' then 1 else 0 end) > 0 then 'Green' else 'Red' end) as colour from t group by product_id
You can have it like this
WITH PriorityTable AS ( SELECT T.*, ROW_NUMBER() OVER (PARTITION BY T.ID ORDER BY PT.ColorPriority ) PriorityColumn FROM XX AS T INNER JOIN ( SELECT 'RED' AS f_COLOUR , 1 AS ColorPriority UNION SELECT 'GREEN' AS f_COLOUR , 2 AS ColorPriority ) AS PT ON T.f_COLOUR = PT.f_COLOUR ) SELECT * FROM PriorityTable WHERE PriorityColumn = 1
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.