CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlgreatest-n-per-group
SBurris
SBurris

Reputation: 7448

SQL Statement(s)

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

Upvotes: 3

Views: 193

Answers (4)

Shannon Severance
Shannon Severance

Reputation: 18430

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

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89741

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;

Upvotes: 3

Eric
Eric

Reputation: 95243

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562991

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;

Upvotes: 1

Related Questions