function help - 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

General SQL Server Forums

New to SQL Server Programming

function help

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-04 : 02:55:18

Dear experts,can you please correct me the logic in my procedure?

actually,
I'm using this query to find out the last three rows in a table

select top 3 * from table15 order by column01 desc, order by column01

but I'm getting the result in the reverse order....

I tried in another way like this........

select * from table15 where column01 not in (select top 20 column01 from table15)

this is giving the desired result as expected,

but for this I've to use select count (*) from table15.

Now I'm trying to write a table return function with these queries....

I'm very new to functions...

please guide me

create function findlastrows(@desiredvalue int) returns table
as begin
@total int
@finalvalue int
@total=select count(*) from table15
@final value=@total-@desiredvalue

return select * from table15 where column01 not in (select top @finalvalue column01 from table15)

end
go

thank you verymuch in advance

Vinod

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-04 : 03:49:34

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-04 : 04:32:14

the actual result set is

100 HRP-Selection Process HRPJOF 1 1 1 1 7 7 7 7 2 2
1001 QAC-Masters QACCHR 1 1 1 1 7 7 1 1
1002 QAC-Masters QACASP 1 1 1 1 7 7 1 1
1003 QAC-Masters QACSSZ 1 1 1 1 7 7 1 1
1004 QAC-Masters QACSSM 1 1 1 1 7 7 1 1
1005 QAC-Masters QACFQD 1 1 1 1 7 7 1 1
1006 QAC-Masters QACTPN 1 1 1 1 7 7 1 1
1007 QAC-Masters QACTDF 1 1 1 1 7 7 7 7
1008 QAC-Masters QACTGP 1 1 1 1 7 7 1 1
1009 QAC-Masters QACITT 1 1 1 1 1 7 7 7

i need last 5 rows

Vinod

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 05:06:11

did u try with the query posted in another discussion which gets last 5 records

declare @tt table ( id int, col1 varchar(50), col2 varchar(20), col3 int, col4 int, col5 int)
insert @tt
select 100 ,'HRP-Selection', 'HRPJOF', 1, 1, 1 union
select 1001,'AC-Masters', 'QACCHR', 1, 1, 1 union
select 1002 ,'QAC-Masters', 'QACASP', 1, 1, 1 union
select 1003 ,'QAC-Masters', 'QACSSZ', 1, 1, 1 union
select 1004 ,'QAC-Masters', 'QACSSM', 1, 1, 1 union
select 1005 ,'QAC-Masters', 'QACFQD', 1, 1, 1 union
select 1006, 'QAC-Masters', 'QACTPN', 1, 1, 1 union
select 1007 ,'QAC-Masters', 'QACTDF', 1, 1, 1 union
select 1008 ,'QAC-Masters', 'QACTGP', 1, 1, 1 union
select 1009 ,'QAC-Masters', 'QACITT', 1, 1, 1

Select * from @tt where id >=( select top 1 id from @tt a where 3 =
(Select count(distinct(id)) from @tt where id >= a.id))

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-04 : 05:13:34

What's so much fuss about it?


-- prepare sample data
declare @t table 
( 
	id int, 
	col1 varchar(50), 
	col2 varchar(20), 
	col3 int, 
	col4 int, 
	col5 int
)

insert @t
select 100 ,'HRP-Selection', 'HRPJOF', 1, 1, 1 union all
select 1001,'AC-Masters', 'QACCHR', 1, 1, 1 union all
select 1002 ,'QAC-Masters', 'QACASP', 1, 1, 1 union all
select 1003 ,'QAC-Masters', 'QACSSZ', 1, 1, 1 union all
select 1004 ,'QAC-Masters', 'QACSSM', 1, 1, 1 union all
select 1005 ,'QAC-Masters', 'QACFQD', 1, 1, 1 union all
select 1006, 'QAC-Masters', 'QACTPN', 1, 1, 1 union all
select 1007 ,'QAC-Masters', 'QACTDF', 1, 1, 1 union all
select 1008 ,'QAC-Masters', 'QACTGP', 1, 1, 1 union all
select 1009 ,'QAC-Masters', 'QACITT', 1, 1, 1 

-- Show expected output
Select * from
	(select top 5 * from @t order by id desc) t 
order by id

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 05:26:36

Both the query will work...but if the id not unique for example id 1009 is present 5 times then the taking top5 of ids in descending order will give 5 1009 records only.

if ur really concern about only no. of records irrespective of duplicates harsh's query is efficient.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-04 : 05:29:48

This will handle duplicates as well:

-- prepare sample data
declare @t table 
( 
	id int, 
	col1 varchar(50), 
	col2 varchar(20), 
	col3 int, 
	col4 int, 
	col5 int
)

insert @t
select 100 ,'HRP-Selection', 'HRPJOF', 1, 1, 1 union all
select 1001,'AC-Masters', 'QACCHR', 1, 1, 1 union all
select 1002 ,'QAC-Masters', 'QACASP', 1, 1, 1 union all
select 1003 ,'QAC-Masters', 'QACSSZ', 1, 1, 1 union all
select 1004 ,'QAC-Masters', 'QACSSM', 1, 1, 1 union all
select 1005 ,'QAC-Masters', 'QACFQD', 1, 1, 1 union all
select 1005 ,'QAC-XXX', 'YCHN', 1, 1, 1 union all
select 1005 ,'QAC-XYX', 'FHN', 1, 1, 1 union all
select 1005 ,'QAC-XXY', 'HFK', 1, 1, 1 union all
select 1005 ,'QAC-XZX', 'VOCS', 1, 1, 1 union all
select 1006, 'QAC-Masters', 'QACTPN', 1, 1, 1 union all
select 1007 ,'QAC-Masters', 'QACTDF', 1, 1, 1 union all
select 1008 ,'QAC-Masters', 'QACTGP', 1, 1, 1 union all
select 1009 ,'QAC-Masters', 'QACITT', 1, 1, 1 

-- Show expected output
Select * from
	(select top 5 WITH TIES * from @t order by id desc) t 
order by id

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 05:39:21

with the same input if u find top 7 records it will introduce duplicates in the output..

In the sample data id 1005 starts exactly in the 5th position from the bottom so the above query will work

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-04 : 05:52:18

As I specified in the other thread, it seems Pagination is needed

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources