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 latest 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

SQL Server 2005, over:

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

Answer 4

.

• , qversion . , desc .
• qversion. , qversion . (. null/not null , .) , where qversion is not null . qversion , , .

---

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