query to find less than the max(sales_id)

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 2008 Forums

Transact-SQL (2008)

query to find less than the max(sales_id)

Author

Topic

byomjan
Starting Member

34 Posts

Posted - 2011-04-05 : 14:31:51

query to find less than the current sales_id

storeSales

sales_id package_id sales_amount

1401 300 30.00
1404 300 20.00
1406 300 30.00

suppose we are at 1406. the query should return the record with sales_id 1404 .

suppose we are at 1404. the query should return the record with sales_id 1401 .

suppose we are at 1401. the query should return the record with sales_id 1401 .

tried this. but not working.

select * from sales ss join
sales ss1 on ss.package_id=ss1.package_id
where ss.sales_id=1406
and ss.sales_id = ( select max( sales_id ) from sales ss3
where ss3.sales_id=1406
and ss.package_id=ss3.package_id
and ss3.sales_id<ss.sales_id )

Byomjan....

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-05 : 14:52:17

select top 1 *
from sales ss
where salesid < 1406
order by ss.salesid

Jim

Everyday I learn something that somebody else already knew

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 14:52:48

this ?


declare @storeSales table(sales_id int, package_id int, sales_amount decimal(10,2) )
declare @sales_id int

set @sales_id = 1406

insert into @storeSales
SELECT 1401, 300, 30.00
UNION
SELECT 1404, 300, 20.00
UNION
SELECT 1406, 300, 30.00

IF EXISTS 
(
select *   
  From @storeSales where sales_id <> @sales_id
		  and sales_id between 0 and @sales_id -1
)
	BEGIN
		PRINT 'Exists'
		select top 1 sales_id, 
			   package_id, 
			   sales_amount 
		       
		       
		  From @storeSales where sales_id <> @sales_id
		  and sales_id between 0 and @sales_id -1
		  order by sales_id desc
	END
ELSE
	BEGIN
		PRINT 'Does not Exists'

		select top 1 sales_id, 
			   package_id, 
			   sales_amount        
		       
		  From @storeSales where sales_id = @sales_id 
		  order by sales_id desc
	END

If you don't have the passion to help people, you have no passion

byomjan
Starting Member

34 Posts

Posted - 2011-04-05 : 14:56:19

well sales is million plus records table. cant think of this solution yosiasz.

@jimf- select top 1 *
from sales ss
where salesid < 1406
order by ss.salesid

But what in case of 1401. That record would nt return anything .

Byomjan....

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 15:04:15

Byomjan are you trying to do this dynamically or via stored procedure?

If you don't have the passion to help people, you have no passion

byomjan
Starting Member

34 Posts

Posted - 2011-04-05 : 15:05:51

via a stored proc. also top 1 will not work as i need more than 1 columns from that record and will use with it case condition statements.

Byomjan....

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 15:09:04

top 1 brings all columns


create sproc byomass(@sales_id  int)
as
IF EXISTS 
(
select *   
  From Sales where sales_id <> @sales_id
		  and sales_id between 0 and @sales_id -1
)
	BEGIN
		PRINT 'Exists'
		select top 1 sales_id, 
			   package_id, 
			   sales_amount 
		       
		       
		  From Sales where sales_id <> @sales_id
		  and sales_id between 0 and @sales_id -1
		  order by sales_id desc
	END
ELSE
	BEGIN
		PRINT 'Does not Exists'

		select top 1 sales_id, 
			   package_id, 
			   sales_amount        
		       
		  From Sales where sales_id = @sales_id 
		  order by sales_id desc
	END

If you don't have the passion to help people, you have no passion

byomjan
Starting Member

34 Posts

Posted - 2011-04-05 : 15:18:12

to get the previous max sales id i cant do
Sales where sales_id <> @sales_id
and sales_id between 0 and @sales_id -1
will have to do something like max or top where sales.sales_id < sales1.sales_id

But here one question for you .

select top 1 sales_id, package_id, sales_amount

here what if i need to do case when package_id= 300 and sales_amount >0 then 'pack' else 'unpack'

how can i do that with top 1 * ??

PS: the name of the proc was interesting byom ass !!

Byomjan....

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 15:22:52

1. why can not do sales where sales_id <> @sales_id and sales_id between 0 and @sales_id -1?
2.


select top 1 
       sales_id, 
       case when package_id= 300 and sales_amount >0 then 'pack' else 'unpack' end,
       package_id, 
       sales_amount

:( i was thinking biomass sorry boss. never want to call a fellow sql developer an a** :)

If you don't have the passion to help people, you have no passion

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-05 : 15:32:34

Is this cheating?

INSERT INTO @table
SELECT 1401 ,300, 30.00 UNION ALL
SELECT 1404 ,300 ,20.00 UNION ALL
SELECT 1406 ,300 ,30.00

declare @id float
set @id = 1406

IF @id = (select min(id) from @table) SET @id = @id+.00001

select top 1 t1.id,Col1,col2
from @table t1
where id < @id
order by id desc

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources