Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Help with a view

Hello.

I need help creating a view.
(it would be easy using a cte but I need this to work on sql 2000 so i cant use CTE)

I have a table with

ID, Alternate Version

And I want to select rows with max (version) and min (alt)
IE, version has higher prio than alternate..

say that I have

ID VER ALT
1   1     1
2   1     1
2   1    2
2  2     2
2   3   2
2  3 4

The view should return

1  2  1  (for row with id 1)
2  3  2  (for row with id 2)   (not 2  3 1 since it don't exist. so its not just to select id max(ver) min(alt)  from the table.
0
pucko73
Asked:
pucko73
  • 9
  • 5
  • 3
  • +1
1 Solution
 
SharathData EngineerCommented:
>> 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).
0
 
Pratima PharandeCommented:
select ID,Min(alernate),max(version) from  [tablename]
group by ID
0
 
pucko73Author Commented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pucko73Author Commented:
sorry sarath, wrote wrong data. Se my last example instead.
0
 
Pratima PharandeCommented:
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)
0
 
pucko73Author Commented:
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
0
 
PortletPaulCommented:
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

Open in new window

0
 
pucko73Author Commented:
Yes but Over partition by came with SQL2005 and don't work on SQL 2000
0
 
PortletPaulCommented:
sorry, just noticed that comment about 2000, yep, won't work there
0
 
pucko73Author Commented:
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
0
 
PortletPaulCommented:
try this one:
SELECT
        tst.id
      , tst.alt
      , tst.ver
FROM tst
INNER JOIN (
             SELECT id, max(ver) AS max_ver FROM tst GROUP BY id
           ) AS mv
             ON tst.id = mv.id AND tst.ver = mv.max_ver
INNER JOIN (
             SELECT id, ver, min(alt) AS min_alt FROM tst GROUP BY id, ver
           ) AS ma
             ON tst.id = ma.id AND tst.alt = ma.min_alt
;

-- http://sqlfiddle.com/#!3/59804/7

Open in new window

0
 
pucko73Author Commented:
Thanks.... my comment abowe did not work. But this works like a charm.....
0
 
Pratima PharandeCommented:
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
0
 
PortletPaulCommented:
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.
0
 
pucko73Author Commented:
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
0
 
pucko73Author Commented:
Ahh ok thank Paul.

There was no sql 2000 in the selection when creating the question.
Only sql2005 and 2008
0
 
PortletPaulCommented:
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.
0
 
pucko73Author Commented:
ok thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now