SQL Query with out using LAST - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query with out using LAST

Author

Topic

assafsn
Starting Member

8 Posts

Posted - 2004-09-27 : 11:14:47

Hello all.

I wrote the following SQL query:
SELECT dbo.test2.num, dbo.test2.age, LAST(dbo.test1.profit)
FROM dbo.test1 RIGHT OUTER JOIN dbo.test2 ON dbo.test1.num = dbo.test2.num AND dbo.test1.age <= dbo.test2.age
GROUP BY dbo.test2.num, dbo.test2.age
ORDER BY dbo.test2.num, dbo.test2.age

I cant use it on MS-SQL because is doesn't support the function 'LAST'. Does any one have any idea how to get the same results without using 'LAST' ?

Thanks for your help.

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-27 : 11:30:46

Does this work?:

SELECT dbo.test2.num, dbo.test2.age, TOP 1 dbo.test1.profit
FROM dbo.test1 RIGHT OUTER JOIN dbo.test2 ON dbo.test1.num = dbo.test2.num AND dbo.test1.age <= dbo.test2.age
GROUP BY dbo.test2.num, dbo.test2.age
ORDER BY dbo.test2.num, dbo.test2.age

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-27 : 11:37:43

influent: it won't work because you can't use top like that

SELECT dbo.test2.num, dbo.test2.age,
min(dbo.test1.profit) -- or max depends what you want
FROM dbo.test1 RIGHT OUTER JOIN dbo.test2 ON dbo.test1.num = dbo.test2.num AND dbo.test1.age <= dbo.test2.age
GROUP BY dbo.test2.num, dbo.test2.age
ORDER BY dbo.test2.num, dbo.test2.age

but i doubt it will work.
and how do you define last? last based on what?
some table definitions and what are you trying to do will help...

Go with the flow & have fun! Else fight the flow

assafsn
Starting Member

8 Posts

Posted - 2004-09-27 : 12:24:23

I can't use max or min because the field is varchar.
I define the last based on on the max age in that grouping.

Any new ideas ???

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-27 : 13:14:37

Are you just trying to get one row? I don't understand what you're trying to do that LAST does.

assafsn
Starting Member

8 Posts

Posted - 2004-09-27 : 13:36:44

Lets take these tables as example:
test1:
Name Age Class
x 1 a
x 4 a
x 6 b

test2:
Name Age
x 1
x 2
x 3
x 4
x 5
x 6
x 7
x 8
x 9
x 10

The desierable sql will give the following output:
Name Age Class
x 1 a
x 2 a
x 3 a
x 4 a
x 5 a
x 6 b
x 7 b
x 8 b
x 9 b
x 10 b

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-27 : 14:19:21

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40546

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources