using DISTINCT or GROUP BY with multi-fields

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

using DISTINCT or GROUP BY with multi-fields

Author

Topic

Cyclone112
Starting Member

3 Posts

Posted - 2009-01-25 : 18:35:40

It's hard to describe so if what I type after this doesn't make sense I included examples of what I mean below. For a given year, I want a full row of data corresponding for each unique entry in the object column that corresponds to the highest year for that object which is less than a given year.

table 'survey'

object year data1 data2
aaaaa 2001 uuuuu 11111
aaaaa 2002 vvvvv 22222
aaaaa 1995 wwww 33333
bbbbb 2000 yyyyy 44444
bbbbb 2008 zzzzzz 55555

if given year = 2010 then i want

aaaaa 2002 vvvvv 22222
bbbbb 2008 zzzzzz 55555

if given year = 2001 then i want

aaaaaa 1995 wwww 33333
bbbbbb 2000 yyyyy 44444

if I try to use DISTINCT on 'Object' then I cant Select the rest of the columns as they will also be included in the distinct and therefore every column will be returned.

if I try to use the following code

SELECT Object, max(year), data1, data2 FROM survey WHERE year < 2009(given year) GROUP BY Object

then I get ERROR: column "survey.data1" must appear in the GROUP BY clause or be used in an aggregate function

It seems that no matter what method I use I either only get partial records or I get error messages when attempting to get the rest of the data.

I've been banging my head on the wall for multiple hours on this and I would appreciate any help. Thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-25 : 19:53:12

[code]declare @yr int; set @yr = 2001;
with cte as
(
select row_number() over (partition by object order by yr desc ) as row, *
from Tbl
where yr < @yr
)
select * from cte where row = 1
[/code]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-01-25 : 20:19:14

or

select t.*
from tbl t
join (select object, year = max(year) from tbl where year <= @year group by object) a
on t.object = a.object
and t.year = a.year

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Cyclone112
Starting Member

3 Posts

Posted - 2009-01-25 : 21:46:45

Thanks very much for the quick responses. I didn't realize that this was just a SQL Server forum. I'm using PostgreSQL and I hope you guys will still help me... I just googled SQL forums and this was the first one I found...

I tried both suggestions but to no avail. For your method nr, I get the following error.

ERROR: column "t.year" must appear in the GROUP BY clause or be used in an aggregate function

It does that for every column until I add every one is there and if I do that then I don't get the right data. As for your suggestion sunitabeck, I tried entering what you wrote but I don't think its compatible with PostgreSQL as I got the following error:

ERROR: syntax error at or near "@".

I got the same error with nr's suggestion but I just changed the @year to a number just for testing purposes in his example. Any other suggestions/ideas guys? Thanks in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-01-25 : 22:04:03

The CTE isa t-sql construct and won't work in anything else.

The @ error is because that's how t-sql presents variables. Obviously your's is different.
The derived table works in a lot of sql variations (what you are seeing sounds close to being a bug if you've typed it correctly). Maybe try with "as a" instead of just "a".

another option
select t.*
from tbl t
where year = (select max(a.year) from tbl as a where a.year <= 2001 and t.object = a.object)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Cyclone112
Starting Member

3 Posts

Posted - 2009-01-25 : 23:14:50

Hooray, I got it working. I used the first piece of code you wrote and I will probably try the second piece just so I understand the language better. You were right, there was just some syntactical differences I had to make which I list at the end of this post for anyone else that comes across this problem.

All I can say is thank you very much nr, you too sunitabeck for trying to help as well. Far too often people come on forums for support then once they get it take off and never lend support themselves.

I've never used databases before and at my work they just threw me into it this Friday and I desperately needed to get this done by tomorrow morning. I would have much preferred to spend some time actually learning the language instead of just looking through function lists and such trying to find what I needed.

I obviously got hung up on DISTINCT and GROUP BY as I kept getting so close to the solution but it would seem they weren't enough on there own for this problem. The stuff I read about JOIN seemed to be used for using other tables already in your schema to create a result set but I see you used all the columns from my survey table and then results you obtained from a query into that very same table to come up with my desired data.

Just for other people that come across this thread. I had to change a few minor things to get the query working properly. Here is what I ended up using.

select * from survey
join (select object, max(year) as year from survey where year < givenyear(2009,2000 etc) group by object) as a
on survey.object = a.object and survey.year = a.year

Again thank you very much nr

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2009-01-26 : 04:05:52

quote:

The CTE isa t-sql construct and won't work in anything else.

It is standard sql. It is supported by DB2 and Oracle among others.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:26:09

actually you dont need CTE at all. this is enough


select *
from
(
select row_number() over (partition by object order by yr desc ) as row, * 
   from Tbl
   where yr < @yr
)t
where row = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources