CODEWITHSUNDEEP
sqlsql-server
pallupz
pallupz

Reputation: 883

How to select the maximum value when the field is in text datatype

I have a table that has data as shown below. The 'Attempt' column has text entries like First, Second, Third and Fourth

entryid User  Attempt
1       1     First
2       1     Second
1       2     First
2       2     Second
3       2     Fourth
4       2     First

The output I need is as below

UsrID   Attempt
1       Second
2       Fourth

As you can see in the case of user 2, even if the latest entry for a user is 'First', since he has an entry for 'Fourth', we need to output Fourth. How can we achieve it?

Currently I'm using four queries to check if a user has any of the entries using EXISTS & NOT EXISTS. I'm then joining their results using UNION. I am also considering assigning each of those statuses a number using CASE WHEN and then taking that new field's maximum. Would anyone have a better solution?

Upvotes: 1

Views: 108

Answers (7)

Luca Calabrese
Luca Calabrese

Reputation: 126

Another approach:

SELECT a.UsrID, a.Attempt
FROM (SELECT * FROM tattempt a
      JOIN (SELECT 1 AS id, 'First' AS value FROM dual
            UNION
            SELECT 2, 'Second' FROM dual
            UNION
            SELECT 3, 'Third' FROM dual
            UNION
            SELECT 4, 'Fourth' FROM dual) v
            ON (a.Attempt = v.value)
       ORDER BY v.id DESC) a
GROUP BY a.UsrID;

where tattempt is the name of your table.

Upvotes: 0

S.Bozzoni
S.Bozzoni

Reputation: 1002

or ...if you prefer without the second join... 

Select X.USER_ID, 
       X.POSITION, 
       (select T.ATTEMPS FROM TAB_ATTEMPTS T where T.N=X.POSITION) AS ATTEMPT 
FROM 
(Select MYTABLE.USER_ID,MAX(TAB_ATTEMPTS.N) AS POSITION 
FROM 
MYTABLE (NOLOCK) 
JOIN 
TAB_ATTEMPTS (NOLOCK) ON MYTABLE.ATTEMPT=TAB_ATTEMPTS.ATTEMPT 
GROUP BY MYTABLE.USER_ID ) as X

Upvotes: 0

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

I would create a view with a "NumericAttempt" column and then query the view on that column.


CREATE VIEW [viewOutput]
AS
SELECT 
entryid,
User,
Attempt,
CASE Attempt
  WHEN 'Fisrt'  THEN 1
  WHEN 'Second' THEN 2
  WHEN 'Third'  THEN 3
  WHEN 'Fourth' THEN 4
  WHEN 'Fifth'  THEN 5
  ELSE 999
END as AttemptNumeric
FROM  TableName

Upvotes: 0

S.Bozzoni
S.Bozzoni

Reputation: 1002

You have to build a table like that

TAB_ATTEMPTS

N Attempt
1 first
2 second
3 third
4 fourth

Then you have to join the table that you have with that one joining the two table on the column attempt

Select X.USER_ID,T.ATTEMPT from (
  Select  MYTABLE.USER_ID,MAX(TAB_ATTEMPTS.N) AS POSITION FROM 
  MYTABLE (NOLOCK) 
  JOIN
  TAB_ATTEMPTS (NOLOCK) ON MYTABLE.ATTEMPT=TAB_ATTEMPTS.ATTEMPT
  GROUP BY  MYTABLE.USER_ID
) as X
JOIN TAB_ATTEMPTS T ON X.POSITION=T.N

That should do the work.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can do this by assigning values in a query using a case or join:

select t.*
from (select t.*,
             row_number() over (partition by user order by n.num desc) as seqnum
      from t join
           (values ('First', 1), ('Second', 2), ('Third', 3), ('Fourth', 4)
           ) n(val, num)
           on t.attempt = n.val
     ) t
where seqnum = 1;

I agree with other comments that normalizing the data using a reference table makes more sense. Sometimes, though, we are stuck with data in a particular format and have to deal with that. This answer addresses that situation.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

Rank your records with ROW_NUMBER and a CASE expression:

select eid, usrid
from
(
  select eid, usrid,
    row_number() over 
    (
      partition by  usrid
      order by
        case when attempt = 'First' then 1 
             when attempt = 'Second' then 2 
             when attempt = 'Third' then 3
             when attempt = 'Fourth' then 4
             else 5
        end desc
    ) as rn
  from mytable
) ranked
where rn = 1;

Upvotes: 3

Meloman
Meloman

Reputation: 3712

I think you can't.

Store integer foreign key into your table :

UsrID   FkAttemptId
1       2
2       4

and make another table with join :

AttemptId   AttemptTitle
1           First
2           Second
3           Third
4           Fourth

With taht you can make a lot of possibilities like add description or other languages...

AttemptId   AttemptTitle   AttemptDescription   AttemptFR    ...
1           First          Bla bla...           Premier
2           Second         Bli bli...           Second
3           Third          ...
4           Fourth

Upvotes: 3

Related Questions