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
 How to avoid aggregate functions and TOP/BOTTOM ?

Author  Topic 

senglory
Starting Member

8 Posts
Posted - 2011-04-27 : 11:39:14
This my table
quote:


CREATE TABLE TAB1
(
curr tinyint NOT NULL ,
ddate smalldatetime NOT NULL ,
rate decimal(15, 4) NOT NULL ,
primary key (curr, ddate)
)


If "rate" didn't change in comparision with previous value of "ddate" then table doesn't contain new record for that unchanged date.

I need to write a query that returns all "currency" for the specified date. This query MUSTN'T contain any aggregate functions and no TOP/BOTTOM words are allowed there as well. Any ideas how to write this query?

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-27 : 11:43:15
We don't homework here, nor anything that isn't microsoft sql server. Show us the query you have so far, even if it doesn't work, and we'll help you from there. Sample data and expected output would be helpful as well

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-04-27 : 11:58:34
How about offset/fetch?
ranking functions?


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

senglory
Starting Member

8 Posts
Posted - 2011-04-27 : 14:25:06
quote:
Originally posted by jimf

We don't homework here, nor anything that isn't microsoft sql server. Show us the query you have so far, even if it doesn't work, and we'll help you from there. Sample data and expected output would be helpful as well

Jim

Everyday I learn something that somebody else already knew


Ok, here's the demo table content:
quote:

ddate curr rate
----------------------- ---- ---------------------------------------
2000-01-01 00:00:00 1 5
2000-01-02 00:00:00 1 6
2000-01-02 00:00:00 2 3
2000-01-03 00:00:00 2 4
2000-01-14 00:00:00 1 7
2000-01-15 00:00:00 3 8
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-27 : 14:30:57
Thanks,
Now provide the expected output, since it really isn't clear from your post exactly what you want. Also provide the query that you've tried so far.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

senglory
Starting Member

8 Posts
Posted - 2011-04-28 : 00:33:57
To be more specific, here's my T-SQL definition of table and data: 

--===== If the test table already exists, drop it     
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
        DROP TABLE #mytable

--===== Create the test table with  
CREATE TABLE #mytable 
(
curr tinyint NOT NULL ,
ddate smalldatetime NOT NULL ,
rate decimal(15, 4) NOT NULL ,
primary key (curr, ddate)
)



--===== Setup any special required conditions especially where dates are concerned    
SET DATEFORMAT DMY


--===== Insert the test data into the test table 
INSERT INTO #mytable        (curr,ddate, rate) 

SELECT '1','Jan  1 2000 12:00AM','5'  UNION ALL
SELECT '2','Jan  1 2000 12:00AM','55' UNION ALL
SELECT '1','Jan  2 2000 12:00AM','6'  UNION ALL
SELECT '2','Jan  2 2000 12:00AM','3'  UNION ALL
SELECT '2','Jan  3 2000 12:00AM','4'  UNION ALL
SELECT '1','Jan 14 2000 12:00AM','7'  UNION ALL
SELECT '2','Jan 15 2000 12:00AM','8'


And these are the expected pseudo-queries and their outputs:
quote:


select curr, rate
from (................)
where ddate='01-01-2000'

curr rate
---- ---------------------------------------
1 5.0000
2 55.0000



select curr, rate
from (................)
where ddate='02-01-2000'

curr rate
---- ---------------------------------------
1 6.0000
2 3.0000


select curr, rate
from (................)
where ddate='03-01-2000'

curr rate
---- ---------------------------------------
1 6.0000
2 3.0000


select curr, rate
from (................)
where ddate='04-01-2000'

curr rate
---- ---------------------------------------
1 6.0000
2 3.0000



select curr, rate
from (................)
where ddate='05-01-2000'

curr rate
---- ---------------------------------------
1 6.0000
2 3.0000


select curr, rate
from (................)
where ddate='13-01-2000'

curr rate
---- ---------------------------------------
1 6.0000
2 3.0000


select curr, rate
from (................)
where ddate='14-01-2000'

curr rate
---- ---------------------------------------
1 7.0000
2 3.0000


select curr, rate
from (................)
where ddate='15-01-2000'

curr rate
---- ---------------------------------------
1 7.0000
2 8.0000


select curr, rate
from (................)
where ddate='16-01-2000'

curr rate
---- ---------------------------------------
1 7.0000
2 8.0000



Hope this is much more clear.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-28 : 08:49:11
Here's a hint:
You'll need the MAX function and a derived table.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

senglory
Starting Member

8 Posts
Posted - 2011-04-28 : 09:05:38
This is exactly I need to avoid - any kind of aggregate functions
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-28 : 09:23:20
no top? no agg? This is the weirdest homework ever. Are you allowed to use CTEs or windowed functions?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2011-04-28 : 09:23:52
are you using SQL Server ?

Why can't you use aggregate functions ? what's the issue ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-28 : 09:24:50
quote:
Originally posted by senglory

This is exactly I need to avoid - any kind of aggregate functions


Why??

If that's homework, it's stupid.

Why would you not use a hammer on a nail?

Corey

I Has Returned!!
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-28 : 09:25:42
It's a homework problem. I'm trying to get senglory to at least give it try by giving him some hints. We'll see how that goes.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-04-28 : 09:37:13
max is an aggregate function.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-28 : 09:40:41
quote:

This query MUSTN'T contain any aggregate functions and no TOP/BOTTOM words are allowed there as well. Any ideas how to write this query?


I'm assuming APPLY is out of the question as well. Which is okay with me, I've done the query with aggs and windowed functions, but the APPLY still eludes me. The only occassion I ever have to use it is anwering questions here.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-04-28 : 14:29:32
[code]select t.curr, t.rate
from tab1 t
where t.ddate <= @date and
not exists(select *
from tab1 n
where n.curr = t.curr and
n.ddate > t.date and
n.ddate <= @date
)[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-28 : 17:55:48
quote:
We don't homework here, nor anything that isn't microsoft sql server. Show us the query you have so far, even if it doesn't work, and we'll help you from there. Sample data and expected output would be helpful as well

Jim


Correction, Mirko will do your homework for you!

Everyday I learn something that somebody else already knew
Go to Top of Page

senglory
Starting Member

8 Posts
Posted - 2011-04-29 : 02:08:33
quote:
Originally posted by mmarovic

select t.curr, t.rate
  from tab1 t
  where t.ddate <= @date and
        not exists(select *
                     from tab1 n
                     where n.curr = t.curr and
                           n.ddate > t.ddate and
                           n.ddate <= @date
                   )


Mirko

My blog: http://mirko-marovic-eng.blogspot.com/


Super! This was exactly what I needed! Thank you for you help, otherwise I'll break my head agains the wall :)
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-04-29 : 02:22:39
quote:
Originally posted by jimf

quote:
We don't homework here, nor anything that isn't microsoft sql server. Show us the query you have so far, even if it doesn't work, and we'll help you from there. Sample data and expected output would be helpful as well

Jim


Correction, Mirko will do your homework for you!

Everyday I learn something that somebody else already knew

Well, this one was not quite elementary.

Senglory, I hope you understand the solution, otherwise I should really be ashamed of doing your homework for you.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-29 : 07:35:18
Very impressive thought process, Mirko! Now that you have written it, it seems so simple, but I for one, could not have thought of it.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-04-29 : 07:52:39
Yes, I'm glad you posted it Merkin, I was banging around with NOT EXISTS and EXISTS but couldn't get it to work. I'd like to see what happens when this guys prof says "explain to me how you came up with this"!


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-04-29 : 11:39:51
Sunita, Jim: Thank you for kind words.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site