Query

Question

Query

jagjitsingh

Ten Centuries

Points: 1059
More actions
July 4, 2016 at 5:02 am

#314802

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

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

jagjitsingh (7/4/2016)
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

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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 2

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.

durga.palepu Hall of Fame Points: 3530 More actions · Answer 3

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;

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 4

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

durga.palepu Hall of Fame Points: 3530 More actions · Answer 5

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;

idname

1000A

980A

970A

990C

950C

960B

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 6

Hi

First one A is coming ok after that it is not working properly.

Thanks

durga.palepu Hall of Fame Points: 3530 More actions · Answer 7

durga.palepu

Hall of Fame

Points: 3530

July 4, 2016 at 6:43 am

#1888296

Can you re post your test data?

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 8

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 10

jagjitsingh

Ten Centuries

Points: 1059

July 4, 2016 at 7:23 am

#1888303

Thanks Crism

Can u little quide how it works

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

jagjitsingh (7/4/2016)
Thanks Crism
Can 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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

durga.palepu Hall of Fame Points: 3530 More actions · Answer 12

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

durga.palepu Hall of Fame Points: 3530 More actions · Answer 14

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.