CODEWITHSUNDEEP
sqlsql-serversubquerycaseconditional-statements
Sami
Sami

Reputation: 4006

Select one row from non unique rows based on row value

I have a quiz table

id  | user_id | quiz_id
--------------------------
1   | 34567   | 12334
2   | 34567   | 12334
3   | 34567   | 23455

id 1 and 2 depicts a quiz that can be assigned to the same user twice

and a quiz transaction table

id  | date | status
------------------------
1   | 2014 | assigned
2   | 2014 | assigned
3   | 2014 | assigned
------------------------
1   | 2014 | completed

id is foreign key to quiz table id, the last row depicts whenever a user finished the quiz, the row in the transaction table is updated with status 'completed'

Expected Result: I want a table with a structure like

id  | user_id| course_id | date | status
------------------------------------------
1   | 34567  | 12334     | 2014 | completed
2   | 32567  | 12334     | 2014 | assigned
3   | 2014   | 23455     | 2014 | assigned

My query is 

SELECT  q.id, q.user_id, q.course_id, qt.date, qt.status FROM quiz q 
LEFT JOIN 
quiz_transaction qt  ON 
q.id = qt.id

but it gives me extra row (as the query will)

1   | 34567  | 12334     | 2014 | assigned

I cannot use 

ON qt.type = 'completed'

Because if its completed it should return a completed row and if not it should return an assigned row but not both.

So in the result I cannot have 

1   | 34567  | 12334     | 2014 | completed
1   | 34567  | 12334     | 2014 | assigned

How can I do it?

Upvotes: 1

Views: 310

Answers (3)

dnoeth
dnoeth

Reputation: 60472

Depending on your release SQLnServer supports Standard SQL's "Windowed Aggregate Functions". ROW_NUMBER will give you a single row:

SELECT
   q.id
  ,q.user_id
  ,q.quiz_id
  ,qt.date
  ,qt.status
FROM quiz q 
JOIN 
 (
   SELECT
     id
     ,date
     ,status
     ,ROW_NUMBER()
      OVER (PARTITION BY id
            ORDER BY Status DESC) as rn
   FROM quiz_transaction
 ) as qt
ON q.id = qt.id
WHERE rn = 1

If you got more complex ordering rules you need to use a CASE:

     ,ROW_NUMBER()
      OVER (PARTITION BY id
            ORDER BY CASE Status WHEN 'completed' THEN 1 
                                 WHEN 'doing'     THEN 2
                                 WHEN 'assigned'  THEN 3
                     END) as rn

Upvotes: 1

Linger
Linger

Reputation: 15058

How about simply using the MAX() function with GROUP BY (SQL Fiddle):

SELECT  q.id, q.user_id, q.course_id, qt.date, MAX(qt.status) AS Status
FROM quiz q 
LEFT JOIN quiz_transaction qt ON q.id = qt.id 
GROUP BY q.id, q.user_id, q.course_id, qt.date

EDIT: If you need to order a string a certain way, you could use a CASE statement to convert the string to a number. Get the MAX value and then convert it back (SQL Fiddle):

SELECT m.id, m.user_id, m.quiz_id, MAX(m.date), 
  CASE WHEN MAX(m.status) =  1 THEN 'assigned'
       WHEN MAX(m.status) = 2 THEN 'doing'
       WHEN MAX(m.status) = 3 THEN 'completed' END AS Status
FROM 
(
  SELECT q.id, q.user_id, q.quiz_id, qt.date, 
  CASE WHEN qt.status = 'assigned' THEN 1 
       WHEN qt.status = 'doing' THEN 2
       WHEN qt.status = 'completed' THEN 3 END AS Status
  FROM quiz q 
  LEFT JOIN quiz_transaction qt ON q.id = qt.id 
) AS m
GROUP BY m.id, m.user_id, m.quiz_id;

Upvotes: 1

Azar
Azar

Reputation: 1867

try this 

SELECT  q.id, q.user_id, q.course_id, q1.date, qt.status FROM quiz q 
         LEFT JOIN 
    (Select id , convert(varchar,max(convert(varbinary,status )))  'Status'
     from     quiz_transaction
    group by id
    ) qt  ON 
         q.id = qt.id 
    left join quiz_transaction q1 on q1.id = qt.id and q1.status=qt.status

Upvotes: 0

Related Questions