March 28, 2014 at 2:51 pm
So I have data like the following:
ID COUNTER DATA
1 10 BLAH
1 20 BLAH
2 10 BLAH
3 10 BLAH
2 20 BLAH
2 30 BLAH
What I want to return is:
1 20 BLAH
2 30 BLAH
3 10 BLAH
I want the top 1, having the highest counter from each ID. This is a highly simplified version of that I am pulling which also is between a date range, but same principle.
IE: SELECT * FROM Table WHERE ID in (SELECT DISTINCT ID FROM Table WHERE Date BETWEEN <date> AND <date>
Any ideas? I'd rather keep it in one statement if possible, but if I have to do it in multiple passes then so be it. 🙂
Thanks!
March 28, 2014 at 3:04 pm
Eric Stout (3/28/2014)
So I have data like the following:
ID COUNTER DATA
1 10 BLAH
1 20 BLAH
2 10 BLAH
3 10 BLAH
2 20 BLAH
2 30 BLAH
What I want to return is:
1 20 BLAH
2 30 BLAH
3 10 BLAH
I want the top 1, having the highest counter from each ID. This is a highly simplified version of that I am pulling which also is between a date range, but same principle.
IE: SELECT * FROM Table WHERE ID in (SELECT DISTINCT ID FROM Table WHERE Date BETWEEN <date> AND <date>
Any ideas? I'd rather keep it in one statement if possible, but if I have to do it in multiple passes then so be it. 🙂
Thanks!
The following is how you can select the top 1 from each group.
with BaseData as (
select
ID,
COUNTER,
DATA,
rn = row_number() over (partition by ID order by COUNTER desc)
where
Date >= somedate and
Date <= someotherdate
)
select
ID,
COUNTER,
DATA
from
BaseData
where
rn = 1;
March 28, 2014 at 3:39 pm
Looks great! Now I just have to figure out how to ALSO get it to work in SQL Anywhere. (I need it for both platforms).
I didn't even think about a temp view (not sure if that's the right term).
Thanks
April 2, 2014 at 8:03 am
You could as well use MAX and Group by... it should work 🙂
SELECT ID,MAX(COUNTER) COUNTER, DATA FROM TABLE
WHERE daterange between date1 and date2
GROUP BY ID,DATA
- Nandu
April 2, 2014 at 8:38 am
nthammareddy (4/2/2014)
You could as well use MAX and Group by... it should work 🙂
SELECT ID,MAX(COUNTER) COUNTER, DATA FROM TABLE
WHERE daterange between date1 and date2
GROUP BY ID,DATA
And if the data stored in the DATA column is different between rows with the same ID this won't work.
April 2, 2014 at 8:43 am
First, I just noticed something left out of my original code and fixed it here.
Second, I am also providing another solution that may work in both SQL Server and other SQL based systems.
with BaseData as (
select
ID,
COUNTER,
DATA,
rn = row_number() over (partition by ID order by COUNTER desc)
from
sometable
where
Date >= somedate and
Date <= someotherdate
)
select
ID,
COUNTER,
DATA
from
BaseData
where
rn = 1;
select
st.ID,
st.COUNTER,
DATA
from
st.sometable st
inner join (select st1.ID, max(st1.COUNTER) maxcnt from sometable st1
where st1.DATE between somedate and someotherdate
group by st1.ID)dt
on (st.ID = dt.ID and st.COUNTER = dt.maxcnt)
where
st.DATE between somedate and someotherdate
April 2, 2014 at 9:48 am
I had noticed your missing "from" as well and it confused me for a moment but I figured it out. Thanks again. 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply