Select values from a column based on priority

Question

I got a table like this

AMID     Entry
------- ---------
1000     MARS
1001     JUPITER
1002     SATURN
1003     VENUS
1003     SATURN
1004     NEPTUNE
1004     SATURN
1005     JUPITER
1005     MARS

Now I want to extract the DISTINCT AMID values with particular ENTRY values. The end table should be like this

 AMID     Entry
-------- --------
  1000    MARS
  1001    JUPITER
  1002    SATURN
  1003    VENUS
  1004    SATURN
  1005    MARS

The condition to select ENTRY values is,

It has to take the values based on this priority :

1. VENUS
2. MARS
3. JUPITER
4. SATURN
5. NEPTUNE

So If any AMID has both VENUS and MARS, it should take VENUS
MARS and SATURN, it should take MARS
NEPTUNE and JUPITER, it should take JUPITER.(select by priority).

Answer 1

You can do it with ranking functions (if you're using SQL Server 2008):

DECLARE @t TABLE (AMID INT, Entry VARCHAR(100))
INSERT @t 
VALUES
(       1000                   ,'MARS'),
(       1001                   ,'JUPITER'),
(       1002                   ,'SATURN'),
(       1003                   ,'VENUS'),
(       1003                   ,'SATURN'),
(       1004                   ,'NEPTUNE'),
(       1004                   ,'SATURN'),
(       1005                   ,'JUPITER'),
(       1005                   ,'MARS')

;WITH a AS(
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY AMID ORDER BY 
                CASE Entry 
                    WHEN 'VENUS' THEN 0
                    WHEN 'MARS' THEN 1
                    WHEN 'JUPITER' THEN 2
                    WHEN 'SATURN' THEN 3
                    WHEN 'NEPTUNE' THEN 4
                END) num
    FROM    @t
)

SELECT  AMID, Entry
FROM    a
WHERE   num = 1

Answer 2

try this:

;WITH CTE AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY AMID ORDER BY T.PRIORITY) AS ROWNUM
FROM PLANETS P
JOIN
    (
    SELECT 1 PRIORITY, 'VENUS' AS NAME UNION ALL
    SELECT 2, 'MARS'  UNION ALL
    SELECT 3, 'JUPITER'  UNION ALL
    SELECT 4, 'SATURN'  UNION ALL
    SELECT 5, 'NEPTUNE')T
ON T.NAME=P.[ENTRY]
)SELECT AMID,[ENTRY] FROM CTE WHERE ROWNUM=1

SQL Fiddle demo

Answer 3

Why don't you just put the entry values in their own table:

CREATE TABLE entry_value
(
    entry VARCHAR(10) NOT NULL PRIMARY KEY,
    value INT NOT NULL
);

INSERT INTO entry_value VALUES
    ('VENUS', 0),
    ('MARS', 1),
    ('JUPITER', 2),
    ('SATURN', 3),
    ('NEPTUNE', 4)    
;

Then finding what you want is trivial:

SELECT
    a.amid,
    (SELECT entry FROM entry_value where value = MIN(e.value)) AS entry
FROM amid a
JOIN entry_value e
ON e.entry = a.entry
GROUP BY a.amid
ORDER BY a.amid;

And as an added bonus, you can add a foreign key to the entry column, so people can't put junk like 1000, 'PLUTO' in there.

Answer 4

try this MySQL query:

SELECT AMID, (CASE MIN(Entry) WHEN 1 THEN "VENUS"
                              WHEN 2 THEN "MARS"
                              WHEN 3 THEN "JUPITER"
                              WHEN 4 THEN "SATURN"
                              WHEN 5 THEN "NEPTUNE"
              END) AS Entry
FROM (
       SELECT AMID, (CASE Entry WHEN "VENUS" THEN 1
                                WHEN "MARS" THEN 2
                                WHEN "JUPITER" THEN 3
                                WHEN "SATURN" THEN 4
                                WHEN "NEPTUNE" THEN 5
                     END) AS Entry
       FROM table_name
      ) a
GROUP BY AMID;