quote:

---

Originally posted by pmccann1

is it possible to run a stored procedure from a view then look at the table that has been created from the procedure?

---

quote:

---

Originally posted by pmccann1

Bascially say i have the follwoing information

id course dept status
1 edexcel lit 1
2 geog sci 5
2 maths sci 7

i want one instance of each, as in one row for id 1 and one row for id 2 either of the ones that are there. the user that want this information can only use views that they have been give permission to. there is already a sp that does something likethis so i was just going to use it.

suggestions to what way i could do it are welcome, could this just be done in a view only

---

CREATE VIEW Yourview
AS
SELECT t.id,
t.course,
t.dept,
t.status
FROM YourTable t
INNER JOIN (SELECT id,MAX(status) AS MaxStatus
            FROM YourTable
            GROUP BY id) t1
ON t1.id=t.id
AND t1.MaxStatus=t.status

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY dbo.stmbiogr.student_id ORDER BY dbo.stcsessd.aos_end_dt DESC) AS RowNo,
dbo.stmbiogr.student_id, RIGHT(dbo.stmbiogr.student_id, 8) AS [Access No], dbo.stmaos.aos_code, dbo.stmaos.acad_period, dbo.stmaos.aos_period, dbo.stcsessd.aos_end_dt, dbo.stmaos.stage_code, dbo.stmaos.attend_mode, dbo.stmbiogr.forename, dbo.stmbiogr.surname, 
dbo.stmbiogr.birth_dt, dbo.stcstatd.full_desc

FROM dbo.stmbiogr INNER JOIN
dbo.stmaos ON dbo.stmbiogr.student_id = dbo.stmaos.student_id INNER JOIN
dbo.stcstatd ON dbo.stmaos.aos_code = dbo.stcstatd.aos_code INNER JOIN
dbo.stcsttyp ON dbo.stcstatd.aos_code = dbo.stcsttyp.aos_code INNER JOIN
dbo.stcsessd ON dbo.stmaos.aos_code = dbo.stcsessd.aos_code AND dbo.stmaos.acad_period = dbo.stcsessd.acad_period AND 
dbo.stmaos.aos_period = dbo.stcsessd.aos_period
WHERE (dbo.stcsttyp.aos_type = 'C') AND (dbo.stmaos.acad_period = '08/09') AND (dbo.stmaos.stage_code = 'ENR') AND 
(dbo.stcsessd.geolocn_code IN ('D0001', 'C0001'))
)t
WHERE RowNo=1
ORDER BY student_id, attend_mode, aos_end_dt

quote:

---

Originally posted by pmccann1

I am using sql2005 but when i use the above it says the
SELECT ROW_NUMBER() OVER is not supported

---