SQL Statement(s)

Question

If I have the following table:

CREATE TABLE #temp (
    id int,
    num int,
    question varchar(50),
    qversion int );

INSERT INTO #temp VALUES(1, 1, 'Question 1 v1', 1);
INSERT INTO #temp VALUES(2, 1, 'Question 1 v2', 2);
INSERT INTO #temp VALUES(3, 2, 'Question 2 v1', 1);
INSERT INTO #temp VALUES(4, 2, 'Question 2 v2', 2);
INSERT INTO #temp VALUES(5, 2, 'Question 2 v3', 3);
INSERT INTO #temp VALUES(6, 3, 'Question 3 v1', 1);

SELECT *
FROM #temp;

DROP TABLE #temp;

And I would like to get a table to display the three questions in their lastest version? This is in SQL Server 2005

Answer 1

CREATE TABLE #temp (
    id int,
    num int,
    question varchar(50),
    qversion int );

INSERT INTO #temp VALUES(1, 1, 'Question 1 v1', 1);
INSERT INTO #temp VALUES(2, 1, 'Question 1 v2', 2);
INSERT INTO #temp VALUES(3, 2, 'Question 2 v1', 1);
INSERT INTO #temp VALUES(4, 2, 'Question 2 v2', 2);
INSERT INTO #temp VALUES(5, 2, 'Question 2 v3', 3);
INSERT INTO #temp VALUES(6, 3, 'Question 3 v1', 1);

WITH latest AS (
   SELECT num, MAX(qversion) AS qversion
   FROM #temp
   GROUP BY num
)
SELECT #temp.*
FROM #temp
INNER JOIN latest
    ON latest.num = #temp.num
    AND latest.qversion = #temp.qversion;

DROP TABLE #temp;

Answer 2

SELECT t1.id, t1.num, t1.question, t1.qversion
FROM #temp t1
LEFT OUTER JOIN #temp t2
  ON (t1.num = t2.num AND t1.qversion < t2.qversion)
GROUP BY t1.id, t1.num, t1.question, t1.qversion
HAVING COUNT(*) < 3;

Answer 3

If your RDBMS supports tuples in IN clause, you can use this(e.g. PostgreSQL, Oracle):

SELECT *
FROM temp 
WHERE (num,qversion) IN (SELECT num, MAX(qversion) FROM temp GROUP BY num)

using WITH clause (and if your RDBMS supports USING (e.g. PostgreSQL)):

    WITH latest AS 
    (
        SELECT num, MAX(qversion) AS qversion
        FROM temp
    )
    SELECT *
    FROM temp JOIN latest USING(num,qversion)

But since you are using MSSQL, use this:

SELECT *
FROM #temp a
WHERE 
 EXISTS
 (
 SELECT b.num, MAX(b.qversion) 
 FROM #temp b 
 WHERE b.num = a.num
 GROUP BY b.num
 HAVING MAX(b.qversion) = a.qversion
 )

Of course, Cade Roux's code works :-) i just offer an alternative solution, maybe they are faster

[EDIT]

the WITH clause is not necessary with this kind of query, you can write it like this instead:

    SELECT *
    FROM temp JOIN 
    (
        SELECT num, MAX(qversion) AS qversion
        FROM temp 
    ) AS latest ON latest.num = temp.num and latest.qversion = temp.qversion

Answer 4

You're using SQL Server 2005, so it's worth at least exploring the over clause:

select
    *
from
    (select *, max(qversion) over (partition by num) as maxVersion from #temp) s
where
    s.qversion = s.maxVersion

Answer 5

I would like to get a table to display the three latest versions of each question.

1. I assume that that qversion is increasing with time. If this assumption is backwards, remove the desc keyword from the answer.
2. The table definition does not have an explicit not null constraint on qversion. I assume that a null qversion should be excluded. (Note: Depending on settings, lack of an explicit null/not null in the declaration may result in a not null constraint.) If the table does have a not null contraint, than the text where qversion is not null should be removed. If qversion can be null, and nulls need to be included in the result set, then additional changes will need to be made.

---

CREATE TABLE #temp (
    id int,
    num int,
    question varchar(50),
    qversion int );

INSERT INTO #temp VALUES(1, 1, 'Question 1 v1', 1);
INSERT INTO #temp VALUES(2, 1, 'Question 1 v2', 2);
INSERT INTO #temp VALUES(3, 2, 'Question 2 v1', 1);
INSERT INTO #temp VALUES(4, 2, 'Question 2 v2', 2);
INSERT INTO #temp VALUES(5, 2, 'Question 2 v3', 3);
INSERT INTO #temp VALUES(7, 2, 'Question 2 v4', 4); 
-- ^^ Added so at least one row would be excluded.
INSERT INTO #temp VALUES(6, 3, 'Question 3 v1', 1);
INSERT INTO #temp VALUES(8, 4, 'Question 4 v?', null);

select id, num, question, qversion
from (select *, 
        row_number() over (partition by num order by qversion desc) as RN
    from #temp
    where qversion is not null) T
where RN <= 3