use DISTINCT but keep a corresponding value

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

use DISTINCT but keep a corresponding value

Author

Topic

jorondo
Starting Member

3 Posts

Posted - 2012-07-13 : 11:37:41

Hi,

I am quite new to SQL and have been searching for an answer to this for a while now. So here is my question:

I have variables X,Y,Z. X has many duplicates, with a numeric value on Y and Z. I want to SELECT DISTINCT X, max(Y), /*and here comes my question*/ is it possible to retrieve at the same time the Z value that corresponds to the max(Y) value?

I don't quite know how to formulate this well so I found nothing on google and I'd be glad if somebody could help me, or at least tell me if this is possible.

Thanks a lot!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 13:10:32

You can use the row_number function like this:

SELECT X,Y,Z FROM
(
	SELECT X,Y,Z,
		ROW_NUMBER() OVER (PARTITION BY X ORDER BY Y DESC,Z DESC) AS RN
	FROM 
		YourTable
)s
WHERE RN = 1;

jorondo
Starting Member

3 Posts

Posted - 2012-07-20 : 10:51:38

Thanks, from the look of it it seems it will do just the right thing. However when I code it the only error that comes up is not recognizing the "over" statement. I should mention i am coding on SAS, maybe that changes something. Here is the error message,

Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=,
<>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE,
LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

Any idea?

quote:
Originally posted by sunitabeck

You can use the row_number function like this:
SELECT X,Y,Z FROM
(
SELECT X,Y,Z,
ROW_NUMBER() OVER (PARTITION BY X ORDER BY Y DESC,Z DESC) AS RN
FROM
YourTable
)s
WHERE RN = 1;

jorondo
Starting Member

3 Posts

Posted - 2012-07-20 : 10:57:04

here is my code, perhaps you'll see something I missed...

	create table thien_14_pat as
		select  b.enrolid, b.svcdate as first_disp, b.clopi, b.pras, b.tica from
		(select  b.enrolid, b.svcdate, b.clopi, b.pras, b.tica,
			row_number() over 
			(partition by b.enrolid order by b.svcdate, b.clopi desc, b.pras desc, b.tica desc) as RN
				from out.last_elig_18 as a inner join thien_14 as b
		)
					on a.enrolid=b.enrolid
						where (RN=1 and (a.indexdt<=b.svcdate<(a.index_dis+14)))
	group by enrolid;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 14:11:16

SQL server doesnt have create table...as syntax. either it should be create view...as or you should use

select ....into table from....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-20 : 14:28:57

quote:
Originally posted by sunitabeck

You can use the row_number function like this:
SELECT X,Y,Z FROM
(
SELECT X,Y,Z,
ROW_NUMBER() OVER (PARTITION BY X ORDER BY Y DESC,Z DESC) AS RN
FROM
YourTable
)s
WHERE RN = 1;

This was your post #3333

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 19:23:49

quote:
This was your post #3333

Oops! I lost it before I could see it and capture it!! :) Now I am on 3335!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources