Need help in GROUPBY

Question

Post reply

Need help in GROUPBY

Bhuvnesh

SSC Guru

Points: 59351
More actions
September 30, 2008 at 10:11 pm

#94141

HI ,
Below is the script
---------------------------------------------------------
create table mytest
(
Recid int ,
Gid varchar(2) )
GO
------------------------------------------------
insert into mytest(gid,num) select 1 ,'a'
insert into mytest(gid,num) select 2 ,'a'
insert into mytest(gid,num) select 3 ,'a'
insert into mytest(gid,num) select 4 ,'f'
insert into mytest(gid,num) select 5 ,'f'
insert into mytest(gid,num) select 6 ,'f'
insert into mytest(gid,num) select 7 ,'b'
insert into mytest(gid,num) select 8 ,'b'
insert into mytest(gid,num) select 9 ,'b'
insert into mytest(gid,num) select 10 ,'d'
insert into mytest(gid,num) select 11 ,'d'
insert into mytest(gid,num) select 12 ,'d'
--------------------------------------
I want result (one record for every Gid) in one query.
For example
2 ,a
4 ,f
8 ,b
12,d
-------
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 14 posts - 1 through 14 (of 14 total)

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

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

The test code produces errors.

The way to do this is to use MIN and GROUP BY.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

Here's the corrected test code and a solution... Now, I'm curious... why don't you care which RecID you return, please?

create table mytest

(

Recid int ,

Gid varchar(2) )

GO

------------------------------------------------

insert into mytest(RecID,Gid) select 1 ,'a'

insert into mytest(RecID,Gid) select 2 ,'a'

insert into mytest(RecID,Gid) select 3 ,'a'

insert into mytest(RecID,Gid) select 4 ,'f'

insert into mytest(RecID,Gid) select 5 ,'f'

insert into mytest(RecID,Gid) select 6 ,'f'

insert into mytest(RecID,Gid) select 7 ,'b'

insert into mytest(RecID,Gid) select 8 ,'b'

insert into mytest(RecID,Gid) select 9 ,'b'

insert into mytest(RecID,Gid) select 10 ,'d'

insert into mytest(RecID,Gid) select 11 ,'d'

insert into mytest(RecID,Gid) select 12 ,'d'

SELECT MIN(RecID) AS RecID, Gid

FROM dbo.MyTest

GROUP BY Gid

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Is it possible to use TOP in this query ....i want to use TOP

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

bhuvnesh.dogra (9/30/2008)
Is it possible to use TOP in this query ....i want to use TOP

Why?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Jeff....appreciate you immediate response.

Actually my requirement is slightly Stupid..

I want always different recid corresponding to their Gid whenever i run it.

For example

1st run

1 a

4 f

8 b

2nd run

2 a

5 f

9 b

11 d

i want to pick randomly

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

This will work... without TOP or a correlated subquery to use it...

;WITH

cteRandom AS

(SELECT ROW_NUMBER() OVER (PARTITION BY Gid ORDER BY NEWID()) AS RandRow,

RecID,Gid

FROM dbo.MyTest

)

SELECT RecID,Gid

FROM cteRandom

WHERE RandRow = 1

Why do you need a random return like this? What are you doing that would require such a thing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

thanks a lottt ..JEFF...it works Fine

Can u explain the logic ...i never use this CTE feature

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

A CTE is nothing more than a "derived table" or an "inline view"... both of which are just sub-queries. If you did it the "old way" without a CTE, it would look like this...

SELECT r.RecID, r.Gid

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Gid ORDER BY NEWID()) AS RandRow,

RecID,Gid

FROM dbo.MyTest) r

WHERE r.RandRow = 1

The difference with a CTE is that it can appear in the FROM clause of the outer query more than once for self joins, etc.

You still need to tell me why you need to select in a random order. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

this is basically Project requirement ,we need to show record randomly

and see below query ,i added BETWEEN clause bcoz i needed 2 records for every Gid.

BUT

Can you tell me how it is picking randomly records ? i still not got it

;WITH

cteRandom AS

(SELECT ROW_NUMBER() OVER (PARTITION BY Gid ORDER BY NEWID()) AS RandRow,

RecID,Gid

FROM dbo.MyTest

)

SELECT RecID,Gid

FROM cteRandom

WHERE RandRow between 1 and 2

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 10

bhuvnesh.dogra (9/30/2008)
we need to show record randomly

Heh... I already know that... but, why is it a requirement? What is it that you are trying to do? ie. Why is it necessary to return data this way?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 11

I've answered several of your questions and provided a code solution... the least you could do is answer just one of mine... what is the purpose of this code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

actually ......one of my colleague asked me about it . he is on leave today ..as sson as he comes i will let you know....

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 13

Thanks. Oh, and the randomness comes from the fact that NEWID generates random numbers. If you order by a random number like that, the result will be in the same order as the random numbers... ie. Random.

The ROS_NUMBER() OVER thing just adds another column of integers to track what the ordinal order of the random numbers produced was.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)