| Author |
 Topic  |
|
|
jorondo
Starting Member
Canada
3 Posts |
 Posted - 07/13/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/13/2012 : 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
Canada
3 Posts |
Posted - 07/20/2012 : 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
Canada
3 Posts |
Posted - 07/20/2012 : 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
India
47189 Posts |
Posted - 07/20/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 07/20/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/20/2012 : 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!! |
|
|
| |
Topic |
|
|
|
use DISTINCT but keep a corresponding value
