Help with a view

>> And I want to select rows with max (version) and min (alt)

Do you want max(version) and min(alt) record for each ID?

>> 1  2  1  (for row with id 1)

How did you get this record. There is no such record in your input data (i.e. 1 1 1).

select ID,Min(alernate),max(version) from  [tablename]
group by ID

Won't work as I wrot.

select id,alt,ver from tst
:
1      1      1
2      1      1
2      1      2
2      2      2
2      3      2
2      3      4

select ID,Min(alt),max(ver) from  tst
group by ID

1      1      1
2      1      4

select * from tst where id=2 and alt = 1 and ver = 4
gives no result.

I expect result as this:

with cte  
AS  
(  
   select id,alt,ver from tst t  
   where ver = (select max(ver) from tst t2 where t2.id=t.id)  
)  
select   * from cte  
where cte.alt = (select min(alt) from tst t3 where t3.id=cte.id and t3.ver=cte.ver)

1      1      1
2      3      4

sorry sarath, wrote wrong data. Se my last example instead.

DECLARE @T1 TABLE (
ID INT,
alt INT,
ver INT)

INSERT INTO @T1
select id,alt,ver from tst t  
   where ver = (select max(ver) from tst t2 where t2.id=t.id)

select   * from @T1 cte
where cte.alt = (select min(alt) from tst t3 where t3.id=cte.id and t3.ver=cte.ver)

I want to cretae a veiw
cant use variables in a view.

I want it to work on sql 2000. CTE wont work on SQL2000

Othervise my example abowe will work......

I can always crete a helper view... but that's not what I want.

create view helper as
select id,alt, max(ver) as ver from tst group by id,alt

and then:
create view TheRealView
as
select id,min(alt),max(ver) from helper group by id

the query below matches your expected results:

/*
| ID | ALT | VER |
|----|-----|-----|
|  1 |   1 |   1 |
|  2 |   3 |   4 |
*/

SELECT
        id
      , alt
      , ver
FROM (
      SELECT
              id
            , alt
            , ver
            , max(ver) over (partition BY id)      AS max_ver
            , min(alt) over (partition BY id, ver) AS min_alt
      FROM tst
      ) AS derived
WHERE ver = max_ver
AND alt = min_alt
;


CREATE TABLE tst
    ([id] int, [alt] int, [ver] int)
;
    
INSERT INTO tst
    ([id], [alt], [ver])
VALUES
    (1, 1, 1),
    (2, 1, 1),
    (2, 1, 2),
    (2, 2, 2),
    (2, 3, 2),
    (2, 3, 4)
;
: http://sqlfiddle.com/#!3/59804/4

Select allOpen in new window

Yes but Over partition by came with SQL2005 and don't work on SQL 2000

sorry, just noticed that comment about 2000, yep, won't work there

But I think you put me in the correct direction anyway:


This seems to work. (if I'm not wrong)

SELECT
        id
      , min(alt)
      , max(ver)
FROM (
      SELECT
              id
                  ,alt
                  ,max(ver) as ver
           
           
      FROM tst group by id,alt,ver
      ) AS derived group by id

Thanks.... my comment abowe did not work. But this works like a charm.....

one correction

SELECT
        id
      , min(alt)
      , ver FROM (
      SELECT
              id
                  ,alt
                  ,max(ver) as ver
           
           
      FROM tst group by id,alt,ver
      ) AS derived group by id

Thanks! Cheers, Paul

btw: I added tag to the question regarding sql 2000
Many of us look at topics/tags to figure out the environment
- so if you have more 2k questions I suggest using a tag.

well it wont....

I have to change to  AS derived group by id,ver otervise it won't runt.
And it also wont give me the result that I want:
The accepted answer works fine though

1      1      1
2      1      1
2      1      2
2      3      4

Ahh ok thank Paul.

There was no sql 2000 in the selection when creating the question.
Only sql2005 and 2008

yes, that's right the topics won't allow selection of sql 2k, but you can type in your own tags

watch for it at the next question.

ok thanks