July 4, 2016 at 5:02 am
Hi
I have 1 query which returns record order by ID like this. Now i want that in addition to the below Query i want another query to group by Name thru Stored Procedure .
Since A is at top first record with Name 'A' top 3 records are displayed . Then with name C , Then B & so on
ID Name
1000 A
990 C
980 A
970 A
960 B
950 C
930 A
Final Data Should be like this
ID Name
1000 A
980 A
970 A
990 C
950 C
960 B
July 4, 2016 at 5:06 am
jagjitsingh (7/4/2016)
HiI have 1 query which returns record order by ID like this. Now i want that in addition to the below Query i want another query to group by Name thru Stored Procedure .
Since A is at top first record with Name 'A' top 3 records are displayed . Then with name C , Then B & so on
ID Name
1000 A
990 C
980 A
970 A
960 B
950 C
930 A
Final Data Should be like this
ID Name
1000 A
980 A
970 A
990 C
950 C
960 B
What are the rules for determining the sequence A -> C -> B in your second query?
Is it related to the highest ID associated with each Name?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 4, 2016 at 5:37 am
Hi
Now 3 Id's of Distinct Name to be get.l
I have got this result from Stored Procedure . Now with Distinct Name i want to get their 3 top ID's
Since Name A is at top according to the first query that's why 3 top ID's of A should appear first. Then C , Theb B and so on.
. I want to display its top 3 Id's . Then Name C appears & same no of ID's (3) want to display.
July 4, 2016 at 6:22 am
Hope this helps you.
create table #name(id int, name char(1));
insert #name(ID, Name)
select 1000, 'A'
union all
select 990, 'C'
union all
select 980, 'A'
union all
select 970, 'A'
union all
select 960, 'B'
union all
select 950, 'C'
union all
select 930, 'A';
with ctenames
as
(
select id, name
, row_number() over(partition by name order by id desc) as rownum
,count(name) over(partition by name ) cntname
from #name
)
select id, name
from ctenames
where rownum <=3 --Replace this with store procedure's param
order by cntname desc;
July 4, 2016 at 6:32 am
Hi
Name A 3 Id's should come first which is not coming , the C Then B & so on .
Since Data from 1st output "A" is at top then "C" then "B".
Thanks
July 4, 2016 at 6:36 am
Hi,
Did you refer to my queries?
The following code from my solution would yield the required result.
with ctenames
as
(
select id, name
, row_number() over(partition by name order by id desc) as rownum
,count(name) over(partition by name ) cntname
from #name
)
select id, name
from ctenames
where rownum <=3
order by cntname desc;
id name
1000 A
980 A
970 A
990 C
950 C
960 B
July 4, 2016 at 6:42 am
Hi
First one A is coming ok after that it is not working properly.
Thanks
July 4, 2016 at 6:43 am
Can you re post your test data?
July 4, 2016 at 6:58 am
Hi
Below is the result returned by Query and this result i am storing in temp table table1. With this table1 i have to get final output.
ID Name
1000 A
990 C
980 A
970 A
960 B
950 C
930 A
July 4, 2016 at 7:03 am
with ctenames
as
(
select id, name
, row_number() over(partition by name order by id desc) as rownum
,MAX(ID) over(partition by name ) maxid
from #name
)
select id, name
from ctenames
where rownum <=3 --Replace this with store procedure's param
order by maxid desc;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 4, 2016 at 7:23 am
Thanks Crism
Can u little quide how it works
Thanks
July 4, 2016 at 7:29 am
jagjitsingh (7/4/2016)
Thanks CrismCan u little quide how it works
Thanks
Sure. Run the CTE part and you will see:
select id, name
, row_number() over(partition by name order by id desc) as rownum
,MAX(ID) over(partition by name ) maxid
from #name
Have a good look at the result of this, ask again if you have further questions.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 4, 2016 at 10:11 am
Hi Chris,
What's the problem with my query? you've just replaced COUNT() with MAX() right?
I could test my version of the query successfully.
July 4, 2016 at 10:16 am
durga.palepu (7/4/2016)
Hi Chris,What's the problem with my query? you've just replaced COUNT() with MAX() right?
I could test my version of the query successfully.
Faulty logic. It's the maximum ID value which is significant, not the number of rows.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 4, 2016 at 10:43 am
Thanks Chris, now I have realized that I've missed the initial requirement to sort names by ID desc.
It's a good problem to takle.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply